in嵌套循环会自动过滤掉重复的ID

最新推荐文章于 2022-06-28 18:24:07 发布

lumengabc

最新推荐文章于 2022-06-28 18:24:07 发布

阅读量2.1k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/webnoties/article/details/17218963

版权

mysql 专栏收录该内容

127 篇文章 0 订阅

订阅专栏

以下查询结果会不同。是因为 in嵌套循环会自动过滤掉重复的ID

mysql> select count(*) from goods
    -> where goodsid
    -> in(SELECT goodsid  FROM goodsatt WHERE cid=200);
+----------+
| count(*) |
+----------+
|   169039 |
+----------+
1 row in set (7.00 sec)

mysql> select count(*) from goods a,goodsatt b
    -> where (a.goodsid=b.goodsid and b.cid=200);
+----------+
| count(*) |
+----------+
|   172083 |
+----------+
1 row in set (2.63 sec)

in嵌套循环会自动过滤掉重复的ID，举例：

以下查询会得到4条符合条件的记录，由于menu_id有重复值。

mysql> SELECT *
    -> FROM lm_menu_att
    -> WHERE att_id =200
    -> LIMIT 0 , 30
    -> ;
+---------+--------+
| menu_id | att_id |
+---------+--------+
|     363 |    200 |
|     359 |    200 |
|     364 |    200 |
|     364 |    200 |
+---------+--------+
4 rows in set (0.02 sec)

所以要是用这2种不同语句统计的话数据是会不同的。

mysql> select count(*) from lm_menu
    -> where menu_id
    -> in(SELECT menu_id  FROM lm_menu_att WHERE att_id=200
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from lm_menu a,lm_menu_att b
    -> where (a.menu_id=b.menu_id and b.att_id=200);
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)