首页 文章

基于SUM的CASE语句导致查询

提问于
浏览 次
0

我有一个查询，我使用SUM来计算帐单明细值 . 我想创建一个 CASE 语句来解决该总和的结果，而不是求和的值 . 下面是我的查询目前的部分示例 .

SELECT 
    bill.billnumber_id, 
    billdetail.billclass, 
    SUM(billdetail.amount) AS Amount
GROUP BY bill.billnumber_id, billdetail.billclass

假设总共有4行 billdetail （2,0,5,3），总和为10.我想基于10个数量而不是单个值来做 CASE 语句 . 谢谢 .

sql sql-server-2008 reporting-services

4 回答

  • 0

    这是你想要的吗？

    SELECT bill.billnumber_id, billdetail.billclass, SUM(billdetail.amount) AS Amount,
       (case when SUM(billdetail.amount) > 10 then 'BigOne'
             else 'Little'
        end)
GROUP BY bill.billnumber_id, billdetail.billclass
    回复于
  • 1

    您需要使用子查询：

    SELECT CASE WHEN Amount = 0 THEN 'Nothing' ELSE 'somthing' END AmountComment
FROM(
  SELECT bill.billnumber_id, billdetail.billclass, SUM(billdetail.amount) AS Amount
  GROUP BY bill.billnumber_id, billdetail.billclass
)x
    回复于
  • 2
    SELECT bill.billnumber_id, billdetail.billclass, (Select SUM(billdetail.amount) from billdetail)
AS Amount
from bill inner join billdetail on bill.id = buildetail.kBillId 
/* include from join (not added above)*/
GROUP BY bill.billnumber_id, billdetail.billclass

    我相信这就是你所追求的......你可能想在你的内部选择语句中包含相同的 from 结构：

    SELECT bill.billnumber_id, billdetail.billclass, (Select SUM(billdetail.amount) from bill inner
join billdetail on bill.id = buildetail.kBillId ) AS Amount
from bill inner join billdetail on bill.id = buildetail.kBillId 
/* include from join (not added above)*/
GROUP BY bill.billnumber_id, billdetail.billclass

    如果这是你的意思，请告诉我 .

    回复于
  • 0

    您可以将其设为子查询：

    SELECT billnumber_id, billclass, CASE WHEN Amount = 10 THEN ... ELSE ... END
FROM
(
  SELECT bill.billnumber_id, billdetail.billclass, SUM(billdetail.amount) AS Amount
  GROUP BY bill.billnumber_id, billdetail.billclass
) A

    或者只是重复聚合：

    SELECT bill.billnumber_id, billdetail.billclass, SUM(billdetail.amount) AS Amount
     , CASE WHEN SUM(billdetail.amount) = 10 THEN ... ELSE ... END
GROUP BY bill.billnumber_id, billdetail.billclass
    回复于

相关问题