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

### 本文来自：IT宝库（https://www.itbaoku.cn）

```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可以在多估字段上使用，因此我可以执行以下操作: 例如:

```select * from colortable where color.value = 2
```

```select * from colortable where color.value = 3
```

## 推荐答案

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

```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
```

## 其他推荐答案

```SELECT ColourTable.ID, KnownColour, ColourTable.Lightness, ColourTable.Darkness,
ColourTable.Colour, ColourTable.Lightness, ColourTable.Darkness
FROM ColourTable, knownColours WHERE (((ColourTable.Colour) Like "*"
& [KnownColour] & "*") AND ((ColourTable.Lightness)=10)
AND ((ColourTable.Darkness)=20));
```

## 其他推荐答案

```--  ** Function for creating column from colors **

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)0)
insert into @temptable(Items) values(RTRIM(LTRIM(@slice)))

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO

-- ** Create view to get all colors in one column **

Create view [dbo].[vColors]
as
select distinct items from split(
(SELECT SUBSTRING(
(SELECT ',' + color
FROM colortable
ORDER BY color
FOR XML PATH('')),2,200000) AS CSV_Color),',')

GO

-- ** And Finally get the result from this query **

select items,sum(lightness)lightness,sum(darkness)darkness
from colortable c inner join vcolors v on c.color like '%'+v.items+'%'
group by items

-- ** output is **

items   lightness   darkness
-------------------------------
black       10      10
blue        20      30
brown       33      20
creame      20      5
green       50      43
magenta     10      10
orange      20      18
purple      20      28
red         53      56
white       10      16
yellow      10      20

```

### 问题描述

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

Given a following table:

```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
```

I want to find out:

• Count of records where color has lightness 10
• Count of records where color has darkness 20

So final output would be:

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

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
```

## 其他推荐答案

If there are a known set of colours then you need to establish a table "KnownColors" for example.

```SELECT ColourTable.ID, KnownColour, ColourTable.Lightness, ColourTable.Darkness,
ColourTable.Colour, ColourTable.Lightness, ColourTable.Darkness
FROM ColourTable, knownColours WHERE (((ColourTable.Colour) Like "*"
& [KnownColour] & "*") AND ((ColourTable.Lightness)=10)
AND ((ColourTable.Darkness)=20));
```

will give you one row for each colour where lightness is 10 and darkness is 20

## 其他推荐答案

```--  ** Function for creating column from colors **

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)0)
insert into @temptable(Items) values(RTRIM(LTRIM(@slice)))

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO

-- ** Create view to get all colors in one column **

Create view [dbo].[vColors]
as
select distinct items from split(
(SELECT SUBSTRING(
(SELECT ',' + color
FROM colortable
ORDER BY color
FOR XML PATH('')),2,200000) AS CSV_Color),',')

GO

-- ** And Finally get the result from this query **

select items,sum(lightness)lightness,sum(darkness)darkness
from colortable c inner join vcolors v on c.color like '%'+v.items+'%'
group by items

-- ** output is **

items   lightness   darkness
-------------------------------
black       10      10
blue        20      30
brown       33      20
creame      20      5
green       50      43
magenta     10      10
orange      20      18
purple      20      28
red         53      56
white       10      16
yellow      10      20

```