SQL：如何根据同一表中的其他列的值创建新列

问题描述：

我有2个表格，Main和Units。SQL：如何根据同一表中的其他列的值创建新列

主表：

Todate Unit CategoryID Quantity 
1/7/2012 1  S   300 
1/7/2012 1  U   350 
2/7/2012 2  S   220 
3/7/2012 2  S    50 
3/7/2012 2  U   330 
4/7/2012 1  S   200 
4/7/2012 1  U   180

S =销售，U =升级

单位表：

UnitNum UnitName 
1   Measures 
2   Performance

我需要得到这样的结果：

Todate UnitNum UnitName Sales Upgrades 
1/7/2012 1  Measures  300  350 
2/7/2012 2  Performance 220 
3/7/2012 2  Performance 50  330 
4/7/2012 1  Measures  200  180

意义我需要创建2列 - 销售和升级，这取决于CategoryID中的值，我需要它们在同一行中。我有什么到目前为止，这是

select Todate, Main.Unit, UnitName, 
case when CategoryID = 'S' then Quantity end as Sales, 
case when CategoryID = 'U' then Quantity end as Upgrades 
from Main join Units on Main.UnitNum = Units.UnitNum 
group by Todate, Main.Unit, UnitName

它给了我2个新列，但它们是两个独立的行..

我真的很感激任何帮助解决这一！谢谢

我真的建议你使用主索引（唯一）的分类。而不是U和S使另一个名为类别的表包含销售和升级，并在主表中存储每行的主要id在类别中。 – 2012-08-05 20:05:49

Matei Mihai，我没有创建这个，这只是一个问题，我被问及不能回答 – Cat 2012-08-05 20:11:29

有没有一个情况可能是2（或更多）行与'Todate'相同，相同'Unit'和相同'CategoryID'？ – 2012-08-05 20:23:33

答

您只需要围绕case语句进行聚合查询。

select m.todate 
    , m.unit 
    , u.unitname 
    , sum(case when m.categoryid = 'S' then quantity end) as sales 
    , sum(case when m.categoryid = 'U' then quantity end) as upgrages 
    from main m 
    join units u 
    on m.unit = u.unitnum 
group by m.todate, m.unit, u.unitname

嗨本，谢谢，但我仍然得到相同的结果 – Cat 2012-08-05 20:12:40

嗯，你是对的，这不会给预期的结果，但只是因为你似乎已经选择了一个随机的'单位名称'。这意味着你应该围绕这个列进行综合查询，并将它从“group by”中移除，但是在你的预期结果集中你没有明确的理由。他们为什么如预期的那样？ – Ben 2012-08-05 20:18:55

您的意思是UnitName不必在结果集中出现？这是预期的结果集，我只是复制了这个问题，因为它是 – Cat 2012-08-05 20:24:55

答

你需要做一个与自己的主表的自联接：

SELECT m1.todate, m1.unit AS unitnum, u.unitname, 
     SUM(m1.quantity) AS sales, SUM(m2.quantity) AS upgrades 
    FROM (SELECT todate, unit, quantity FROM Main WHERE category = 'S') AS m1 
    FULL OUTER JOIN 
     (SELECT todate, unit, quantity FROM Main WHERE category = 'U') AS m2 
    ON m1.todate = m2.todate AND m1.unit = m2.unit 
    JOIN units AS u 
    ON m1.unit = u.unitnum 
    GROUP BY m1.todate, m1.unit, u.unitname 
    ORDER BY m1.todate, m1.unit, u.unitname;

有编写相同的查询的许多其他等效方法;尽管如此，这个问题还是很好地保留了问题的对称性。（更新使用完全外部的m1和m2子查询之间的连接，以应对那里有销售没有升级或升级，没有销售的情况下）

这个版本忽略了我只有销售（2/7/2012 2 220）的行，它没有出现在结果中。另外，如果我添加没有单元号的行，它会完全忽略它们。 – Cat 2012-08-05 21:14:18

如果没有单元号的不完整行，你会做什么？处理另一个问题是一个麻烦 - 你需要一个完整的外部联接（假设你只能在任何一天有销售或升级）。 – 2012-08-05 23:02:36

答

你需要的东西是这样的：

SELECT 
    Todate, m.Unit, UnitName, 
    Sales = (SELECT SUM(Quantity) 
      FROM dbo.Main m2 
      WHERE m.Todate = m2.Todate AND CategoryID = 'S'), 
    Updates = (SELECT SUM(Quantity) 
       FROM dbo.Main m2 
       WHERE m.Todate = m2.Todate AND CategoryID = 'U') 
FROM 
    dbo.Main m 
INNER join 
    dbo.Units u on m.Unit = u.UnitNum 
GROUP BY 
    Todate, m.Unit, UnitName 
ORDER BY 
    Todate, m.Unit, UnitName

这似乎是回到你要找的输出：

enter image description here

看起来真的很接近，但是如果我在单位为空的情况下添加另一行，例如：2012年4月7日，null，S，15，它应该被添加为另一行，但是您的代码会将其添加到总和最后一行，我得到了2012-04-07 1措施215 180 – Cat 2012-08-05 21:07:12

@Cat：当你添加这一行时你期望什么？它会被添加到（1）给定的（2012年4月7日）的“Todate”，以及根据“CategoryID”添加到销售或更新列 - 但是在您的问题中，您没有明确说明“单元'在这里是相关的.....什么**应该**发生在一行'（2012年4月7日，NULL，'S'，15）'？它应该加到总和上吗？还是被忽略？还有什么？ – 2012-08-06 05:04:03

对不起，我从一开始就不清楚。如果未指定单位，则不应将其添加到另一行的总和中，而应该添加为另一行。我在一次采访中被问到了这个问题，我不知道具体的规则，我只是按照我给出的预期结果行事。有“无单位”的行被单独显示为一行。 – Cat 2012-08-07 11:44:34

答

你可以使用PIVOT也是这样的：

WITH data AS (
    SELECT 
    m.Todate, 
    m.UnitNum, 
    u.UnitName, 
    Category = CASE m.CategoryID 
       WHEN 'S' THEN 'Sales' 
       WHEN 'U' THEN 'Upgrades' 
       END, 
    Quantity 
    FROM Main m 
    INNER JOIN Units u 
    ON m.UnitNum = u.UnitNum 
) 
SELECT 
    Todate, 
    UnitNum, 
    UnitName, 
    Sales, 
    Upgrades 
FROM data 
PIVOT (
    SUM(Quantity) FOR Category IN (Sales, Upgrades) 
) p 
;

SQL：如何根据同一表中的其他列的值创建新列

相关推荐