现在有表:
category:
--------------
id name
1 A
2 B
3 Cproduct:
---------------------------
id img categoryid submittime
1 23423.jpg 1 2005-03-23
2 39444.jpg 2 2005-03-12
3 38953.jpg 1 2006-03-03
4 39444.jpg 2 2007-03-12要得到:
A 38953.jpg
B 39444.jpg就是列出所有类别,然后为每个类别读一张最新的图片,请问这个语句怎么写,前几天问过,还是不懂.先谢谢了.
category:
--------------
id name
1 A
2 B
3 Cproduct:
---------------------------
id img categoryid submittime
1 23423.jpg 1 2005-03-23
2 39444.jpg 2 2005-03-12
3 38953.jpg 1 2006-03-03
4 39444.jpg 2 2007-03-12要得到:
A 38953.jpg
B 39444.jpg就是列出所有类别,然后为每个类别读一张最新的图片,请问这个语句怎么写,前几天问过,还是不懂.先谢谢了.
from category a
join product b
on a.id=b.categoryid
where not exists
(select 1 from product where categoryid=b.categoryid and id>b.id)
INSERT @TA
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C'DECLARE @TB TABLE([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT @TB
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 1, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 2, '2007-03-12'SELECT A.[name],[img]
FROM @TA AS A JOIN @TB AS B ON A.id=B.[categoryid]
WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE [categoryid]=B.[categoryid] AND [submittime]>B.[submittime])
/*
name img
---- ---------
A 38953.jpg
B 39444.jpg
*/
go
create table [category]([id] int,[name] varchar(10))
insert [category] select 1,'A'
union all select 2,'B'
union all select 3,'C'if object_id('[product]') is not null drop table [product]
go
create table [product]([id] int,[img] varchar(10),[categoryid] int,[submittime] datetime)
insert [product] select 1,'23423.jpg',1,'2005-03-23'
union all select 2,'39444.jpg',2,'2005-03-12'
union all select 3,'38953.jpg',1,'2006-03-03'
union all select 4,'39444.jpg',2,'2007-03-12'select a.name,b.img
from category a
join product b
on a.id=b.categoryid
where not exists
(select 1 from product where categoryid=b.categoryid and id>b.id)
/*
name img
---------- ----------
A 38953.jpg
B 39444.jpg(2 行受影响)
*/