SELECT customername,
CASE ocname WHEN '结帐' THEN (CASE state WHEN '1' THEN SUM(totaloc)
ELSE 0 END) ELSE 0 END AS 结账,
CASE ocname WHEN '销售' THEN (CASE state WHEN '1' THEN SUM(totaloc)
ELSE 0 END) ELSE 0 END AS 销售
FROM dbo.View_totaldata
GROUP BY customername, ocname, state销售可以统计出来,结账就无法统计出来,全部是0,帮忙分析一下问题可能出在哪儿?
CASE ocname WHEN '结帐' THEN (CASE state WHEN '1' THEN SUM(totaloc)
ELSE 0 END) ELSE 0 END AS 结账,
CASE ocname WHEN '销售' THEN (CASE state WHEN '1' THEN SUM(totaloc)
ELSE 0 END) ELSE 0 END AS 销售
FROM dbo.View_totaldata
GROUP BY customername, ocname, state销售可以统计出来,结账就无法统计出来,全部是0,帮忙分析一下问题可能出在哪儿?
SELECT customername,
SUM(CASE ocname WHEN '结帐' THEN (CASE state WHEN '1' THEN totaloc
ELSE 0 END) ELSE 0 END) AS 结账,
SUM(CASE ocname WHEN '销售' THEN (CASE state WHEN '1' THEN totaloc
ELSE 0 END) ELSE 0 END) AS 销售
FROM dbo.View_totaldata
GROUP BY customername, ocname, state
SELECT customername,
SUM(CASE WHEN ocname = N'结帐' AND state = '1' THEN isnull(totaloc,0) ELSE 0 END) AS 结账,
SUM(CASE WHEN ocname = N'销售' AND state = '1' THEN isnull(totaloc,0) ELSE 0 END) AS 销售
FROM dbo.View_totaldata
GROUP BY customername, ocname, state
21001 销售 4 0
21001 销售 29 1
21001 结账 4 0
21001 结账 6 1
数据例子
sum(case when ocname='销售' and state=1 then totaloc else 0 end)
from tb group by customername
现在的问题会是与结帐这两个字有关吗?它的原始类型是VARCHAR(20),连接过程中应该不会有空字符的问题吧,关健是其它OCNAME与它来源也是一样的。
use tempdb;
/*
create table B
(
customername nvarchar(10) not null,
ocname nvarchar(10) not null,
totaloc int not null,
[state] int not null
);
insert into B(customername,ocname,totaloc,[state])
values
('21001','销售',4,0),
('21001','销售',29,1),
('21001','结账',4,0),
('21001','结账',6,1);
*/
SELECT customername,
CASE ocname WHEN N'结账' THEN (CASE [state] WHEN 1 THEN SUM(totaloc) ELSE 0 END) ELSE 0 END AS 结账,
CASE ocname WHEN N'销售' THEN (CASE [state] WHEN 1 THEN SUM(totaloc) ELSE 0 END) ELSE 0 END AS 销售
FROM B
GROUP BY customername,ocname,[state];
from dbo.View_totaldata order by customername