请教各位朋友:用group by分组后,如何按每组(某字段最小值)取整条记录?表M,用a分组,取每组b的最小值的整条记录(*)a b c
1 56 kl
1 45 yui
2 89 op
2 34 uy
3 12 hj
3 65 vb
我这样不行
select a,min(b)
from M
group by a我想取整条记录,谢谢
1 56 kl
1 45 yui
2 89 op
2 34 uy
3 12 hj
3 65 vb
我这样不行
select a,min(b)
from M
group by a我想取整条记录,谢谢
(select a,min(b) as minb
from M
group by a
)select m.* from m join cte on cte.a = m.a and cte.minb = m.b
select 1 from M where t.a=a
group by a
having min(b)=t.b
)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-17 09:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[M]
if object_id('[M]') is not null drop table [M]
go
create table [M]([a] int,[b] int,[c] varchar(3))
insert [M]
select 1,56,'kl' union all
select 1,45,'yui' union all
select 2,89,'op' union all
select 2,34,'uy' union all
select 3,12,'hj' union all
select 3,65,'vb'
--------------开始查询--------------------------
SELECT *
FROM m a
WHERE EXISTS ( SELECT 1
FROM ( SELECT a ,
MIN(b) b
FROM [M]
GROUP BY A
) b
WHERE a.a = b.a
AND a.b = b.b )
----------------结果----------------------------
/* (6 行受影响)
a b c
----------- ----------- ----
1 45 yui
2 34 uy
3 12 hj(3 行受影响)
*/
go
create table #test([a] int,[b] int,[c] varchar(3))
insert #test(a,b,c)
select 1,56,'kl' union all
select 1,45,'yui' union all
select 2,89,'op' union all
select 2,34,'uy' union all
select 3,12,'hj' union all
select 3,65,'vb'select
a,b,c
from
(select a,b,c,row_number() over(partition by a order by b) row from #test) a
where a.row=1
if object_id('tempdb.dbo.#test') is not null drop table #test
select 1,56,'kl'
union all
select 1,45,'yui'
union all
select 2,89,'op'union all
select 2,34,'uy'union all
select 3,12,'hj'
union all
select 3,65,'vb'select a1.* from m a1,(select a,min(b) b from m group by a) a0
where a1.a=a0.a and a1.b=a0.b
select * from m t where t.b<=all(select b from m where m.a=t.a)这样也行。
你好,请问[M]表中,假如其中有两条记录的a=a,b=b,会怎么样?
初学者,表示看不懂,能不能解释一下WHERE a.a = b.a AND a.b = b.b 是什么意思呢?为什么要这样写呢?如果能把整条语句解释一下就最好了,感激不尽!