--> 测试数据:@T declare @T table([operway] varchar(1),[sxf] int) insert @T select 'h',102 union all select 'j',15 union all select 'k',12 union all select 'o',10 union all select 'd',5 union all select 'h',9 union all select 'd',3 union all select 'j',2select case operway when 'h' then 'k' when 'i' then 'k' when 'k' then 'k' else operway end ,sum(sxf) from @T group by operway
declare @T table([operway] varchar(1),[sxf] int) insert @T select 'h',102 union all select 'j',15 union all select 'k',12 union all select 'o',10 union all select 'd',5 union all select 'h',9 union all select 'd',3 union all select 'j',2 select case when [operway]in('h','k','j') then 'hkj' else [operway] end [operway], sum([sxf])[sxf] from @t group by case when [operway]in('h','k','j') then 'hkj' else [operway] end /* operway sxf ------- ----------- d 8 hkj 140 o 10(3 個資料列受到影響) */
declare @T table([operway] varchar(1),[sxf] int) insert @T select 'h',102 union all select 'j',15 union all select 'k',12 union all select 'o',10 union all select 'd',5 union all select 'h',9 union all select 'd',3 union all select 'j',2 SELECT [SUM_HKJ]=SUM(CASE WHEN [operway] IN( 'h' , 'k' , 'j') THEN [sxf] END), [SUM_D] =SUM(CASE WHEN [operway]='d' then [sxf] END) FROM @T/* SUM_HKJ SUM_D 140 8*/
谢谢上面的解答,我表述有点问题,重新改正一下。 table1 broker operway sxf 76556 h 102 76556 j 15 76556 k 12 1256 o 10 1256 d 5 326 h 9 258 d 3 159 j 2 h、k、j、o、h、一类统计他们的手续费 d 作为一类能用一个语句解决吗,谢谢各位了! 要得到类似这样结构的表 brokeid hkjoh的手续费 d的手续费
declare @T table([operway] varchar(1),[sxf] int)
insert @T
select 'h',102 union all
select 'j',15 union all
select 'k',12 union all
select 'o',10 union all
select 'd',5 union all
select 'h',9 union all
select 'd',3 union all
select 'j',2select case operway when 'h' then 'k' when 'i' then 'k' when 'k' then 'k' else operway end ,sum(sxf) from @T group by operway
insert @T
select 'h',102 union all
select 'j',15 union all
select 'k',12 union all
select 'o',10 union all
select 'd',5 union all
select 'h',9 union all
select 'd',3 union all
select 'j',2
select case when [operway]in('h','k','j') then 'hkj' else [operway] end [operway],
sum([sxf])[sxf]
from @t
group by case when [operway]in('h','k','j') then 'hkj' else [operway] end
/*
operway sxf
------- -----------
d 8
hkj 140
o 10(3 個資料列受到影響)
*/
declare @T table([operway] varchar(1),[sxf] int)
insert @T
select 'h',102 union all
select 'j',15 union all
select 'k',12 union all
select 'o',10 union all
select 'd',5 union all
select 'h',9 union all
select 'd',3 union all
select 'j',2
SELECT [SUM_HKJ]=SUM(CASE WHEN [operway] IN( 'h' , 'k' , 'j') THEN [sxf] END),
[SUM_D] =SUM(CASE WHEN [operway]='d' then [sxf] END)
FROM @T/*
SUM_HKJ SUM_D
140 8*/
table1
broker operway sxf
76556 h 102
76556 j 15
76556 k 12
1256 o 10
1256 d 5
326 h 9
258 d 3
159 j 2
h、k、j、o、h、一类统计他们的手续费
d 作为一类能用一个语句解决吗,谢谢各位了!
要得到类似这样结构的表
brokeid hkjoh的手续费 d的手续费