27,579
社区成员
发帖
与我相关
我的任务
分享
create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b
go
drop table #t
/*
b ct
---------- -----------
44 7
4401 4
4402 3
44013 3
44020 3
440131 2
440132 1
440201 1
440203 2
(9 行受影响)
*/
create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,3),count(*) from #t group by left(b,3)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b order by b desc
go
drop table #t
/*
b ct
---------- -----------
440203 2
440201 1
4402 3
440132 1
440131 2
4401 4
440 7
44 7
(8 行受影响)
*/
create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,3),count(*) from #t group by left(b,3)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b order by b desc
go
drop table #t
/*
b ct
---------- -----------
440203 2
440201 1
4402 3
440132 1
440131 2
4401 4
440 7
44 7
(8 行受影响)
*/
create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')
go
select left(b,2)b,count(*)ct from #t group by left(b,2)
union all
select left(b,4),count(*) from #t group by left(b,4)
union all
select left(b,5),count(*) from #t where len(b)=5 group by left(b,5)
union all
select b,count(*) from #t where len(b)=6 group by b
go
drop table #t
/*
b ct
---------- -----------
44 7
4401 4
4402 3
44013 3
44020 3
440131 2
440132 1
440201 1
440203 2
(9 行受影响)
*/
create table #t(a int,b varchar(10))
insert into #t values(1,'440203')
insert into #t values(2,'440203')
insert into #t values(3,'440201')
insert into #t values(4,'440132')
insert into #t values(5,'440131')
insert into #t values(6,'440131')
insert into #t values(7,'4401')
select left(b,2),count(*) from #t group by left(b,2)
select left(b,4),count(*) from #t group by left(b,4)
go
/*
(1 行受影响)
---- -----------
44 7
(1 行受影响)
-------- -----------
4401 4
4402 3
(2 行受影响)
*/