---------------------------------------------------------------- -- 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 */
这个语句:select MAX(id) 'id' ,max(num) 'num' from tt group by snum having num>=2肯定会报错,之所以报错,是因为having num>=2 中的列num,要么是在group by中的列,而如果不在group by,那么必须有聚合函数,也就是写成你的第一个语句那样:having max(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
*/
group by snum
having num>=2肯定会报错,之所以报错,是因为having num>=2 中的列num,要么是在group by中的列,而如果不在group by,那么必须有聚合函数,也就是写成你的第一个语句那样:having max(num)>=2