我前几天问过这个问题,只搞懂了两级分类,但是三级就没有办法了,实在想不出来.就是要求出每个专题最新的一张图片一共有3个表。专题、分类和图片结构如下:
tbcolumn:
id name
---------------
1 熊猫专题
2 自然风光专题tbcategory:
id name columnid
--------------------------
1 大熊猫 1
2 小熊猫 1
3 青城山 2
4 九在沟 2tbimages:
id img categoryid submittime
---------------------------------------
1 23423.jpg 1 2005-03-23
2 39444.jpg 2 2005-03-12
3 38953.jpg 3 2006-03-03
4 39444.jpg 4 2007-03-12
要得到的结果是:
-----------------------
columnname img
熊猫专题 23423.jpg
自然风光专题 39444.jpg请问这条SQL该怎么写?
tbcolumn:
id name
---------------
1 熊猫专题
2 自然风光专题tbcategory:
id name columnid
--------------------------
1 大熊猫 1
2 小熊猫 1
3 青城山 2
4 九在沟 2tbimages:
id img categoryid submittime
---------------------------------------
1 23423.jpg 1 2005-03-23
2 39444.jpg 2 2005-03-12
3 38953.jpg 3 2006-03-03
4 39444.jpg 4 2007-03-12
要得到的结果是:
-----------------------
columnname img
熊猫专题 23423.jpg
自然风光专题 39444.jpg请问这条SQL该怎么写?
DECLARE @tbcolumn TABLE([id] INT, [name] VARCHAR(50))
INSERT @tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' DECLARE @tbcategory TABLE([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT @tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
DECLARE @tbimages TABLE([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT @tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12'
INSERT @tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' DECLARE @tbcategory TABLE([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT @tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
DECLARE @tbimages TABLE([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT @tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12' --select * from @tbcolumn
--select * from @tbcategory
--select * from @tbimagesselect a.[name] columnname ,c.img
from @tbcolumn a
join @tbcategory b
on a.id=b.columnid
join @tbimages c
on b.id=c.categoryid
where not exists
(select 1
from @tbcategory d
join @tbimages e
on d.id=e.categoryid
where d.[columnid]=b.[columnid]
and e.[submittime]>c.[submittime])/*
columnname img
-------------------------------------------------- ---------
熊猫专题 23423.jpg
自然风光专题 39444.jpg(2 行受影响)
*/
INSERT tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' create TABLE tbcategory([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
create TABLE tbimages([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12' ;with hgo as
(
select t.[id],t.[name] as [tname],c.[name],i.[img],i.[submittime] from tbcolumn t join tbcategory c on t.[id]=c.[columnid] join tbimages i on c.[id]=i.[categoryid]
)
select * from hgo h where not exists (select * from hgo where id=h.id and [submittime]<h.[submittime])
create TABLE tbcolumn([id] INT, [name] VARCHAR(50))
INSERT tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' create TABLE tbcategory([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
create TABLE tbimages([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12' ;with hgo as
(
select t.[id],t.[name] as [tname],c.[name],i.[img],i.[submittime] from tbcolumn t join tbcategory c on t.[id]=c.[columnid] join tbimages i on c.[id]=i.[categoryid]
)
select * from hgo h where not exists (select * from hgo where id=h.id and [submittime]>h.[submittime])
id tname name img submittime
----------- -------------------------------------------------- --------- --------- -----------------------
1 熊猫专题 大熊猫 23423.jpg 2005-03-23 00:00:00.000
2 自然风光专题 九在沟 39444.jpg 2007-03-12 00:00:00.000(2 行受影响)
(
select t.[id],t.[name] as [tname],c.[name],i.[img],i.[submittime] from tbcolumn t join tbcategory c on t.[id]=c.[columnid] join tbimages i on c.[id]=i.[categoryid]
)
select [tname],[img] from hgo h where not exists (select * from hgo where id=h.id and [submittime]>h.[submittime])tname img
-------------------------------------------------- ---------
熊猫专题 23423.jpg
自然风光专题 39444.jpg(2 行受影响)
DECLARE @tbcolumn TABLE([id] INT, [name] VARCHAR(50))
INSERT @tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' DECLARE @tbcategory TABLE([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT @tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
DECLARE @tbimages TABLE([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT @tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12'
SELECT NAME,IMG FROM @tbcolumn TAB,
(SELECT columnid,IMG
FROM @tbimages TB,
(SELECT columnid,MAX(submittime) submittime
FROM @tbcategory T
JOIN @tbimages T1
ON T.id=T1.categoryid GROUP BY columnid) TB1
WHERE TB.submittime=TB1.submittime) TB2
WHERE TB2.columnid=TAB.ID熊猫专题 23423.jpg
自然风光专题 39444.jpg
select tbcolumn.name
,
(select max(tbimages.submittime)
from tbcategory inner join tbimages on tbcategory.id=tbimages.id
where tbcategory.id=tbcolumn.id
group by tbcategory.[columnid]) as lastestTime
from tbcolumn
select tbcolumn.name
,(select img from tbimages
where submittime=(select max(tbimages.submittime)
from tbcategory inner join tbimages on tbcategory.id=tbimages.categoryid
where tbcategory.id=tbcolumn.id
group by tbcategory.[columnid]
)
) as latestImg
from tbcolumn
DECLARE @tbcolumn TABLE([id] INT, [name] VARCHAR(50))
INSERT @tbcolumn
SELECT 1, '熊猫专题' UNION ALL
SELECT 2, '自然风光专题' DECLARE @tbcategory TABLE([id] INT, [name] VARCHAR(9), [columnid] INT)
INSERT @tbcategory
SELECT 1, '大熊猫', 1 UNION ALL
SELECT 2, '小熊猫', 1 UNION ALL
SELECT 3, '青城山', 2 UNION ALL
SELECT 4, '九在沟', 2
DECLARE @tbimages TABLE([id] INT, [img] VARCHAR(9), [categoryid] INT, [submittime] DATETIME)
INSERT @tbimages
SELECT 1, '23423.jpg', 1, '2005-03-23' UNION ALL
SELECT 2, '39444.jpg', 2, '2005-03-12' UNION ALL
SELECT 3, '38953.jpg', 3, '2006-03-03' UNION ALL
SELECT 4, '39444.jpg', 4, '2007-03-12' SELECT A.NAME,B.IMG FROM @tbcolumn A right join @tbimages B on a.id=b.id
WHERE NAME IS NOT NULL