如何从具有多值字段的同一张表中查找具有不同where条件的多个记录的计数 [英] How to find count of multiple records with different where conditions from same table with multivalued fields

问题描述

我问了问题今天早些时候，但我有一个后续问题，该问题增加了多值字段的复杂性.

I asked a question earlier today but I have a follow up question to that which adds a complexity of multivalued fields.

给出下表:

ID    lightness    | darkness     | color
------|-------------|--------------|---------
1     |10           | 20           | green, blue, yellow
2     |10           | 08           | green, purple, orange
3     |10           | 10           | black, magenta, orange
4     |20           | 05           | green, creame
5     |10           | 20           | red, purple
6     |10           | 16           | red, white
7     |33           | 20           | brown, red
8     |10           | 10           | green, blue

我想找出:

• 颜色为浅色10的记录数
• 颜色为暗20的记录数

所以最终输出将是:

Color    | lightness   | darkness   | Total
---------|-------------|------------|---------
green    | 4           | 1          | 5
red      | 2           | 2          | 4
Total    | 6           | 3          | 9

group by将丢失其值，结果将不正确.可以在多值字段上使用.value，因此我可以执行以下操作: 例如:

The group by would lose its value and the results will be incorrect. The .value can be used on the multivalued field so I can do the following: For Example:

select * from colortable where color.value = 2

将显示绿色存在的所有记录

Will show all records where green exists

select * from colortable where color.value = 3

将显示红色存在的所有记录

Will show all records where red exists

我知道这确实是一个糟糕的设计，但是我已经继承了这个设计，必须在数据上运行查询.

I understand that this is really bad design but I've inherited this and have to run queries on the data.

推荐答案

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

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

这篇关于如何从具有多值字段的同一张表中查找具有不同where条件的多个记录的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！