SELECT q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted, count(t.c_id) answerNum FROM t_ext_question q left join t_comment t on t.c_pid = q.c_id where q.c_deleted = 0 AND lower( q.c_title ) LIKE '%123%' AND q.c_userid = 'ADMIN' AND DATE_FORMAT( q.c_createtime, '%Y-%m-%d' ) BETWEEN '2023-05-01' AND '2023-05-14' GROUP BY q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted HAVING count(t.c_id)=0 ORDER BY c_createtime desc
时间: 2023-06-20 21:09:27 浏览: 37
这是一个 SQL 查询语句,作用是查询符合一定条件的问题,并按照创建时间倒序排列。具体解释如下:
- SELECT:指定要查询的字段。
- FROM:指定要查询的表。
- LEFT JOIN:左连接操作,将问题表和评论表进行连接。
- WHERE:指定查询的条件,包括问题未被删除,标题中包含“123”,提问者为“ADMIN”,创建时间在2023年5月1日到14日之间。
- GROUP BY:按照问题ID、提问者ID、标题、创建时间、是否私有、被采纳的回答ID、是否删除进行分组。
- HAVING:对分组后的结果进行筛选,只留下回答数量为0的记录。
- ORDER BY:按照创建时间倒序排列。
所以这个查询的目的是找到提问者为“ADMIN”,标题中包含“123”,创建时间在2023年5月1日到14日之间,且没有任何回答的问题,并按照创建时间倒序排列。
相关问题
SELECT q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted, count(t.c_id) answerNum FROM t_ext_question q left join t_comment t on t.c_pid = q.c_id where q.c_deleted = 0 AND lower( q.c_title ) LIKE '%123%' AND q.c_userid = 'ADMIN' AND DATE_FORMAT( q.c_createtime, '%Y-%m-%d' ) BETWEEN '2023-05-01' AND '2023-05-14' GROUP BY q.c_id, q.c_userid, q.c_title, q.c_createtime, q.c_isprivate, q.c_acceptid, q.c_deleted HAVING count(t.c_id)=0 ORDER BY c_createtime desc
这是一个 SQL 查询,查询的是一个问题和它的答案数量。具体的查询条件包括:
- `q.c_deleted = 0`:问题没有被删除
- `lower( q.c_title ) LIKE '%123%'`:问题标题中包含字符串 "123"(不区分大小写)
- `q.c_userid = 'ADMIN'`:问题是由用户 "ADMIN" 提交的
- `DATE_FORMAT( q.c_createtime, '%Y-%m-%d' ) BETWEEN '2023-05-01' AND '2023-05-14'`:问题创建时间在 2023 年 5 月 1 日和 5 月 14 日之间
- `count(t.c_id)=0`:问题没有答案
- `ORDER BY c_createtime desc`:按照问题创建时间的倒序排序
查询的结果包括问题的 ID、用户 ID、标题、创建时间、是否为私有问题、被接受的答案 ID、是否被删除以及答案的数量。
SELECT orders.id,orders.user_id,orders.cityarea_id,orders.batchcode,orders.status, orders.pay_status,orders.real_pay,orders.total,orders.money,orders.pay_type, orders.premium,orders.createtime,orders.paytime,orders.confirmtime, orders.is_evaluate,orders.pay_method,orders.exp_user_id, orders.return_score, orders.is_change,orders.reward_money,orders.isvalid,orders.after_sale_status,orders.remark, orders.use_coupon_detail,orders.block_chain_price, users.name,users.weixin_name,users.weixin_fromuser, users.phone,wcoe.diy_area_name ,writes.ActivityStatus,writes.NewMoney,records.Commission,records.ReturnRadio from wsy_o2o.weixin_cityarea_orders orders left join wsy_o2o.weixin_cityarea_supply supply on orders.cityarea_id = supply.id left join wsy_o2o.weixin_cityarea_payonline_orders wcco on orders.batchcode=wcco.batchcode left join wsy_user.weixin_users users on users.id = orders.user_id LEFT JOIN wsy_o2o.weixin_cityarea_orders_extend wcoe ON orders.batchcode = wcoe.batchcode LEFT JOIN wsy_dream.ShopWrite writes on orders.batchcode = writes.Batchcode LEFT JOIN wsy_o2o.weixin_cityarea_supply_commissons_record records ON orders.cityarea_id = records.SupplyId and records.CreateTime < orders.createtime ORDER BY records.CreateTime DESC LIMIT 1 WHERE orders.types=3 and orders.customer_id = 41 AND orders.isvalid=true GROUP BY orders.batchcode ORDER BY orders.id DESC limit 0,20; 以上代码报错,怎么解决
根据您提供的查询代码,有几个问题需要解决:
1. `LIMIT` 和 `ORDER BY` 语句的顺序错误。在 `ORDER BY` 之前应该是 `WHERE` 语句,然后是 `GROUP BY`,最后是 `ORDER BY` 和 `LIMIT`。请将这两个语句调整到正确的位置。
2. `LEFT JOIN` 和 `JOIN` 语句之间缺少逗号。请在第一个 `LEFT JOIN` 语句和第二个 `LEFT JOIN` 语句之间添加逗号。
修改后的代码如下:
```sql
SELECT orders.id, orders.user_id, orders.cityarea_id, orders.batchcode, orders.status,
orders.pay_status, orders.real_pay, orders.total, orders.money, orders.pay_type,
orders.premium, orders.createtime, orders.paytime, orders.confirmtime,
orders.is_evaluate, orders.pay_method, orders.exp_user_id, orders.return_score,
orders.is_change, orders.reward_money, orders.isvalid, orders.after_sale_status, orders.remark,
orders.use_coupon_detail, orders.block_chain_price, users.name, users.weixin_name, users.weixin_fromuser,
users.phone, wcoe.diy_area_name , writes.ActivityStatus, writes.NewMoney, records.Commission, records.ReturnRadio
FROM wsy_o2o.weixin_cityarea_orders orders
LEFT JOIN wsy_o2o.weixin_cityarea_supply supply ON orders.cityarea_id = supply.id
LEFT JOIN wsy_o2o.weixin_cityarea_payonline_orders wcco ON orders.batchcode = wcco.batchcode
LEFT JOIN wsy_user.weixin_users users ON users.id = orders.user_id
LEFT JOIN wsy_o2o.weixin_cityarea_orders_extend wcoe ON orders.batchcode = wcoe.batchcode
LEFT JOIN wsy_dream.ShopWrite writes ON orders.batchcode = writes.Batchcode
LEFT JOIN wsy_o2o.weixin_cityarea_supply_commissons_record records ON orders.cityarea_id = records.SupplyId AND records.CreateTime < orders.createtime
WHERE orders.types = 3 AND orders.customer_id = 41 AND orders.isvalid = true
GROUP BY orders.batchcode
ORDER BY records.CreateTime DESC
LIMIT 1 OFFSET 0;
```
请注意,上述修改的代码仅修复了语法错误。如果仍然存在问题,可能需要进一步检查表和列名的正确性以及其他约束条件。