存储过程混乱

Question

这是一个表格

name a  b  c   normal loan status
abc  50 60 70               normal
bcd  50 50 50               loan

我首先要得到的是a、b、c列根据它们的状态的值的总和。意义;状态正常的名称总额在正常栏，状态贷款的名称总额在贷款栏

在正常情况下我应该得到abc的总数50+60+70bcd的贷款总额为50+50+50

我该怎么做??

我尝试了如果在SP，但似乎不能得到它

Answer 1

我认为下面是你需要的。下面的查询是标准的ANSI查询:

SELECT    name
          ,SUM(CASE WHEN status = 'normal' THEN (a + b + c) ELSE 0 END)  AS normal
          ,SUM(CASE WHEN status = 'loan' THEN (a + b + c) ELSE 0 END)  AS loan
          ,status
FROM     yourTable
GROUP BY name, status

输出:

name    normal  loan    status
bcd      0      150     loan
abc      180    0       normal

根据您在评论中的要求更新现有行:

UPDATE  yourTable
SET normal = CASE WHEN status = 'normal' THEN (a + b + c) ELSE 0 END,
loan = CASE WHEN status = 'loan' THEN (a + b + c) ELSE 0 END
FROM yourTable

请注意，每次插入新记录或更改原始INSERT语句以包含这些列时，都需要更新表

Answer 2

在单次选择中你可以做到。

试

SELECT *,(CASE WHEN [status]='normal' THEN [a]+[b]+[c] ELSE null END) as normal,(CASE WHEN [status]='loan' THEN [a]+[b]+[c] ELSE null END) as loan FROM YourTable