select sum(col) from tb group by substring(xmdm,5,3) select avg(col) from tb group by substring(xmdm,5,3) select max(col) from tb group by substring(xmdm,5,3)
create table #zbxmb ( xmdm varchar(30) ) insert into #zbxmb select 'ZX01001001' union all select 'ZX02001001' union all select 'ZX02002001' union all select 'ZX02002001' union all select 'ZX02003001' union all select 'ZX02003001'select col1,count(xmdm) '数量' from (select substring(xmdm,5,3) as col1,xmdm from #zbxmb) T group by col1
select col1,count(xmdm) '数量' from (select substring(xmdm,5,3) as col1,xmdm from #zbxmb) T group by col1 col1 数量 ------ ----------- 001 2 002 2 003 2(3 行受影响)
insert @dwys_zbxmb select 'ZX01001001'
insert @dwys_zbxmb select 'ZX02001001'
insert @dwys_zbxmb select 'ZX02002001'
insert @dwys_zbxmb select 'ZX01002001'
insert @dwys_zbxmb select 'ZX01003001'
insert @dwys_zbxmb select 'ZX03003001'select xmdm=substring(xmdm,5,3) from @dwys_zbxmb group by substring(xmdm,5,3)xmdm
------
001
002
003(3 行受影响)
select avg(col) from tb group by substring(xmdm,5,3)
select max(col) from tb group by substring(xmdm,5,3)
(
xmdm varchar(30)
)
insert into #zbxmb select 'ZX01001001'
union all select 'ZX02001001'
union all select 'ZX02002001'
union all select 'ZX02002001'
union all select 'ZX02003001'
union all select 'ZX02003001'select col1,count(xmdm) '数量' from (select substring(xmdm,5,3) as col1,xmdm from #zbxmb) T group by col1
col1 数量
------ -----------
001 2
002 2
003 2(3 行受影响)