SQL:请改进我的选择，其中子查询返回一个查询

Question

我已经有了下面问题的有效解决方案，但我担心它很愚蠢或效率低下。

有一个列为(id, attributes...)的Thing表，也有列为(id, thing_id, version_attributes...)的ThingVersion表。问题是从正好存在一个对应的ThingVersion行的Thing中进行选择。

现在我有一些类似的东西

SELECT Thing.id AS id, Foo.whatever
FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
WHERE Thing.id IN (
    SELECT thing_id FROM ThingVersion TV
    WHERE 1 = (
        SELECT COUNT(*)
        FROM ThingVersion TV2
        WHERE TV2.thing_id = TV.thing_id)
    )
ORDER BY Foo.whatever

它似乎给出了正确的结果，作为一个强调的非大师，它似乎不言自明，但-->em>三个选择了？！？！--我忍不住觉得一定有更好的办法。

有吗？

Answer 1

您可以在子查询中使用HAVING子句：

SELECT Thing.id AS id, Foo.whatever
  FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
 WHERE Thing.id IN
        ( SELECT thing_id
            FROM ThingVersion TV
           GROUP BY thing_id
          HAVING COUNT(*) = 1
        )
 ORDER BY Foo.whatever
;

您也可以在主查询中删除JOIN：

SELECT thing_id AS id, whatever
  FROM Foo
 WHERE thing_id IN
        ( SELECT thing_id
            FROM ThingVersion TV
           GROUP BY thing_id
          HAVING COUNT(*) = 1
        )
 ORDER BY whatever
;

（我假设Foo.thing_id和ThingVersion.thing_id中出现的每个值肯定都出现在Thing.id中。）

Answer 2

SELECT Thing.id AS id, Foo.whatever
FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
where Thing.idin (select thing_id from ThingVersion group by thing_id having COUNT(*)>1)

Answer 3

严格使用JOINs:的解决方案

SELECT t.*
FROM Thing t
JOIN ThingVersion tv1
  ON tv1.thing_id = t.id
LEFT JOIN ThingVersion tv2
  ON tv2.thing_id = t.id
  AND tv2.id <> tv1.id
WHERE tv2.id IS NULL

使用GROUP BY和COUNT:的解决方案

SELECT t.* 
FROM Thing t
JOIN ThingVersion tv
  ON tv.thing_id = t.id
GROUP BY t.id
HAVING COUNT(t.id) = 1;

试着把它们都表现出来。