关联表查询

Question

CREATE TABLE #t1(itemID INT,itemFatID INT,itemName VARCHAR(20))
CREATE TABLE #t2(yrID INT,itemID INT,itemYear INT,itemOrder INT)INSERT INTO #t1 
SELECT 1,0,'体育新闻' UNION ALL
SELECT 2,1,'足球' UNION ALL
SELECT 3,1,'篮球' UNION ALL
SELECT 4,1,'网球' UNION ALL
SELECT 5,0,'财经新闻' UNION ALL
SELECT 6,5,'股票' UNION ALL
SELECT 7,5,'基金'INSERT INTO #t2
--2010年度顺序
SELECT 100,1,2010,1 UNION ALL
SELECT 101,2,2010,1 UNION ALL
SELECT 102,3,2010,2 UNION ALL
SELECT 103,4,2010,3 UNION ALL
SELECT 104,5,2010,2 UNION ALL
SELECT 105,6,2010,1 UNION ALL
SELECT 106,7,2010,2 UNION ALL
--2009年度顺序
SELECT 107,1,2009,2 UNION ALL
SELECT 108,2,2009,2 UNION ALL
SELECT 109,3,2009,3 UNION ALL
SELECT 110,4,2009,1 UNION ALL
SELECT 111,5,2009,1 UNION ALL
SELECT 112,6,2009,1 UNION ALL
SELECT 113,7,2009,2--#t1 表存分类名称、父分类ID，#t2表存各个分类在各个年度的排序，想生成如下的查询
yrID itemID itemFatID itemYear itemName itemFatOrder itemOrder
100  1      0         2010     体育新闻  1           -1
101  2      1         2010     足球      1           1
102  3      1         2010     篮球      1           2
103  4      1         2010     网球      1           3
104  5      0         2010     财经新闻  2           -1
105  6      5         2010     股票      2           1
106  7      5         2010     基金      2           2
111  5      0         2009     财经新闻  1           -1
112  6      5         2009     股票      1           1
113  7      5         2009     基金      1           2
107  1      0         2009     体育新闻  2           -1
110  4      1         2009     网球      2           1
108  2      1         2009     篮球      2           2
109  3      1         2009     足球      2           3

Answer 1

CREATE TABLE #t1(itemID INT,itemFatID INT,itemName NVARCHAR(20))
CREATE TABLE #t2(yrID INT,itemID INT,itemYear INT,itemOrder INT)INSERT INTO #t1 
SELECT 1,0,N'体育新闻' UNION ALL
SELECT 2,1,N'足球' UNION ALL
SELECT 3,1,N'篮球' UNION ALL
SELECT 4,1,N'网球' UNION ALL
SELECT 5,0,N'财经新闻' UNION ALL
SELECT 6,5,N'股票' UNION ALL
SELECT 7,5,N'基金'INSERT INTO #t2
--2010年度顺序
SELECT 100,1,2010,1 UNION ALL
SELECT 101,2,2010,1 UNION ALL
SELECT 102,3,2010,2 UNION ALL
SELECT 103,4,2010,3 UNION ALL
SELECT 104,5,2010,2 UNION ALL
SELECT 105,6,2010,1 UNION ALL
SELECT 106,7,2010,2 UNION ALL
--2009年度顺序
SELECT 107,1,2009,2 UNION ALL
SELECT 108,2,2009,2 UNION ALL
SELECT 109,3,2009,3 UNION ALL
SELECT 110,4,2009,1 UNION ALL
SELECT 111,5,2009,1 UNION ALL
SELECT 112,6,2009,1 UNION ALL
SELECT 113,7,2009,2select t2.yrID,t1.itemID,t1.itemFatID,
t2.itemYear,t1.itemName,
(select itemOrder from #t2 where itemID=(case when t1.itemFatID=0 then t1.itemID else t1.itemFatID end)
and itemYear=t2.itemYear) itemFatOrder,
(case when t1.itemFatID=0 then -1 else t2.itemOrder end) itemOrder
from #t1 t1,#t2 t2 where t1.itemID=t2.itemID
order by itemYear desc,itemFatOrder,itemOrder;
/*
100 1 0 2010 体育新闻 1 -1
101 2 1 2010 足球 1 1
102 3 1 2010 篮球 1 2
103 4 1 2010 网球 1 3
104 5 0 2010 财经新闻 2 -1
105 6 5 2010 股票 2 1
106 7 5 2010 基金 2 2
111 5 0 2009 财经新闻 1 -1
112 6 5 2009 股票 1 1
113 7 5 2009 基金 1 2
107 1 0 2009 体育新闻 2 -1
110 4 1 2009 网球 2 1
108 2 1 2009 足球 2 2
109 3 1 2009 篮球 2 3
*/

Answer 2

1楼兄弟，如果展示列变成：
yrID itemID itemFatID itemYear itemFatName itemName itemFatOrder itemOrder
则SQL 这样调整，是吗？
select t2.yrID,t1.itemID,t1.itemFatID,
t2.itemYear,(CASE WHEN t1.itemFatID=0 THEN t1.itemName ELSE  (SELECT itemName FROM #t1 
                                     WHERE itemID=t1.itemFatID) END) itemFatName, t1.itemName,
(select itemOrder from #t2 where itemID=(case when t1.itemFatID=0 then t1.itemID else t1.itemFatID end)
and itemYear=t2.itemYear) itemFatOrder,
(case when t1.itemFatID=0 then -1 else t2.itemOrder end) itemOrder
from #t1 t1,#t2 t2 where t1.itemID=t2.itemID
order by itemYear desc,itemFatOrder,itemOrder