当前位置：文江博客话题详情

MySQL 使用 EXISTS 选择计数

发布于 2024-11-08 22:50:07 字数 703 浏览 2 评论 0原文

我有3张桌子。

表1 id、thing_id

table_index id

table_index_info table_index_id、table1_id

table_index_info 包含 table_index 的历史记录。这个历史可以参考table1，可能很多次或者0次。

我需要运行一个查询，返回 table1 中具有特定 thing_id 的所有行。它还需要计算 table_index 中有多少行至少有 1 个 table_index_info 链接到 table1。

这是我的问题：

SELECT table1.*,
   (SELECT COUNT(i.id)
      FROM table_index i
      WHERE EXISTS (SELECT 0
                 FROM table_index_info h
                 WHERE h.table1_id = table1.id
                 AND h.table_index_id = i.id)
   ) AS indexCount

FROM table1
WHERE table1.thing_id= $thingId

这是最好/正确的方法吗？

原文

I have 3 tables.

table1
id, thing_id

table_index
id

table_index_info
table_index_id, table1_id

table_index_info contains a history of table_index. This history can refer to table1, possibly many times or 0 times.

I need to run a query that returns all rows in table1 with a specific thing_id.
It also needs to count how many rows in table_index that have at least 1 table_index_info linking to table1.

Here's my query:

SELECT table1.*,
   (SELECT COUNT(i.id)
      FROM table_index i
      WHERE EXISTS (SELECT 0
                 FROM table_index_info h
                 WHERE h.table1_id = table1.id
                 AND h.table_index_id = i.id)
   ) AS indexCount

FROM table1
WHERE table1.thing_id= $thingId

Is this the best/correct way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月棠 2024-11-15 22:50:07

在这种情况下，我会使用 JOIN 而不是 EXISTS。

SELECT table1.*,
    ( SELECT COUNT(i.id)
         FROM table_index i
         INNER JOIN table_index_info h ON h.table_index_id = i.id 
         WHERE h.table1_id = table1.id
     ) AS indexCount
FROM table1
WHERE table1.thing_id = $thingId

I would use a JOIN instead of EXISTS in this case.

SELECT table1.*,
    ( SELECT COUNT(i.id)
         FROM table_index i
         INNER JOIN table_index_info h ON h.table_index_id = i.id 
         WHERE h.table1_id = table1.id
     ) AS indexCount
FROM table1
WHERE table1.thing_id = $thingId

回复收藏 0 原文

~没有更多了~