如何从具有多值字段的同一张表中找到具有不同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.

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

其他推荐答案

you can get your result from your table use following,

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