在MySQL中,我有这样的表格:
+-----------------------+
| Assets |
+-----------------------+
| Id | Name | RootId |
+----+---------+--------+
| 1 | Asset A | 1 |
+----+---------+--------+
| 2 | Asset B | 2 |
+----+---------+--------+
| 3 | Asset C | 3 |
+----+---------+--------+
| 4 | Asset D | 2 |
+----+---------+--------+
| 5 | Asset E | 3 |
+----+---------+--------+
| 6 | Asset F | 3 |
+----+---------+--------+
我知道不是最好的表格结构...但我现在坚持下去。
我正在尝试编写一个查询,该查询在给定Id
值的情况下,仅当正好有两 (2) 行具有相同RootId
时才返回一个RootId
并RootName
。否则,这些列应为 NULL。
因此,使用上表,如果给定 4Id
,则查询应返回:
+----------------------------------+
| Assets |
+----------------------------------+
| Id | Name | RootId | RootName |
+----+---------+--------+----------+
| 4 | Asset D | 2 | AssetB |
+----+---------+--------+----------+
但是,如果给定任何其他Id
值,例如 5,它应该返回:
+----------------------------------+
| Assets |
+----------------------------------+
| Id | Name | RootId | RootName |
+----+---------+--------+----------+
| 5 | Asset E | null | null |
+----+---------+--------+----------+
对此的任何帮助将不胜感激。我认为这将需要一个带有 COUNT 的子查询,可能还需要一个 GROUP BY,但我真的不确定如何表达它......
提前感谢!
以下内容应实现此逻辑:
select id, name,
(case when cnt = 2 then rootid end) as rootid,
(case when cnt = 2 then ari.name end) as rootname
from assets a join
(select rootid, count(*) as cnt
from assets a
group by rootid
) ri
on a.rootid = ri.rootid left join
assets ari
on a.rootid = ari.id
where id = 4;
您也可以按以下方式执行此操作:
select a.id, a.name,
(case when a.cnt = 2 then a.rootid end) as rootid,
(case when a.cnt = 2 then ari.name end) as rootname
from (select a.*,
(select count(*) from assets a2 where a2.rootid = a.rootid) as cnt
from assets a
where id = 4
) a left join
assets ari
on a.rootid = ari.id;
如果没有完全聚合,这将执行得更好。
这里有一个SQL小提琴来说明它们。
类似这样的内容将返回指定的结果集:
SELECT a.Id
, a.Name
, IF(q.cnt=2,q.RootId,NULL) AS RootId
, IF(q.cnt=2,q.RootName,NULL) AS RootName
FROM Assets a
JOIN ( SELECT COUNT(1) AS cnt
, r.RootId
, r.RootName
FROM Assets r
JOIN Assets s
ON s.RootId = r.RootId
WHERE r.Id = 4
GROUP BY r.RootId, r.RootName
) q
ON q.Id = a.Id
如果RootId
可能为 NULL,则需要使用 LEFT [OUTER] JOIN,并包含 .如果要将RootId
的 NULL 值视为与另一个 NULL 值匹配,请将相等比较器替换为 null 安全的相等比较运算符,<=>
添加这两个调整可提供更强大的解决方案:
SELECT a.Id
, a.Name
, IF(q.cnt=2,q.RootId,NULL) AS RootId
, IF(q.cnt=2,q.RootName,NULL) AS RootName
FROM Assets a
JOIN ( SELECT COUNT(1) AS cnt
, r.RootId
, r.RootName
FROM Assets r
LEFT -- lef outer join
JOIN Assets s
ON s.RootId <=> r.RootId -- nullsafe equality
WHERE r.Id = 4
GROUP BY r.RootId, r.RootName
) q
ON q.Id = a.Id