SELECT c.id as cid,`c`.`realname`,`c`.`gender`,`c`.`appointtime`,`c`.`age`,`c`.`health`,`c`.`medication_instruction`,`c`.`createtime`,`c`.`mobile`,o.id as order_id,`o`.*,`i`.* FROM `fa_consult` `c` LEFT JOIN `fa_order` `o` ON `c`.`id`=`o`.`consult_id` LEFT JOIN ( SELECT * FROM ( SELECT * FROM `fa_order_changeprice` ORDER BY `id` DESC ) new GROUP BY `order_id` ) i ON `c`.`id`=`i`.`order_id` WHERE ( `i`.`state` = 0 AND `o`.`is_changeprice` = 1 ) AND `c`.`deletetime` IS NULL ORDER BY `c`.`createtime` DESC LIMIT 0,10, 其中i.state=0没有筛选出正确的数据
时间: 2023-07-13 16:41:24 浏览: 44
这是一条 SQL 查询语句,其目的是从 `fa_consult` 表中查询最近 10 条咨询记录及其对应的订单信息和订单变价记录。但是,该查询语句中的 `i.state=0` 条件只会筛选出最新的一条订单变价记录,并不会筛选出所有状态为 0 的订单变价记录。如果你想要查询所有状态为 0 的订单变价记录,应该将该条件移动到子查询中,例如:
```
SELECT c.id as cid, `c`.`realname`, `c`.`gender`, `c`.`appointtime`, `c`.`age`, `c`.`health`, `c`.`medication_instruction`, `c`.`createtime`, `c`.`mobile`, o.id as order_id, `o`.*, `i`.*
FROM `fa_consult` `c`
LEFT JOIN `fa_order` `o` ON `c`.`id`=`o`.`consult_id`
LEFT JOIN (
SELECT *
FROM (
SELECT *
FROM `fa_order_changeprice`
WHERE `state` = 0
ORDER BY `id` DESC
) new
GROUP BY `order_id`
) i ON `o`.`id`=`i`.`order_id`
WHERE `o`.`is_changeprice` = 1 AND `c`.`deletetime` IS NULL
ORDER BY `c`.`createtime` DESC
LIMIT 0, 10
```
这样,查询结果中就会包含所有状态为 0 的订单变价记录。