尝试根据案例声明创建字段

问题描述：

我在查询时遇到了一些问题。我试图确定“类别”字段是A，B还是C，然后根据类别创建字段。该领域将总结支付领域。但我遇到了错误，说“附近的关键字为不正确的语法”。我在SQL视图中创建这个。使用SQL Server 2008尝试根据案例声明创建字段

SELECT r.id, r.category 
CASE 
WHEN r.category = 'A' then SUM(r.payment) As A_payments 
WHEN r.category = 'B' then SUM(r.payment) As B_payments 
WHEN r.category = 'C' then SUM(r.payment) As C_payments 
END 
FROM r_invoiceTable As r 
GROUP BY r.id, r.category

我在那里，因为我有数据有A，B和C

样品数据 - r_invoiceTable

Id --- Category ---- Payment 
222  A  ---- 50 
444  A  ---- 30 
111  B  ---- 90 
777  C  ---- 20 
555  C  ---- 40

所有上述情况下应该执行数据期望的输出 A_payments = 80，B_payments = 90，C_payments = 60

是的，但我想要有3个独立的领域......我不能只有一个领域。我需要使用基于类别的付款总和创建3个单独的字段。对于你计算相同'SUM'的每个类别，都要使用 – dido 2012-04-06 16:38:28

;使用'CASE'有什么意义？ – mshsayem 2012-04-06 16:42:36

@mshsayem每个类别都会有不同的总和。这就是为什么我需要按类别分开 – dido 2012-04-06 16:50:11

答

也许你正在寻找这样的：

SELECT 
    SUM(CASE WHEN category = 'A' THEN payment END) AS A_payments, 
    SUM(CASE WHEN category = 'B' THEN payment END) AS B_payments, 
    SUM(CASE WHEN category = 'C' THEN payment END) AS C_payments 
FROM r_invoiceTable

是的，这是我需要的，谢谢！ – dido 2012-04-06 17:43:00

答

可能是你想要的（类别明智的支付金额）？：

SELECT r.category, SUM(r.payment) As Payments 
FROM r_invoiceTable As r 
GROUP BY r.category

输出将是这样的：

category  Payments 
--------- ------------ 
    A   80 
    B   90 
    C   60

如果你喜欢在一个排在这里，这些数字是一个天真的做法：

SELECT 
    (SELECT SUM(r.payment) From r_invoiceTable As r Where r.category = 'A') as A_Payment, 
    (SELECT SUM(r.payment) From r_invoiceTable As r Where r.category = 'B') as B_Payment, 
    (SELECT SUM(r.payment) From r_invoiceTable As r Where r.category = 'C') as C_Payment

那么，上述解决方案将提供3个独立的列吗？因为在我的Web应用程序中，我必须放置3个独立的列。 – dido 2012-04-06 17:16:18

答

SELECT * 
FROM dbo.r_invoiceTable 
PIVOT 
(SUM(payment) 
FOR category IN ([a],[b],[c]) 
) AS pivoted

您必须将源中的列限制为“payment”和“category”。否则'id'和其他列（如果有的话）也会参与分组。即代替'FROM dbo.r_invoiceTable'，您需要选择'FROM（SELECT payment，category FROM dbo.r_invoiceTable）alias'。 – 2012-04-06 17:29:25

尝试根据案例声明创建字段

相关推荐