create table ordersheet(orderno varchar(30),tot_money money,ispay varchar(10),tag int,empno int) goinsert ordersheet values('00100010102080500000028',CAST('85.00' AS MONEY),'No', 0,2) insert ordersheet values('00100010102080200000024',CAST('809.50' AS MONEY),'Yes', 0, 1) insert ordersheet values('00100010102080200000025',CAST('2,731.00' AS MONEY),'Yes', 1, 2) insert ordersheet values('00100010102080200000026',CAST('812.40' AS MONEY),'No', 2, 1) insert ordersheet values('00100010102080500000027',CAST('317.00' AS MONEY),'No', 0, 3) insert ordersheet values('00100014502080500000029',CAST('8.80' AS MONEY),'No', 2, 1)select empno 工号, count(case when left(upper(ltrim(ispay)),3)='YES' then ispay end) 到款数, count(case when left(upper(ltrim(ispay)),2)='NO' then ispay end) 未到款数, count(case when tag=1 then tag end) 退单数, count(case when tag=2 then tag end) 废单数, sum(case when left(upper(ltrim(ispay)),3)='YES' and tag=0 then tot_money end) 收款金额 from ordersheet group by empno order by empno
我很关注这个问题
goinsert ordersheet values('00100010102080500000028',CAST('85.00' AS MONEY),'No', 0,2)
insert ordersheet values('00100010102080200000024',CAST('809.50' AS MONEY),'Yes', 0, 1)
insert ordersheet values('00100010102080200000025',CAST('2,731.00' AS MONEY),'Yes', 1, 2)
insert ordersheet values('00100010102080200000026',CAST('812.40' AS MONEY),'No', 2, 1)
insert ordersheet values('00100010102080500000027',CAST('317.00' AS MONEY),'No', 0, 3)
insert ordersheet values('00100014502080500000029',CAST('8.80' AS MONEY),'No', 2, 1)select empno 工号,
count(case when left(upper(ltrim(ispay)),3)='YES' then ispay end) 到款数,
count(case when left(upper(ltrim(ispay)),2)='NO' then ispay end) 未到款数,
count(case when tag=1 then tag end) 退单数,
count(case when tag=2 then tag end) 废单数,
sum(case when left(upper(ltrim(ispay)),3)='YES' and tag=0 then tot_money end) 收款金额
from ordersheet
group by empno
order by empno