-- 测试CREATE TABLE #T(id INT,fair DECIMAL(10,2),jzflag TINYINT) INSERT #T SELECT 1,10.00,1 UNION ALL SELECT 1,5.00,2 UNION ALL SELECT 1,3.00,3 UNION ALL SELECT 2,6.00,1 UNION ALL SELECT 2,4.00,2 UNION ALL SELECT 3,5.00,1 UNION ALL SELECT 3,5.00,2 UNION ALL SELECT 3,0.5,3 UNION ALL SELECT 4,1.00,1 UNION ALL SELECT 4,2.00,2 SELECT * FROM #TSELECT id, fair1 = SUM(CASE WHEN jzflag = 1 THEN fair ELSE 0.00 END), fair2 = SUM(CASE WHEN jzflag = 2 THEN fair ELSE 0.00 END), fair3 = SUM(CASE WHEN jzflag = 3 THEN fair ELSE 0.00 END) FROM #T GROUP BY idDROP TABLE #T-- 结果 /* id fair jzflag ----------- ------------ ------ 1 10.00 1 1 5.00 2 1 3.00 3 2 6.00 1 2 4.00 2 3 5.00 1 3 5.00 2 3 .50 3 4 1.00 1 4 2.00 2(所影响的行数为 10 行)id fair1 fair2 fair3 ----------- ------------------------ 1 10.00 5.00 3.00 2 6.00 4.00 .00 3 5.00 5.00 .50 4 1.00 2.00 .00(所影响的行数为 4 行) */
如果你的每条收费记录是不定的,那就需要用动态sql了。
SELECT id, max(CASE WHEN jzflag = 1 THEN fair ELSE 0.00 END), max(CASE WHEN jzflag = 2 THEN fair ELSE 0.00 END), max(CASE WHEN jzflag = 3 THEN fair ELSE 0.00 END) FROM yourtable GROUP BY id
create table #1(id int,fair decimal(4,2),jzflag int) insert into #1 select '1','10.00',0 insert into #1 select '1','5.00',0 insert into #1 select '1','3.00',0 insert into #1 select '2','6.00',1 insert into #1 select '2','4.00',1 insert into #1 select '3','5.00',4 insert into #1 select '3','5.00',4 insert into #1 select '3','0.5',4 insert into #1 select '4','1.00',5 insert into #1 select '4','2.00',5select identity(int,1,1) IDX,* into #2 from #1select ID, isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)),0) as fair1, isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)+1),0) as fair2, isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)+2),0) as fair3, jzflag from #2 a group by ID,jzflag
INSERT #T
SELECT 1,10.00,1
UNION ALL SELECT 1,5.00,2
UNION ALL SELECT 1,3.00,3
UNION ALL SELECT 2,6.00,1
UNION ALL SELECT 2,4.00,2
UNION ALL SELECT 3,5.00,1
UNION ALL SELECT 3,5.00,2
UNION ALL SELECT 3,0.5,3
UNION ALL SELECT 4,1.00,1
UNION ALL SELECT 4,2.00,2
SELECT * FROM #TSELECT id,
fair1 = SUM(CASE WHEN jzflag = 1 THEN fair ELSE 0.00 END),
fair2 = SUM(CASE WHEN jzflag = 2 THEN fair ELSE 0.00 END),
fair3 = SUM(CASE WHEN jzflag = 3 THEN fair ELSE 0.00 END)
FROM #T
GROUP BY idDROP TABLE #T-- 结果
/*
id fair jzflag
----------- ------------ ------
1 10.00 1
1 5.00 2
1 3.00 3
2 6.00 1
2 4.00 2
3 5.00 1
3 5.00 2
3 .50 3
4 1.00 1
4 2.00 2(所影响的行数为 10 行)id fair1 fair2 fair3
----------- ------------------------
1 10.00 5.00 3.00
2 6.00 4.00 .00
3 5.00 5.00 .50
4 1.00 2.00 .00(所影响的行数为 4 行)
*/
max(CASE WHEN jzflag = 1 THEN fair ELSE 0.00 END),
max(CASE WHEN jzflag = 2 THEN fair ELSE 0.00 END),
max(CASE WHEN jzflag = 3 THEN fair ELSE 0.00 END)
FROM yourtable
GROUP BY id
insert into #1 select '1','10.00',0
insert into #1 select '1','5.00',0
insert into #1 select '1','3.00',0
insert into #1 select '2','6.00',1
insert into #1 select '2','4.00',1
insert into #1 select '3','5.00',4
insert into #1 select '3','5.00',4
insert into #1 select '3','0.5',4
insert into #1 select '4','1.00',5
insert into #1 select '4','2.00',5select identity(int,1,1) IDX,* into #2 from #1select ID,
isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)),0) as fair1,
isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)+1),0) as fair2,
isnull((select top 1 fair from #2 where ID=a.ID and IDX=min(a.idx)+2),0) as fair3,
jzflag
from #2 a
group by ID,jzflag