mysql过滤列结果

Question

我有以下数据:

<表类> ID heade tbody><<tr>100蓝色100黑色100橙色101布朗101绿色102绿色102白色102黄色103黄色103蓝色

SELECT ID, COUNT(DISTINCT heade) AS num_colours
FROM mytable
WHERE heade IN ('blue', 'white')
GROUP BY ID
HAVING num_colours = 2

这可以概括为:

SELECT ID
FROM your_table
WHERE heade IN ('Color1', 'Colo2', 'Color3'......'Colorn')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = n;

您可以传递确切的颜色及其计数为'n'。下面是一个dbfill演示。

当你通过三种颜色时:

SELECT ID
FROM your_table
WHERE heade IN ('blue', 'yellow', 'white')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 3;

Outout: Empty.

当你通过两种颜色时:

SELECT ID
FROM your_table
WHERE heade IN ('blue', 'yellow')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 2;

输出:

ID
103

当你通过单一颜色:

SELECT ID
FROM your_table
WHERE heade IN ('blue')
GROUP BY ID
HAVING COUNT(DISTINCT heade) = 1;

输出:

ID
100
103