mysql - 输出共享和不共享相同属性的项目的 SQL 查询？

Question

假设我有五种水果，其中一种是重点，另外四种是长期农业的兴趣。这些水果当然有很多属性，一些属性是共享的，而另一些属性则是每种水果所独有的。

以下 SQL 查询及其变体可以完成确定共享哪些属性的工作。我能够从[此处][1]及时提供的答案中获得我正在寻找的内容，但仍在挖掘以获取我需要的报告。

SELECT ATTRIBUTE, COUNT(*) FROM (
    SELECT DISTINCT ATTRIBUTE, FRUIT 
    FROM FRUIT_TABLE
    WHERE ATTRIBUTE IN (
        SELECT ATTRIBUTE
        FROM FRUIT_TABLE
        GROUP BY ATTRIBUTE
        HAVING COUNT(DISTINCT FRUIT_NAME) > 1 )
        ORDER BY ATTRIBUTE, FRUIT_NAME ) AS RESULTS 
 GROUP BY ATTRIBUTE

但是，我无法弄清楚如何确定 Apple 未共享的属性(例如)。

我正在寻找的理想输出如下所示:

Fruit | Shared w/ Apple | Not Shared w/ Apple
---------------------------------------------
Orange       1                       5
Banana       0                       8
Pear         6                       2
Plum         2                       9

预先感谢您以这种方式发送的任何帮助。

Answer 1

您可以对 FRUIT_TABLE 进行自连接:

SELECT ft1.FRUIT_NAME AS Fruit,
    SUM(CASE WHEN ft2.ATTRIBUTE IS NOT NULL THEN 1 ELSE 0 END) As 'Shared with Apple',
    SUM(CASE WHEN ft2.ATTRIBUTE IS NULL THEN 1 ELSE 0 END) As 'Not Shared with Apple'
FROM FRUIT_TABLE ft1 LEFT JOIN
(
    SELECT ATTRIBUTE, FRUIT_NAME
    FROM FRUIT_TABLE ft
    WHERE ft.FRUIT_NAME = "Apple"
) ft2
ON ft1.ATTRIBUTE = ft2.ATTRIBUTE
GROUP BY ft1.FRUIT_NAME