今天在关联查询数据的时候出现了select * 和select count(*) 的数据不一致的问题 select*查询的数据是一条,但是count(*)之后的数据为2条 具体看下图:
sql:
SELECT
*
FROM t_goods goods LEFT JOIN t_goods_merchant gm ON gm.goods_id=goods.id LEFT JOIN t_goods_pic pic ON pic.goods_id=goods.id WHERE goods.source_type=2
AND pic.goods_pic_main=1 AND goods.goods_type=2 AND goods.status=1 OR (gm.status=1 AND gm.merchant_id=1 AND goods.put_status=1) GROUP BY goods.id
因为用到了group by,数据为一条,但是当我使用count(*)的时候 出现了2条数据 如下图
:
sql:
SELECT
COUNT(*)
FROM t_goods goods LEFT JOIN t_goods_merchant gm ON gm.goods_id=goods.id LEFT JOIN t_goods_pic pic ON pic.goods_id=goods.id WHERE goods.source_type=2
AND pic.goods_pic_main=1 AND goods.goods_type=2 AND goods.status=1 OR (gm.status=1 AND gm.merchant_id=1 AND goods.put_status=1) GROUP BY goods.id
结果出现了2条数据 。经过分析,怀疑是group by和count的执行顺序问题。当我把后面的group by 去掉之后查询 select * 结果就是2条。
也就是说 先执行的count 后执行的gourpby 所以导致select *是一条 然后count的时候是2条。
后面对sql做了一个包裹 问题就解决了,sql如下:
SELECT COUNT(a.id) FROM (
SELECT
goods.*
FROM t_goods goods LEFT JOIN t_goods_merchant gm ON gm.goods_id=goods.id LEFT JOIN t_goods_pic pic ON pic.goods_id=goods.id WHERE goods.source_type=2
AND pic.goods_pic_main=1 AND goods.goods_type=2 AND goods.status=1 OR (gm.status=1 AND gm.merchant_id=1 AND goods.put_status=1) GROUP BY goods.id
)a
这回count(*)的数据就是1条了。