select * from 表 t
where not exists(select 1
from 表
where dev_name=t.dev_name
and type=t.type
and date_time>t.date_time)
where not exists(select 1
from 表
where dev_name=t.dev_name
and type=t.type
and date_time>t.date_time)
where date_time=(select max(date_time)
from 表
where dev_name=t.dev_name
and type=t.type )select * from 表 t
where date_time=(select top 1 date_time)
from 表
where dev_name=t.dev_name
and type=t.type
order by date_time desc)
(
ID int,
dev_name varchar(10),
type int,
date_time varchar(10)
)
insert @tb
select 1,'abc',1,'2005.09.11' union
select 2,'ad',1,'2004.09.11' union
select 3,'abc',2,'2002.09.11' union
select 4,'abc',1,'2006.09.11' --查询
select * from @tb t
where not exists(select 1
from @tb
where dev_name=t.dev_name
and type=t.type
and date_time>t.date_time)--结果
/*ID dev_name type date_time
----------- ---------- ----------- ----------
2 ad 1 2004.09.11
3 abc 2 2002.09.11
4 abc 1 2006.09.11(所影响的行数为 3 行)
*/
FROM test a INNER JOIN
(SELECT dev_name, type
FROM test
GROUP BY dev_name, type) b ON a.dev_name = b.dev_name AND a.type = b.type这样查询出来的是4条记录:1 abc 1 2005.09.11 ...
2 ad 1 ....
3 abc 2 .....
4 abc 1 2006...怎么去掉重复的记录?
FROM test a INNER JOIN
(SELECT max(id) as id , dev_name, type
FROM test
GROUP BY dev_name, type) b ON a.id = b.id AND a.dev_name = b.dev_name AND a.type = b.type
FROM Test test1 INNER JOIN
(SELECT max(test2.id) as id , test2.dev_name, test2.type
FROM Test test2
GROUP BY test2.dev_name, test2.type) test2 ON test1.id = test2.id AND test1.dev_name = test2.dev_name AND test1.type = test2.type行不?