我前几天问过这个问题，只搞懂了两级分类，但是三级就没有办法了,实在想不出来.就是要求出每个专题最新的一张图片
一共有3个表。专题、分类和图片
结构如下:
tbcolumn:
id name
---------------
1 熊猫专题
2 自然风光专题
tbcategory:
id name columnid
--------------------------
1 大熊猫 1
2 小熊猫 1
3 青城山 2
4 九在沟 2
tbimages:
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该怎么写？
------解决方案--------------------
- SQL code
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 * from @tbcolumn--select * from @tbcategory--select * from @tbimagesselect a.[name] columnname ,c.imgfrom @tbcolumn a join @tbcategory b on a.id=b.columnid join @tbimages c on b.id=c.categoryidwhere 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 行受影响)*/
------解决方案--------------------
- SQL code
;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 [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 行受影响)
------解决方案--------------------
- SQL code
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) TB2WHERE TB2.columnid=TAB.ID