由于具有多字段值列，因此***的解决方案是创建一个新表并将该表中的所有已知颜色都扔掉.这样您的新表看起来就像

Since you have a multifield value column, your best solution is to make a new table and throw all the known colors in that table. So your new table would look like

ID | cid | color
---|-----|-------
1  | 2   | green
2  | 3   | red

现在您可以加入一些东西了！

Now you've got something to join with!

SELECT p.color, 
       Sum(IIf(lightness=10,1,0)) as lightness, 
       Sum(IIf(darkness=20,1,0)) as darkness,
       lightness+darkness AS Total
FROM colortable c inner join predefinedcolors p on p.id = c.color.value
WHERE c.color.value in (2,3)
GROUP BY c.color, p.conditionid.value