用left joinselect m.Account_Id , m.Full_Name,n.* from
Res_User_Temp m
left join
(SELECT ACCOUNT_ID,MAX(FULL_NAME)AS FULL_NAME,MAX(JBCB)AS JBCB,MAX(JBBM)AS JBBM,MAX(JBZXB)AS JBZXB,
SUM(CASE DATEPART(D,JBRQ) WHEN '1' THEN JBSS END)AS D1,
SUM(CASE DATEPART(D,JBRQ) WHEN '2' THEN JBSS END)AS D2,
SUM(CASE DATEPART(D,JBRQ) WHEN '3' THEN JBSS END)AS D3,
SUM(CASE DATEPART(D,JBRQ) WHEN '4' THEN JBSS END)AS D4,
SUM(CASE DATEPART(D,JBRQ) WHEN '5' THEN JBSS END)AS D5,
SUM(CASE DATEPART(D,JBRQ) WHEN '6' THEN JBSS END)AS D6,
SUM(CASE DATEPART(D,JBRQ) WHEN '7' THEN JBSS END)AS D7,
SUM(CASE DATEPART(D,JBRQ) WHEN '8' THEN JBSS END)AS D8,
SUM(CASE DATEPART(D,JBRQ) WHEN '9' THEN JBSS END)AS D9,
SUM(CASE DATEPART(D,JBRQ) WHEN '10' THEN JBSS END)AS D10,
SUM(CASE DATEPART(D,JBRQ) WHEN '11' THEN JBSS END)AS D11,
SUM(CASE DATEPART(D,JBRQ) WHEN '12' THEN JBSS END)AS D12,
SUM(CASE DATEPART(D,JBRQ) WHEN '13' THEN JBSS END)AS D13,
SUM(CASE DATEPART(D,JBRQ) WHEN '14' THEN JBSS END)AS D14,
SUM(CASE DATEPART(D,JBRQ) WHEN '15' THEN JBSS END)AS D15,
SUM(CASE DATEPART(D,JBRQ) WHEN '16' THEN JBSS END)AS D16,
SUM(CASE DATEPART(D,JBRQ) WHEN '17' THEN JBSS END)AS D17,
SUM(CASE DATEPART(D,JBRQ) WHEN '18' THEN JBSS END)AS D18,
SUM(CASE DATEPART(D,JBRQ) WHEN '19' THEN JBSS END)AS D19,
SUM(CASE DATEPART(D,JBRQ) WHEN '20' THEN JBSS END)AS D20,
SUM(CASE DATEPART(D,JBRQ) WHEN '21' THEN JBSS END)AS D21,
SUM(CASE DATEPART(D,JBRQ) WHEN '22' THEN JBSS END)AS D22,
SUM(CASE DATEPART(D,JBRQ) WHEN '23' THEN JBSS END)AS D23,
SUM(CASE DATEPART(D,JBRQ) WHEN '24' THEN JBSS END)AS D24,
SUM(CASE DATEPART(D,JBRQ) WHEN '25' THEN JBSS END)AS D25,
SUM(CASE DATEPART(D,JBRQ) WHEN '26' THEN JBSS END)AS D26,
SUM(CASE DATEPART(D,JBRQ) WHEN '27' THEN JBSS END)AS D27,
SUM(CASE DATEPART(D,JBRQ) WHEN '28' THEN JBSS END)AS D28,
SUM(CASE DATEPART(D,JBRQ) WHEN '29' THEN JBSS END)AS D29,
SUM(CASE DATEPART(D,JBRQ) WHEN '30' THEN JBSS END)AS D30,
SUM(CASE DATEPART(D,JBRQ) WHEN '31' THEN JBSS END)AS D31 FROM CQ_JB
GROUP BY ACCOUNT_ID,JBZXB
) n
on m.Account_Id = n.Account_Id
Res_User_Temp m
left join
(SELECT ACCOUNT_ID,MAX(FULL_NAME)AS FULL_NAME,MAX(JBCB)AS JBCB,MAX(JBBM)AS JBBM,MAX(JBZXB)AS JBZXB,
SUM(CASE DATEPART(D,JBRQ) WHEN '1' THEN JBSS END)AS D1,
SUM(CASE DATEPART(D,JBRQ) WHEN '2' THEN JBSS END)AS D2,
SUM(CASE DATEPART(D,JBRQ) WHEN '3' THEN JBSS END)AS D3,
SUM(CASE DATEPART(D,JBRQ) WHEN '4' THEN JBSS END)AS D4,
SUM(CASE DATEPART(D,JBRQ) WHEN '5' THEN JBSS END)AS D5,
SUM(CASE DATEPART(D,JBRQ) WHEN '6' THEN JBSS END)AS D6,
SUM(CASE DATEPART(D,JBRQ) WHEN '7' THEN JBSS END)AS D7,
SUM(CASE DATEPART(D,JBRQ) WHEN '8' THEN JBSS END)AS D8,
SUM(CASE DATEPART(D,JBRQ) WHEN '9' THEN JBSS END)AS D9,
SUM(CASE DATEPART(D,JBRQ) WHEN '10' THEN JBSS END)AS D10,
SUM(CASE DATEPART(D,JBRQ) WHEN '11' THEN JBSS END)AS D11,
SUM(CASE DATEPART(D,JBRQ) WHEN '12' THEN JBSS END)AS D12,
SUM(CASE DATEPART(D,JBRQ) WHEN '13' THEN JBSS END)AS D13,
SUM(CASE DATEPART(D,JBRQ) WHEN '14' THEN JBSS END)AS D14,
SUM(CASE DATEPART(D,JBRQ) WHEN '15' THEN JBSS END)AS D15,
SUM(CASE DATEPART(D,JBRQ) WHEN '16' THEN JBSS END)AS D16,
SUM(CASE DATEPART(D,JBRQ) WHEN '17' THEN JBSS END)AS D17,
SUM(CASE DATEPART(D,JBRQ) WHEN '18' THEN JBSS END)AS D18,
SUM(CASE DATEPART(D,JBRQ) WHEN '19' THEN JBSS END)AS D19,
SUM(CASE DATEPART(D,JBRQ) WHEN '20' THEN JBSS END)AS D20,
SUM(CASE DATEPART(D,JBRQ) WHEN '21' THEN JBSS END)AS D21,
SUM(CASE DATEPART(D,JBRQ) WHEN '22' THEN JBSS END)AS D22,
SUM(CASE DATEPART(D,JBRQ) WHEN '23' THEN JBSS END)AS D23,
SUM(CASE DATEPART(D,JBRQ) WHEN '24' THEN JBSS END)AS D24,
SUM(CASE DATEPART(D,JBRQ) WHEN '25' THEN JBSS END)AS D25,
SUM(CASE DATEPART(D,JBRQ) WHEN '26' THEN JBSS END)AS D26,
SUM(CASE DATEPART(D,JBRQ) WHEN '27' THEN JBSS END)AS D27,
SUM(CASE DATEPART(D,JBRQ) WHEN '28' THEN JBSS END)AS D28,
SUM(CASE DATEPART(D,JBRQ) WHEN '29' THEN JBSS END)AS D29,
SUM(CASE DATEPART(D,JBRQ) WHEN '30' THEN JBSS END)AS D30,
SUM(CASE DATEPART(D,JBRQ) WHEN '31' THEN JBSS END)AS D31 FROM CQ_JB
GROUP BY ACCOUNT_ID,JBZXB
) n
on m.Account_Id = n.Account_Id
SUM(CASE DATEPART(D,JBRQ) WHEN '1' THEN JBSS END)AS D1,
SUM(CASE DATEPART(D,JBRQ) WHEN '2' THEN JBSS END)AS D2,
SUM(CASE DATEPART(D,JBRQ) WHEN '3' THEN JBSS END)AS D3,
SUM(CASE DATEPART(D,JBRQ) WHEN '4' THEN JBSS END)AS D4,
SUM(CASE DATEPART(D,JBRQ) WHEN '5' THEN JBSS END)AS D5,
SUM(CASE DATEPART(D,JBRQ) WHEN '6' THEN JBSS END)AS D6,
SUM(CASE DATEPART(D,JBRQ) WHEN '7' THEN JBSS END)AS D7,
SUM(CASE DATEPART(D,JBRQ) WHEN '8' THEN JBSS END)AS D8,
SUM(CASE DATEPART(D,JBRQ) WHEN '9' THEN JBSS END)AS D9,
SUM(CASE DATEPART(D,JBRQ) WHEN '10' THEN JBSS END)AS D10,
SUM(CASE DATEPART(D,JBRQ) WHEN '11' THEN JBSS END)AS D11,
SUM(CASE DATEPART(D,JBRQ) WHEN '12' THEN JBSS END)AS D12,
SUM(CASE DATEPART(D,JBRQ) WHEN '13' THEN JBSS END)AS D13,
SUM(CASE DATEPART(D,JBRQ) WHEN '14' THEN JBSS END)AS D14,
SUM(CASE DATEPART(D,JBRQ) WHEN '15' THEN JBSS END)AS D15,
SUM(CASE DATEPART(D,JBRQ) WHEN '16' THEN JBSS END)AS D16,
SUM(CASE DATEPART(D,JBRQ) WHEN '17' THEN JBSS END)AS D17,
SUM(CASE DATEPART(D,JBRQ) WHEN '18' THEN JBSS END)AS D18,
SUM(CASE DATEPART(D,JBRQ) WHEN '19' THEN JBSS END)AS D19,
SUM(CASE DATEPART(D,JBRQ) WHEN '20' THEN JBSS END)AS D20,
SUM(CASE DATEPART(D,JBRQ) WHEN '21' THEN JBSS END)AS D21,
SUM(CASE DATEPART(D,JBRQ) WHEN '22' THEN JBSS END)AS D22,
SUM(CASE DATEPART(D,JBRQ) WHEN '23' THEN JBSS END)AS D23,
SUM(CASE DATEPART(D,JBRQ) WHEN '24' THEN JBSS END)AS D24,
SUM(CASE DATEPART(D,JBRQ) WHEN '25' THEN JBSS END)AS D25,
SUM(CASE DATEPART(D,JBRQ) WHEN '26' THEN JBSS END)AS D26,
SUM(CASE DATEPART(D,JBRQ) WHEN '27' THEN JBSS END)AS D27,
SUM(CASE DATEPART(D,JBRQ) WHEN '28' THEN JBSS END)AS D28,
SUM(CASE DATEPART(D,JBRQ) WHEN '29' THEN JBSS END)AS D29,
SUM(CASE DATEPART(D,JBRQ) WHEN '30' THEN JBSS END)AS D30,
SUM(CASE DATEPART(D,JBRQ) WHEN '31' THEN JBSS END)AS D31 FROM Res_User_Temp left join CQ_JB on m.ACCOUNT_ID = n.ACCOUNT_ID
GROUP BY m.ACCOUNT_ID
from Res_User_Temp a left join
CQ_JB b on a.Account_ID=b.Account_ID
insert into tb1 values(15264 , '王雙文' , '三廠' , '制四' , '2EE' , 2.5 , '2009/3/2')
insert into tb1 values(22625 , '呂紅虎' , '三廠' , '制四' , '2EE' , 2.5 , '2009/3/2')
insert into tb1 values(23444 , '黃攀' , '三廠' , '制四' , '2EE' , 2.5 , '2009/3/2')
insert into tb1 values(23735 , '李茂輝' , '三廠' , '制四' , '2EE' , 2.5 , '2009/3/2')
insert into tb1 values(26709 , '曹潔' , '三廠' , '制四' , '2EE' , 2.5 , '2009/3/2')
create table tb2(Account_Id int, Full_Name varchar(10), CB varchar(10), BM varchar(10), XB varchar(10))
insert into tb2 values(23735 , '李茂輝' , '三廠' , '制四' , '2EE' )
insert into tb2 values(29198 , '方亞南' , '三廠' , '制四' , '2EE' )
insert into tb2 values(29565 , '田曉' , '三廠' , '制四' , '2EE' )
insert into tb2 values(26752 , '李煥新' , '三廠' , '制四' , '2EE' )
insert into tb2 values(27401 , '李嗣標' , '三廠' , '制四' , '2EE')
insert into tb2 values(26090 , '陳勝軍' , '三廠' , '制四' , '2EE')
insert into tb2 values(29988 , '蘇麗' , '三廠' , '制四' , '2EE' )
insert into tb2 values(26473 , '孫霞' , '三廠' , '制四' , '2EE' )
insert into tb2 values(13448 , '陳強' , '三廠' , '制四' , '2EE' )
insert into tb2 values(22625 , '呂紅虎' , '三廠' , '制四' , '2EE' )
insert into tb2 values(26585 , '孟友紅' , '三廠' , '制四' , '2EE' )
insert into tb2 values(26709 , '曹潔' , '三廠' , '制四' , '2EE' )
insert into tb2 values(23444 , '黃攀' , '三廠' , '制四' , '2EE' )
insert into tb2 values(27696 , '孫美佳' , '三廠' , '制四' , '2EE' )
insert into tb2 values(30298 , '呂亞浩' , '三廠' , '制四' , '2EE' )
insert into tb2 values(30341 , '聶正標' , '三廠' , '制四' , '2EE' )
insert into tb2 values(15264 , '王雙文' , '三廠' , '制四' , '2EE' )
insert into tb2 values(28254 , '敖成芬' , '三廠' , '制四' , '2EE' )
insert into tb2 values(24287 , '田露露' , '三廠' , '制四' , '2EE' )
insert into tb2 values(20847 , '閆美琳' , '三廠' , '制四' , '2EE' )
insert into tb2 values(25202 , '林品森' , '三廠' , '制四' , '2EE' )
insert into tb2 values(25194 , '桑曉芳' , '三廠' , '制四' , '2EE' )
insert into tb2 values(13351 , '張留民' , '三廠' , '制四' , '2EE' )
insert into tb2 values(29060 , '高銀娟' , '三廠' , '制四' , '2EE')
goselect m.* , n.jbss , n.jbrq from tb2 m left join tb1 n on m.account_id = n.account_id
/*
Account_Id Full_Name CB BM XB jbss jbrq
----------- ---------- ---------- ---------- ---------- -------------------- ------------------------------------------------------
23735 李茂輝 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
29198 方亞南 三廠 制四 2EE NULL NULL
29565 田曉 三廠 制四 2EE NULL NULL
26752 李煥新 三廠 制四 2EE NULL NULL
27401 李嗣標 三廠 制四 2EE NULL NULL
26090 陳勝軍 三廠 制四 2EE NULL NULL
29988 蘇麗 三廠 制四 2EE NULL NULL
26473 孫霞 三廠 制四 2EE NULL NULL
13448 陳強 三廠 制四 2EE NULL NULL
22625 呂紅虎 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
26585 孟友紅 三廠 制四 2EE NULL NULL
26709 曹潔 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
23444 黃攀 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
27696 孫美佳 三廠 制四 2EE NULL NULL
30298 呂亞浩 三廠 制四 2EE NULL NULL
30341 聶正標 三廠 制四 2EE NULL NULL
15264 王雙文 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
28254 敖成芬 三廠 制四 2EE NULL NULL
24287 田露露 三廠 制四 2EE NULL NULL
20847 閆美琳 三廠 制四 2EE NULL NULL
25202 林品森 三廠 制四 2EE NULL NULL
25194 桑曉芳 三廠 制四 2EE NULL NULL
13351 張留民 三廠 制四 2EE NULL NULL
29060 高銀娟 三廠 制四 2EE NULL NULL(所影响的行数为 24 行)*/select m.* , n.jbss , n.jbrq from tb2 m left join tb1 n on m.account_id = n.account_id
order by case when n.jbrq is not null then 1 else 2 end , m.Account_Id
/*
Account_Id Full_Name CB BM XB jbss jbrq
----------- ---------- ---------- ---------- ---------- -------------------- ------------------------------------------------------
15264 王雙文 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
22625 呂紅虎 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
23444 黃攀 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
23735 李茂輝 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
26709 曹潔 三廠 制四 2EE 2.5 2009-03-02 00:00:00.000
13351 張留民 三廠 制四 2EE NULL NULL
13448 陳強 三廠 制四 2EE NULL NULL
20847 閆美琳 三廠 制四 2EE NULL NULL
24287 田露露 三廠 制四 2EE NULL NULL
25194 桑曉芳 三廠 制四 2EE NULL NULL
25202 林品森 三廠 制四 2EE NULL NULL
26090 陳勝軍 三廠 制四 2EE NULL NULL
26473 孫霞 三廠 制四 2EE NULL NULL
26585 孟友紅 三廠 制四 2EE NULL NULL
26752 李煥新 三廠 制四 2EE NULL NULL
27401 李嗣標 三廠 制四 2EE NULL NULL
27696 孫美佳 三廠 制四 2EE NULL NULL
28254 敖成芬 三廠 制四 2EE NULL NULL
29060 高銀娟 三廠 制四 2EE NULL NULL
29198 方亞南 三廠 制四 2EE NULL NULL
29565 田曉 三廠 制四 2EE NULL NULL
29988 蘇麗 三廠 制四 2EE NULL NULL
30298 呂亞浩 三廠 制四 2EE NULL NULL
30341 聶正標 三廠 制四 2EE NULL NULL(所影响的行数为 24 行)*/
drop table tb1 , tb2
[code=SQL]
select m.ACCOUNT_ID,m.FULL_NAME,m.CB,m.BM,m.XB,n.* from
Res_User_Temp m
left join
(SELECT ACCOUNT_ID,MAX(FULL_NAME)AS FULL_NAME,MAX(JBCB)AS JBCB,MAX(JBBM)AS JBBM,MAX(JBZXB)AS JBZXB,
SUM(CASE DATEPART(D,JBRQ) WHEN '1' THEN JBSS END)AS D1,
SUM(CASE DATEPART(D,JBRQ) WHEN '2' THEN JBSS END)AS D2,
SUM(CASE DATEPART(D,JBRQ) WHEN '3' THEN JBSS END)AS D3,
SUM(CASE DATEPART(D,JBRQ) WHEN '4' THEN JBSS END)AS D4,
SUM(CASE DATEPART(D,JBRQ) WHEN '5' THEN JBSS END)AS D5,
SUM(CASE DATEPART(D,JBRQ) WHEN '6' THEN JBSS END)AS D6,
SUM(CASE DATEPART(D,JBRQ) WHEN '7' THEN JBSS END)AS D7,
SUM(CASE DATEPART(D,JBRQ) WHEN '8' THEN JBSS END)AS D8,
SUM(CASE DATEPART(D,JBRQ) WHEN '9' THEN JBSS END)AS D9,
SUM(CASE DATEPART(D,JBRQ) WHEN '10' THEN JBSS END)AS D10,
SUM(CASE DATEPART(D,JBRQ) WHEN '11' THEN JBSS END)AS D11,
SUM(CASE DATEPART(D,JBRQ) WHEN '12' THEN JBSS END)AS D12,
SUM(CASE DATEPART(D,JBRQ) WHEN '13' THEN JBSS END)AS D13,
SUM(CASE DATEPART(D,JBRQ) WHEN '14' THEN JBSS END)AS D14,
SUM(CASE DATEPART(D,JBRQ) WHEN '15' THEN JBSS END)AS D15,
SUM(CASE DATEPART(D,JBRQ) WHEN '16' THEN JBSS END)AS D16,
SUM(CASE DATEPART(D,JBRQ) WHEN '17' THEN JBSS END)AS D17,
SUM(CASE DATEPART(D,JBRQ) WHEN '18' THEN JBSS END)AS D18,
SUM(CASE DATEPART(D,JBRQ) WHEN '19' THEN JBSS END)AS D19,
SUM(CASE DATEPART(D,JBRQ) WHEN '20' THEN JBSS END)AS D20,
SUM(CASE DATEPART(D,JBRQ) WHEN '21' THEN JBSS END)AS D21,
SUM(CASE DATEPART(D,JBRQ) WHEN '22' THEN JBSS END)AS D22,
SUM(CASE DATEPART(D,JBRQ) WHEN '23' THEN JBSS END)AS D23,
SUM(CASE DATEPART(D,JBRQ) WHEN '24' THEN JBSS END)AS D24,
SUM(CASE DATEPART(D,JBRQ) WHEN '25' THEN JBSS END)AS D25,
SUM(CASE DATEPART(D,JBRQ) WHEN '26' THEN JBSS END)AS D26,
SUM(CASE DATEPART(D,JBRQ) WHEN '27' THEN JBSS END)AS D27,
SUM(CASE DATEPART(D,JBRQ) WHEN '28' THEN JBSS END)AS D28,
SUM(CASE DATEPART(D,JBRQ) WHEN '29' THEN JBSS END)AS D29,
SUM(CASE DATEPART(D,JBRQ) WHEN '30' THEN JBSS END)AS D30,
SUM(CASE DATEPART(D,JBRQ) WHEN '31' THEN JBSS END)AS D31 FROM CQ_JB
GROUP BY ACCOUNT_ID,JBZXB
)n
on m.ACCOUNT_ID=n.ACCOUNT_ID
where CB='三廠' and BM='制四' and XB='2EE'
select m.ACCOUNT_ID,m.FULL_NAME,m.CB,m.BM,m.XB,n.* from
Res_User_Temp m
left join
(SELECT ACCOUNT_ID,MAX(FULL_NAME)AS FULL_NAME,MAX(JBCB)AS JBCB,MAX(JBBM)AS JBBM,MAX(JBZXB)AS JBZXB,
SUM(CASE DATEPART(D,JBRQ) WHEN '1' THEN JBSS END)AS D1,
SUM(CASE DATEPART(D,JBRQ) WHEN '2' THEN JBSS END)AS D2,
SUM(CASE DATEPART(D,JBRQ) WHEN '3' THEN JBSS END)AS D3,
SUM(CASE DATEPART(D,JBRQ) WHEN '4' THEN JBSS END)AS D4,
SUM(CASE DATEPART(D,JBRQ) WHEN '5' THEN JBSS END)AS D5,
SUM(CASE DATEPART(D,JBRQ) WHEN '6' THEN JBSS END)AS D6,
SUM(CASE DATEPART(D,JBRQ) WHEN '7' THEN JBSS END)AS D7,
SUM(CASE DATEPART(D,JBRQ) WHEN '8' THEN JBSS END)AS D8,
SUM(CASE DATEPART(D,JBRQ) WHEN '9' THEN JBSS END)AS D9,
SUM(CASE DATEPART(D,JBRQ) WHEN '10' THEN JBSS END)AS D10,
SUM(CASE DATEPART(D,JBRQ) WHEN '11' THEN JBSS END)AS D11,
SUM(CASE DATEPART(D,JBRQ) WHEN '12' THEN JBSS END)AS D12,
SUM(CASE DATEPART(D,JBRQ) WHEN '13' THEN JBSS END)AS D13,
SUM(CASE DATEPART(D,JBRQ) WHEN '14' THEN JBSS END)AS D14,
SUM(CASE DATEPART(D,JBRQ) WHEN '15' THEN JBSS END)AS D15,
SUM(CASE DATEPART(D,JBRQ) WHEN '16' THEN JBSS END)AS D16,
SUM(CASE DATEPART(D,JBRQ) WHEN '17' THEN JBSS END)AS D17,
SUM(CASE DATEPART(D,JBRQ) WHEN '18' THEN JBSS END)AS D18,
SUM(CASE DATEPART(D,JBRQ) WHEN '19' THEN JBSS END)AS D19,
SUM(CASE DATEPART(D,JBRQ) WHEN '20' THEN JBSS END)AS D20,
SUM(CASE DATEPART(D,JBRQ) WHEN '21' THEN JBSS END)AS D21,
SUM(CASE DATEPART(D,JBRQ) WHEN '22' THEN JBSS END)AS D22,
SUM(CASE DATEPART(D,JBRQ) WHEN '23' THEN JBSS END)AS D23,
SUM(CASE DATEPART(D,JBRQ) WHEN '24' THEN JBSS END)AS D24,
SUM(CASE DATEPART(D,JBRQ) WHEN '25' THEN JBSS END)AS D25,
SUM(CASE DATEPART(D,JBRQ) WHEN '26' THEN JBSS END)AS D26,
SUM(CASE DATEPART(D,JBRQ) WHEN '27' THEN JBSS END)AS D27,
SUM(CASE DATEPART(D,JBRQ) WHEN '28' THEN JBSS END)AS D28,
SUM(CASE DATEPART(D,JBRQ) WHEN '29' THEN JBSS END)AS D29,
SUM(CASE DATEPART(D,JBRQ) WHEN '30' THEN JBSS END)AS D30,
SUM(CASE DATEPART(D,JBRQ) WHEN '31' THEN JBSS END)AS D31 FROM CQ_JB
GROUP BY ACCOUNT_ID,JBZXB
)n
on m.ACCOUNT_ID=n.ACCOUNT_ID
where CB='三廠' and BM='制四' and XB='2EE'
isnull((select sum(JBSS) from CQ_JB where ACCOUNT_ID=m.ACCOUNT_ID and DATEPART(D,JBRQ)='1'),0) as D1,
以下类推
from Res_User_Temp m