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

本文是小编为大家收集整理的关于如何从具有多值字段的同一张表中找到具有不同where条件的多条记录的计数?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

我问a 问题今天早些时候,我有一个后续问题,它增加了多相关字段的复杂性.

给定下表:

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

将显示RED存在的所有记录

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

推荐答案

由于您有一个多场值列,因此最好的解决方案是制作一个新表并将所有已知颜色扔到该表中.所以你的新桌子看起来像

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));

将为您提供每一行,每行亮度为10,黑暗为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

本文地址:https://www.itbaoku.cn/post/1644525.html

问题描述

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