本文发表在 rolia.net 枫下论坛When I used T-SQL (SQL server 2008), it took me more than three hours. And I can only get it done in a specified tree level (three). And I cannot even image how to figure it out in SQL server 2000.
If I use LINQ, it only took me about 10 minutes. And it can take any tree levels done (no limitation).
T-SQL:
DECLARE @html varchar(max)
SELECT @html = '';
With H_data (parent, child, level)
AS
(
SELECT P.parent, P.child, 0 as level FROM RAW_DATA P Left JOIN RAW_DATA C on P.parent = C.child Where C.child Is NULL
Union ALL
SELECT P.parent, P.child, level + 1 FROM RAW_DATA P JOIN H_data H ON H.child = P.parent
)
SELECT @html += CASE WHEN @html ='' THEN '' When row1 =1 THEN '</li></ul></li></ul></li></ul>' WHEN row2 = 1 THEN '</li></ul></li></ul>' WHEN row3 = 1 THEN '</li></ul>' END
+ CASE WHEN row1 = 1 AND Q.parent IS NOT NULL THEN '<ul><li>' + Q.parent ELSE '' END
+ CASE WHEN row2 = 1 AND Q.child IS NOT NULL THEN '<ul><li>' + Q.child ELSE '' END
+ CASE WHEN row3 = 1 AND Q.GChild IS NOT NULL THEN '<ul><li>' + Q.GChild ELSE '' END
+ CASE WHEN Q.GGChild IS NULL THEN '' ELSE '<ul><li>' + Q.GGChild +'</li></ul>' END FROM
(SELECT A.*, B.child AS GGChild,
ROW_NUMBER() OVER(PARTITION by A.parent ORDER by A.parent) row1,
ROW_NUMBER() OVER(PARTITION by A.child ORDER by A.child) row2,
ROW_NUMBER() OVER(PARTITION by A.GChild ORDER by A.GChild) row3
From
(SELECT P.*, S.child AS GChild FROM H_data P left JOIN H_data S on S.parent = P.child And S.level = P.level + 1
Where P.level =0 ) A left JOIN (SELECT * FROM H_data Where level = 2) B on A.GChild = B.parent ) Q
ORDER by row1, row2,row3
SELECT @html += '</li></ul></li></ul></li></ul>'
SELECT @html
LINQ:
DataContext ds = new DataContext();
List<RAW_DATA> data = ds.RAW_DATAs.ToList<RAW_DATA>();
var q = from p in data
join c in data on p.parent equals c.child
into g
from s in g.DefaultIfEmpty()
where s == null || s.child == null
select p;
string html = "";
string sTag = "<ul><li>";
string eTag = "</li></ul>";
foreach (var item in q.ToLookup(p => p.parent))
{
html += string.Format("{0}{1}", sTag, item.Key);
foreach (var c in item)
{
html += LookInto(c.child, data);
}
html += eTag;
}
private string LookInto(string parent, List<RAW_DATA> data)
{
string sTag = "<ul><li>";
string eTag = "</li></ul>";
string ret = string.Format("{0}{1}", sTag, parent);
var q = data.Where(p => p.parent.Equals(parent));
if (q.Count() == 0) return ret + eTag;
foreach (var item in q)
{
ret += LookInto(item.child, data);
}
return ret + eTag;
}
更多精彩文章及讨论，请光临枫下论坛 rolia.net