mysql - 在单个表中显示重叠行的 SQL 查询？

Question

我一直在 Google 和 SO 中搜索这个问题的答案，但遇到了困难。甚至不确定我问的是否正确，但我会在这里尽力而为:

我的数据:

Fruit  | Attributes
-------------------
Apple  | Dark Red
Apple  | Green
Apple  | Yellow
Apple  | Light Red
Apple  | Greenish Yellow
Apple  | Dark Yellow
Apple  | Brown
Banana | Yellow
Banana | Greenish Yellow
Banana | Dark Yellow 
Banana | Yellow 
Banana | Brown
Banana | Red
Banana | Black

我想做的是运行一个查询，输出重叠的所有水果(在本例中只是苹果和香蕉)的属性数量。

希望我已经说清楚了...如果需要澄清，请告诉我。

Answer 1

是这样的吗？

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

我以类似的方式对 City 和 State 进行了尝试，结果成功了:

SELECT DISTINCT CITY, STATE 
FROM ADDRESS
WHERE CITY IN (
    SELECT CITY
    FROM ADDRESS
    GROUP BY CITY
    HAVING COUNT(DISTINCT STATE) > 1)
ORDER BY CITY, STATE    

• 浴室注意
• 洗澡
• 贝德福德 NS
• 贝德福德质量控制
• 布兰登 MB
• 布兰登山
• 布兰普顿 NB
• 安大略省布兰普顿

...等等

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