表结构如下
col1 col2 col3
ab ac c
ad bc c
dd db c
ad am d
ae xy d
___________________
期望的返回结构为
col1 col2 col3
ab ac 3
ad am 2
———————
即col1和col2返回几行数据的任意一行(可以是第一行,也可以是第二行,也可以是任意一行)col3返回记录条数(以col3分组的记录条数)
不知道这个语句改怎么写呀,即简便效率又高,因为表的行数挺多的
col1 col2 col3
ab ac c
ad bc c
dd db c
ad am d
ae xy d
___________________
期望的返回结构为
col1 col2 col3
ab ac 3
ad am 2
———————
即col1和col2返回几行数据的任意一行(可以是第一行,也可以是第二行,也可以是任意一行)col3返回记录条数(以col3分组的记录条数)
不知道这个语句改怎么写呀,即简便效率又高,因为表的行数挺多的
if object_id('tempdb.dbo.#tb') is not null drop table #tb
create table #tb([col1] varchar(2),[col2] varchar(2),[col3] varchar(1))
insert #tb
select 'ab','ac','c' union all
select 'ad','bc','c' union all
select 'dd','db','c' union all
select 'ad','am','d' union all
select 'ae','xy','d'
--期望的返回结构为
--col1 col2 col3
-- ab ac 3
-- ad am 2;with vvv as (
select [col1],[col2],b.c3 from #tb a
join (select [col3],count([col3]) as c3 from #tb group by [col3]) b
on a.col3=b.[col3] )select * from vvv v where not exists(select 1 from vvv where c3=v.c3 and col1< v.col1 )
from tb A
group by col3
create table #tb([col1] varchar(2),[col2] varchar(2),[col3] varchar(1))
insert #tb
select 'ab','ac','c' union all
select 'ad','bc','c' union all
select 'dd','db','c' union all
select 'ad','am','d' union all
select 'ae','xy','d'
select t2.col1,t2.col2,t1.num
from (select COUNT(*) as num,col3 from #tb group by col3) t1
cross apply (select top (1) col1,col2 from #tb t2 where t1.col3=t2.col3 order by NEWID()) t2
---------------
col1 col2 num
ab ac 3
ad am 2
Create table ttt
(
col1 varchar(100),
col2 varchar(100),
col3 varchar(100)
)
insert into ttt values('ab','ac','c')
insert into ttt values('ad','bc','c')
insert into ttt values('dd','db','c')
insert into ttt values('ad','am','d' )
insert into ttt values('ae','xy','d')
--drop table ttt
Create table #ttt
(
col1 varchar(100),
col2 varchar(100),
col3 int
)
insert into #ttt
select col1,col2,num from
(select col3 ,count(col3) as num from ttt group by col3) a
inner join ttt b on a.col3=b.col3select col1,col2,col3
from
(
select *,row_number() over(patition by col3 order by col1) as id from #ttt
)
where id=1
create table #tb([col1] varchar(2),[col2] varchar(2),[col3] varchar(1))
insert #tb
select 'ab','ac','c' union all
select 'ad','bc','c' union all
select 'dd','db','c' union all
select 'ad','am','d' union all
select 'ae','xy','d'
select col1,col2,(select count(*) from #tb where col3=a.col3) num
from #tb a where exists(select * from (select top 1 * from #tb where col3=a.col3 order by col1,col2) as b where col1=a.col1 and col2=a.col2)