having 怎么用?
id snum demo num
----------- ----------- -------------------- --------------------
1 24 NULL 1
4 24 aaa 2
2 26 NULL 1
5 26 aaa 2
3 32 NULL 1
7 32 aaa 2
select MAX(id) 'id' ,max(num) 'num' from tt
group by snum
having max(num)>=2
select MAX(id) 'id' ,max(num) 'num' from tt
group by snum
having num>=2
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-26 11:44:17
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tt]
if object_id('[tt]') is not null drop table [tt]
go
create table [tt]([id] int,[snum] int,[demo] varchar(3),[num] int)
insert [tt]
select 1,24,null,1 union all
select 4,24,'aaa',2 union all
select 2,26,null,1 union all
select 5,26,'aaa',2 union all
select 3,32,null,1 union all
select 7,32,'aaa',2
--------------开始查询--------------------------
select MAX(id) 'id' ,max(num) 'num' from tt
group by snum,num
having num>=2
----------------结果----------------------------
/*
id num
----------- -----------
4 2
5 2
7 2
*/