mysql - 如何确定多对多关系中所有现有列表的条目是否在列表中

Question

如果已经有人问过类似的问题，但我找不到它，请原谅我。搜索的问题在于，虽然设置非常简单，但很难用几句话表达问题:

基本上，我有可以分配给列表的条目。为此，我有三个表:

mysql> SELECT * FROM list;
+-----+-----------+
| lid | listname  |
+-----+-----------+
|  1  | Fine List |
|  2  | Bad List  |
+-----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM entry;
+-----+-----------+
| eid | entryname |
+-----+-----------+
|  1  | red       |
|  2  | green     |
|  3  | blue      |
|  4  | gray      |
|  5  | black     |
+-----+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM entry2list;
+-----+-----+
| eid | lid |
+-----+-----+
|  1  |  1  |
|  2  |  1  |
|  5  |  1  |
|  2  |  2  |
|  4  |  2  |
+-----+-----+
5 rows in set (0.00 sec)

因此，我有 2 个列表，一个分配了 2 种颜色，一个分配了 3 种颜色。

我可以轻松获取包含特定列表上的条目的列表:

SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=1

但我还需要缺少的条目，这要困难得多:

SELECT tmp.eid, e.* FROM entry e
LEFT JOIN (
SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=2) AS tmp
USING (eid)

+---------+-----+-----------+
| tmp.eid | eid | entryname |
+---------+-----+-----------+
|  NULL   | 1   | red       |
|  2      | 2   | green     |
|  NULL   | 3   | blue      |
|  2      | 4   | gray      |
|  NULL   | 5   | black     |
+---------+-----+-----------+

就我而言，我需要一个是/否列表来查看某个条目是否在列表中:

SELECT GROUP_CONCAT(IF(tmp.eid IS NULL, 'n', 'y')) AS is_set FROM entry e
LEFT JOIN (
SELECT * FROM list
LEFT JOIN entry2list USING (lid)
LEFT JOIN entry USING (eid)
WHERE lid=2) AS tmp
USING (eid)

结果

+-----------+
| is_set    |
+-----------+
| n,y,n,y,n |
+-----------+

但现在我陷入困境了；结果基本上是我需要的，但请求的最后一个要求是我需要每个列表都有一个类似的行，因此 where 类中的 lid 已被替换以某种方式(或者更可能的是，请求必须完全转换)。

我想要得到的结果是这样的

+-----+-----------+
| lid | is_set    |
+-----+-----------+
| 1   | y,y,y,n,n |
| 2   | n,y,n,y,n |
+-----+-----------+

这可能吗？怎么办？

Answer 1

首先，生成您需要的行——所有列表和所有条目。然后引入有关什么属于什么的信息，并聚合以获得您想要的奇怪的输出形式。

以下内容会生成您想要的内容，每个列表和条目都有一个单独的行:

select l.listname, e.entryname,
       (case when el.lid is null then 'n' else 'y' end) as InList
from list l cross join
     entry e left join
     entry2list el
     on l.lid = el.lid and e.eid = el.eid;

对于您想要的格式，您需要group by 和group concat。像这样的事情:

select l.lid, l.listname,
       group_concat(case when el.lid is null then 'n' else 'y' end order by e.entryname) as ListFlags
from list l cross join
     entry e left join
     entry2list el
     on l.lid = el.lid and e.eid = el.eid
group by l.lid;