SELECT TOP 1 * FROM test
WHERE product IN
(SELECT product FROM test GROUP BY product HAVING COUNT(*) =1)
ORDER BY datetime DESC
WHERE product IN
(SELECT product FROM test GROUP BY product HAVING COUNT(*) =1)
ORDER BY datetime DESC
where not exists(select 1 from test where product=t.product and [datetime]>t.[datetime])or:select * from test t
where [datetime]=(select max([datetime]) from test where product=t.product)
a.*
from
test a
where
a.id = (select top 1 id from test where product=a.product order by [datetime] desc)
select
a.*
from
test a
where
a.id = (select max(id) from test where product=a.product)
select
a.*
from
test a
where
not exists(select 1 from test where product=a.product and [datetime]>a.[datetime])
(SELECT product FROM test GROUP BY product HAVING COUNT(*) =1)
ORDER BY datetime DESC你的不对啊,,
select * from test t
where not exists(select 1 from test where product=t.product and [datetime]>t.[datetime])or:select * from test t
where [datetime]=(select max([datetime]) from test where product=t.product)
好厉害啊,谢谢高手,揭贴了
create table test(id int,product varchar(10),price int,[datetime] datetime)
insert into test select 1,'apple' ,11,'2005-02-02'
insert into test select 2,'apple' ,22,'2005-02-06'
insert into test select 3,'apple' ,33,'2005-05-05'
insert into test select 4,'orange',44,'2005-02-03'
insert into test select 5,'orange',55,'2005-09-09'
insert into test select 6,'aa' ,66,'2005-09-01'
insert into test select 7,'bb' ,77,'2005-11-09'
--查询方式一
select
a.*
from
test a
where
a.id = (select top 1 id from test where product=a.product order by [datetime] desc)
order by
a.id
--输出结果
/*
id product price datetime
--- ------- ----- -----------------------
3 apple 33 2005-05-05 00:00:00.000
5 orange 55 2005-09-09 00:00:00.000
6 aa 66 2005-09-01 00:00:00.000
7 bb 77 2005-11-09 00:00:00.000*/
--查询方式二
select
a.*
from
test a
where
a.id = (select max(id) from test where product=a.product)
order by
a.id--输出结果
/*
id product price datetime
--- ------- ----- -----------------------
3 apple 33 2005-05-05 00:00:00.000
5 orange 55 2005-09-09 00:00:00.000
6 aa 66 2005-09-01 00:00:00.000
7 bb 77 2005-11-09 00:00:00.000*/
--查询方式三
select
a.*
from
test a
where
not exists(select 1 from test where product=a.product and [datetime]>a.[datetime])
order by
a.id--输出结果
/*
id product price datetime
--- ------- ----- -----------------------
3 apple 33 2005-05-05 00:00:00.000
5 orange 55 2005-09-09 00:00:00.000
6 aa 66 2005-09-01 00:00:00.000
7 bb 77 2005-11-09 00:00:00.000*/
--删除测试数据
drop table test
where product in(select 1 from test group by product having count(*)=1)
order by datetime desc