SQL Server-尝试对表进行非规范化 [英] SQL Server - Trying to de-normalize my table

问题描述

对这个标题表示歉意，但即使是我自己解释，我也想尽我所能.

Apologies for the title but I am trying to do stuff way above my level even for me to explain it.

说我有一个表，其中包含变量人员，食品和数量:

Say I have a table with the variables person, foodstuff and amount:

 Person  food     Amount
 Mike    Butter   3
 Mike    Milk     4
 Mike    Chicken  2
 Tim     Milk     4
 John    Chicken  2

通过在查询中将表与其自身连接，我设法列出了一个列表，其中食物是新变量的基础，而值是数量.上表将变为:

By joining the table with itself in the query I have managed to make a list where the food is the basis for new variables and the value is the amount. The above table would become:

Person  Butter Milk Chicken
Mike    3      4    2

代码大致为:

Select 
    a.person, 
    b.amount as Butter,
    c.amount as Milk, 
    d.amount as Chicken
from PersonFoodAmount a
inner join PersonFoodAmount b on a.person = b.person
inner join PersonFoodAmount c on a.person=c.person
where b.food='Butter' 
and c.food='Milk'
and d.food='Chicken'

现在，这给了我Mike，因为他检查了所有箱子.但我还需要进行部分匹配:

Now, this gives me Mike because he checks all boxes off. But I need to also have the partial matches:

Person  Butter Milk Chicken
Mike    3      4    2
Tim     NULL   4    NULL
John    NULL   Null 2

我尝试了所有类型的连接，包括完全外部连接，但我仍然只能得到配备完整冰箱的人.

I have tried all kinds of joins, including full outer join but I still only get persons with a full fridge.

有什么建议吗?

推荐答案

您可以使用Pivot进行此操作.

You can use Pivot for make this.

DECLARE @PersonStuff TABLE (Person varchar(10), Food varchar(10), Amount INT)

INSERT INTO @PersonStuff VALUES
('Mike','Butter', 3),
('Mike','Milk', 4),
('Mike','Chicken', 2),
('Tim','Milk', 4),
('John','Chicken', 2)

SELECT 
    * 
FROM ( 
    SELECT 
        * 
    FROM @PersonStuff ) AS SourceTable
PIVOT ( 
    AVG(Amount) 
    FOR Food IN ( [Butter],[Milk],[Chicken] ) 
) AS PivotTable

结果:

Person  Butter  Milk    Chicken
John    NULL    NULL    2
Mike    3       4       2
Tim     NULL    4       NULL

这篇关于SQL Server-尝试对表进行非规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！