当前位置: 代码迷 >> Sql Server >> 急求一条SQL,该怎么处理
  详细解决方案

急求一条SQL,该怎么处理

热度:92   发布时间:2016-04-27 10:42:45.0
急:求一条SQL
我前几天问过这个问题,只搞懂了两级分类,但是三级就没有办法了,实在想不出来.就是要求出每个专题最新的一张图片

一共有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
  相关解决方案