SELECT * FROM T1 WHERE (col4 IN (SELECT MAX(col4) FROM T1 GROUP BY COL1))
-----假定你的col2是唯一标识 select * from tablename a where col2=(select top 1 col2 from tablename where col1=a.col1 order by col4 desc)
-----如果没有唯一标识 alter table tablename add sid int identity(1,1) go select * from tablename a where sid=(select top 1 sid from tablename where col1=a.col1 order by col4 desc) go alter table tablename drop column sid
Select * from [Table] A Where Not Exists(Select * from [Table] Where col1=col1 And col4>A.col4)
--建立测试环境 if exists(select 1 from sysobjects where name='test' and xtype='U') drop table test gocreate table test ( col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 int )insert into test select 'A','A1','04-01',1 union all select 'A','A2','04-02',4 union all select 'B','B1','04-01',1 union all select 'B','B2','04-02',2 union all select 'B','B3','04-03',3--测试 select * from test where col4 in(select max(col4) from test group by col1) --删除测试环境 drop table test--结果 /* col1 col2 col3 col4 A A2 04-02 4 B B3 04-03 3 */
select * from test where col4 in (select max(col4) from test group by col1)
TO:posonhuang(自知才疏学浅) 如果我的第二条记录是: A A2 04-02 2结果将会是: * col1 col2 col3 col4 A A2 04-02 2 B B2 04-02 2 B B3 04-03 3 */ 所以这个查询是错误的
写错了一点--建立测试环境 if exists(select 1 from sysobjects where name='test' and xtype='U') drop table test gocreate table test ( col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 int )insert into test select 'A','A1','04-01',1 union all select 'A','A2','04-02',2 union all select 'B','B1','04-01',1 union all select 'B','B2','04-02',2 union all select 'B','B3','04-03',3--测试 Select * from [test] A Where Not Exists(Select * from [test] Where col1=A.col1 And col4>A.col4)--删除测试环境 drop table test--结果 /* col1 col2 col3 col4 A A2 04-02 2 B B3 04-03 3 */
数据量大的话,推荐用这条,效率高很多。--建立测试环境 if exists(select 1 from sysobjects where name='test' and xtype='U') drop table test gocreate table test ( col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 int )insert into test select 'A','A1','04-01',1 union all select 'A','A2','04-02',2 union all select 'B','B1','04-01',1 union all select 'B','B2','04-02',2 union all select 'B','B3','04-03',3--测试 Select A.* from test A Inner Join (Select col1,Max(col4) As col4 from test Group By col1) B On A.col1=B.col1 And A.col4=B.col4 Order By A.col1 --删除测试环境 drop table test--结果 /* col1 col2 col3 col4 A A2 04-02 2 B B3 04-03 3 */
select a.* from a inner join ( select col1,col4=max(col4) from a group by col1) b on (a.col1=b.col1 and a.col4=b.col4)
FROM T1
WHERE (col4 IN
(SELECT MAX(col4)
FROM T1
GROUP BY COL1))
select *
from tablename a
where col2=(select top 1 col2 from tablename where col1=a.col1 order by col4 desc)
alter table tablename add sid int identity(1,1)
go
select *
from tablename a
where sid=(select top 1 sid from tablename where col1=a.col1 order by col4 desc)
go
alter table tablename drop column sid
Where Not Exists(Select * from [Table] Where col1=col1 And col4>A.col4)
if exists(select 1 from sysobjects where name='test' and xtype='U')
drop table test
gocreate table test
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 int
)insert into test
select 'A','A1','04-01',1
union all select 'A','A2','04-02',4
union all select 'B','B1','04-01',1
union all select 'B','B2','04-02',2
union all select 'B','B3','04-03',3--测试
select * from test where col4 in(select max(col4) from test group by col1)
--删除测试环境
drop table test--结果
/*
col1 col2 col3 col4
A A2 04-02 4
B B3 04-03 3
*/
(select max(col4) from test
group by col1)
如果我的第二条记录是:
A A2 04-02 2结果将会是:
*
col1 col2 col3 col4
A A2 04-02 2
B B2 04-02 2
B B3 04-03 3
*/
所以这个查询是错误的
if exists(select 1 from sysobjects where name='test' and xtype='U')
drop table test
gocreate table test
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 int
)insert into test
select 'A','A1','04-01',1
union all select 'A','A2','04-02',2
union all select 'B','B1','04-01',1
union all select 'B','B2','04-02',2
union all select 'B','B3','04-03',3--测试
Select * from [test] A
Where Not Exists(Select * from [test] Where col1=A.col1 And col4>A.col4)--删除测试环境
drop table test--结果
/*
col1 col2 col3 col4
A A2 04-02 2
B B3 04-03 3
*/
if exists(select 1 from sysobjects where name='test' and xtype='U')
drop table test
gocreate table test
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 int
)insert into test
select 'A','A1','04-01',1
union all select 'A','A2','04-02',2
union all select 'B','B1','04-01',1
union all select 'B','B2','04-02',2
union all select 'B','B3','04-03',3--测试
Select A.* from test A
Inner Join (Select col1,Max(col4) As col4 from test Group By col1) B
On A.col1=B.col1 And A.col4=B.col4
Order By A.col1
--删除测试环境
drop table test--结果
/*
col1 col2 col3 col4
A A2 04-02 2
B B3 04-03 3
*/
select a.* from a inner join (
select col1,col4=max(col4) from a
group by col1) b on (a.col1=b.col1 and a.col4=b.col4)