I am good at VB.NET, So I use VB.NET. less than 20 minutes without LINQ. -tjhong(我检讨) @卡尔加里华人论坛:卡尔加里枫下论坛 The Rolia Forum of Calgary

工作学习 / 学科技术讨论 / query hierarchical data to an unordered list in HTML...should be a challenging qry for the afternoon. prefer T-SQL. 暂时不考虑LINQ. thanks. -sunday8(每个年头都有新的梦景); 2009-8-13 {608} (#5482053@0)
table:
parent child

king jones
jones scott
scott adams
jones ford
ford smith
king blake
blake allen
blake ward
king clark
clark miller

Output:
<ul>
<li>KING
<ul><li>JONES
<ul><li>SCOTT
<ul><li>ADAMS</li></ul>
</li></ul>
<ul><li>FORD
<ul><li>SMITH</li></ul>
</li>
</ul>
</li></ul>
<ul><li>BLAKE
<ul><li>ALLEN</li>
<li>WARD</li>
</ul>
</li></ul>
<ul><li>CLARK
<ul><li>MILLER</li></ul>
</li>
</ul>
</li>
</ul>

more three hours by T-SQL VS 10 minutes by LINQ -deep_blue(BLUE); 2009-8-15 {3148} (#5486152@0)
本文发表在 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

You have given me hope.. thanks. -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488739@0)
Mixing SQL with HTML codes would be a bad practice. If I do, I would query database, wrap dataset with XML, let it become a general http/xml output, then write stylesheet to transform xml to html or pdf format for whatever UI you prefer. -nicetomeetyou(蓦然回首彩虹跨天地); 2009-8-17 (#5488828@0)

I prefer to LINQ rather any thing else. -deep_blue(BLUE); 2009-8-17 (#5488844@0)

yes. LINQ looks promising. will integrate linq into the project on next version. -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488906@0)
关键是你用了递归，和你抬一下杠：把数据简单放到DataTable,简单做个递归，不用LINQ，１０分钟也能完成。 -tjhong(我检讨); 2009-8-17 (#5488913@0)

嗯，此题关键是用递归，用LINQ是简化里面具体的实现步骤 -binghongcha76(一只大猫); 2009-8-17 (#5488973@0)
Read my code carefully. Recursion is important. Inside recursive method, it’s not necessary to use LINQ query. However, group join and ToLookup() method are all put thing very simple. -deep_blue(BLUE); 2009-8-17 (#5489223@0)

Exactly. it’s not necessary to use LINQ query. -tjhong(我检讨); 2009-8-17 {212} (#5489303@0)
I can use DataTable.Select Method intead of Enumerable.ToLookup Method. Sometimes using a loop is not a big deal. using DataColumn.Expression Property is easy to create a parent's parent column instead of join.

就是说LINQ会有些优点，这３小时和１０分钟的差异不能把帐全记在LINQ上。 -tjhong(我检讨); 2009-8-17 (#5489327@0)

First of all, I compared T-SQL with LINQ. Secondly, I suggest that you write out a non-LINQ code to see how long can you spend. -deep_blue(BLUE); 2009-8-17 (#5489481@0)

I am good at VB.NET, So I use VB.NET. less than 20 minutes without LINQ. -tjhong(我检讨); 2009-8-17 {2057} (#5489600@0)
本文发表在 rolia.net 枫下论坛Imports System.Data.SqlClient
Public Class Class1

Private Sub AAA()
Using myConn As New SqlConnection("")
Dim myComm As New SqlCommand("Select ...", myConn)
myConn.Open()
Dim myDS As New DataSet
Dim sqlA As New SqlDataAdapter(myComm)
sqlA.Fill(myDS)
Dim i As Integer
Dim StrStart As String = "<ul><li>"
Dim strEnd As String = "</li></ul>"
Dim strhtml As String = StrStart

For i = 0 To myDS.Tables(0).Rows.Count - 1
If HasParent(CStr(myDS.Tables(0).Rows(i)("Parent")), myDS.Tables(0)) = False Then
strhtml += Recursion(CStr(myDS.Tables(0).Rows(i)("Parent")), myDS.Tables(0))
End If

Next
strhtml += strEnd
End Using

End Sub

Private Function HasParent(ByVal name As String, ByVal DT As DataTable) As Boolean
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Child")) = name Then
Return True
End If
Next
Return False
End Function

Private Function HasChild(ByVal name As String, ByVal DT As DataTable) As Boolean
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Parent")) = name Then
Return True
End If
Next
Return False
End Function

Private Function Recursion(ByVal Name As String, ByVal DT As DataTable) As String

Dim StrStart As String = "<ul><li>"
Dim strEnd As String = "</li></ul>"
Dim returnStr As String = String.Format("{0}{1}", StrStart, Name)
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If HasChild(Name, DT) Then
returnStr += Recursion(CStr(DT.Rows(i)("Parent")), DT)
End If
Next
Return returnStr + strEnd
End Function

End Class更多精彩文章及讨论，请光临枫下论坛 rolia.net

有个笔误，更正版 -tjhong(我检讨); 2009-8-17 {1984} (#5489618@0)
本文发表在 rolia.net 枫下论坛Imports System.Data.SqlClient
Public Class Class1

Private Sub AAA()
Using myConn As New SqlConnection("")
Dim myComm As New SqlCommand("Select ...", myConn)
myConn.Open()
Dim myDS As New DataSet
Dim sqlA As New SqlDataAdapter(myComm)
sqlA.Fill(myDS)
Dim i As Integer
Dim StrStart As String = "<ul><li>"
Dim strEnd As String = "</li></ul>"
Dim strhtml As String = StrStart

Dim myName As String
Dim myArrayList As New ArrayList

For i = 0 To myDS.Tables(0).Rows.Count - 1
myName = CStr(myDS.Tables(0).Rows(i)("Parent"))
If HasParent(myName, myDS.Tables(0)) = False AndAlso _
Not myArrayList.Contains(myName) Then
myArrayList.Add(myName)
strhtml += Recursion(CStr(myDS.Tables(0).Rows(i)("Parent")), myDS.Tables(0))
End If

Next
strhtml += strEnd
End Using

End Sub

Private Function HasParent(ByVal name As String, ByVal DT As DataTable) As Boolean
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Child")) = name Then
Return True
End If
Next
Return False
End Function

Private Function Recursion(ByVal Name As String, ByVal DT As DataTable) As String

Dim StrStart As String = "<ul><li>"
Dim strEnd As String = "</li></ul>"
Dim returnStr As String = String.Format("{0}{1}", StrStart, Name)
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Parent")) = Name Then
returnStr += Recursion(CStr(DT.Rows(i)("Child")), DT)
End If
Next
Return returnStr + strEnd
End Function

End Class更多精彩文章及讨论，请光临枫下论坛 rolia.net

Did you test your code? I guess not. At beginning it losses root element already. -deep_blue(BLUE); 2009-8-18 (#5490966@0)

这么说就没劲了，现在给你一段测试过的代码，您老点评点评LINQ高明在哪？我看只有简单问题复杂化。 -tjhong(我检讨); 2009-8-18 {3379} (#5491039@0)
本文发表在 rolia.net 枫下论坛Imports System.Data.SqlClient
Public Class Class2

Public Sub AAA()
Dim myDS As DataSet = createTestDS()

Dim i As Integer
Dim strhtml As String = ""

Dim myName As String
Dim myArrayList As New ArrayList

For i = 0 To myDS.Tables(0).Rows.Count - 1
myName = CStr(myDS.Tables(0).Rows(i)("Parent"))
If HasParent(myName, myDS.Tables(0)) = False AndAlso _
Not myArrayList.Contains(myName) Then
myArrayList.Add(myName)
strhtml += Recursion(CStr(myDS.Tables(0).Rows(i)("Parent")), myDS.Tables(0))
End If

Next

MsgBox(strhtml)

End Sub

Private Function HasParent(ByVal name As String, ByVal DT As DataTable) As Boolean
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Child")) = name Then
Return True
End If
Next
Return False
End Function

Private Function Recursion(ByVal Name As String, ByVal DT As DataTable) As String

Dim StrStart As String = "<ul><li>"
Dim strEnd As String = "</li></ul>"
Dim returnStr As String = String.Format("{0}{1}", StrStart, Name)
Dim i As Integer
For i = 0 To DT.Rows.Count - 1
If CStr(DT.Rows(i)("Parent")) = Name Then
returnStr += Recursion(CStr(DT.Rows(i)("Child")), DT)
End If
Next
Return returnStr + strEnd
End Function

Private Function createTestDS() As DataSet
Dim myDS As New DataSet
Dim myTB As New DataTable
Dim myCol As DataColumn
myCol = New DataColumn("Parent")
myTB.Columns.Add(myCol)
myCol = New DataColumn("Child")
myTB.Columns.Add(myCol)
myDS.Tables.Add(myTB)

Dim myDR As DataRow

'king(jones)
myDR = myTB.NewRow
myDR(0) = "king"
myDR(1) = "jones"
myTB.Rows.Add(myDR)
'jones(scott)
myDR = myTB.NewRow
myDR(0) = "jones"
myDR(1) = "scott"
myTB.Rows.Add(myDR)
'scott(adams)
myDR = myTB.NewRow
myDR(0) = "scott"
myDR(1) = "adams"
myTB.Rows.Add(myDR)
'jones(ford)
myDR = myTB.NewRow
myDR(0) = "jones"
myDR(1) = "ford"
myTB.Rows.Add(myDR)
'ford(smith)
myDR = myTB.NewRow
myDR(0) = "ford"
myDR(1) = "smith"
myTB.Rows.Add(myDR)
'king(blake)
myDR = myTB.NewRow
myDR(0) = "king"
myDR(1) = "blake"
myTB.Rows.Add(myDR)
'blake(allen)
myDR = myTB.NewRow
myDR(0) = "blake"
myDR(1) = "allen"
myTB.Rows.Add(myDR)
'blake(ward)
myDR = myTB.NewRow
myDR(0) = "blake"
myDR(1) = "ward"
myTB.Rows.Add(myDR)
'king(clark)
myDR = myTB.NewRow
myDR(0) = "king"
myDR(1) = "clark"
myTB.Rows.Add(myDR)
'clark(miller)
myDR = myTB.NewRow
myDR(0) = "clark"
myDR(1) = "miller"
myTB.Rows.Add(myDR)

Return myDS
End Function

End Class更多精彩文章及讨论，请光临枫下论坛 rolia.net

Every one has his (her) own style. -deep_blue(BLUE); 2009-8-18 {176} (#5491089@0)
Most tasks can be done by different approaches, even very old ones. Following some things called best practice can make codes well-structured and easy to read and maintenance.

Come on, lookup is totally differnt from select. Check out LINQ's loopup menthod. -deep_blue(BLUE); 2009-8-17 (#5489465@0)

这个的论坛里你应该是比较用功的一位，就是缺少灵活性，DataTable.Select Method 返回值一个是数组，Lookup返回值一个是字典， -tjhong(我检讨); 2009-8-17 {121} (#5489531@0)
数组用
for (int i = 1; i <= 5; i++)
{

}

字典用 for each循环

这个地方咋就非得用字典不能用数组？

No. lookup returns groups. In tree strccture, it needs only one parent for each group, rather than every item's parent data. -deep_blue(BLUE); 2009-8-17 (#5489562@0)
这个，ToLookUp返回的不是一个简单的字典，ToDictionary才是一个字典。ToLookUp返回的是一个IEnumerable的key, value值，和字典很像，但是其Value是一个经过GroupBy的IEnumerable值。和DataTable的Select返回值性质不太一样 -binghongcha76(一只大猫); 2009-8-17 (#5489580@0)

It's just used by For Each. We don't use other features of lookup. -tjhong(我检讨); 2009-8-17 (#5489606@0)
哈哈，看你们两位一直推销LINQ，我拉开门往里偷窥一把，所以我就２天的LINQ经验，LINQ可能会有FANCY的地方。但你们两还没拿出太吸引人的地方，.NET太浩瀚，这好象非LINQ不可的地方不多。 -tjhong(我检讨); 2009-8-17 (#5489888@0)

哥们误会了，这道题我上边回帖很清楚了，重点是这个递归算法，LINQ只是使得算法实施起来更容易一些，说白了，这题其实和LINQ没多大关系 -binghongcha76(一只大猫); 2009-8-17 {557} (#5490402@0)
如果LINQ能用一行代码实现这个递归算法，那么说用LINQ来做这道题才算是名副其实。但很遗憾，C#的LINQ现在还做不到这些，F#倒是支持

LINQ的特点是提高生产力，就说这个ToLookUp()扩展函数，你如果用普通代码去实现这个函数所作的事情，要多少精力和时间？

还有GroupBy, Join等等，如果自己写同等的代码我现在想想都可怕，工作量大得不可思议，而且还要测试，因为是谁都要拥到的底层API，万一出了问题大家都完蛋。我的项目中大量运用了类似的函数，省下很多时间，而且绝对安全

本人并不想推销什么东西，只是觉得突然发现一种很好玩的东西就会贴上来，大家一起讨论，一起进步，仅此而已，呵呵

呵呵，不用扣字眼嘛，说推销也无妨，毕竟这个LINQ你们有经验，不过说实话，玩WCF，Ajax等比玩LINQ有劲，LINQ只不过是一个nice to have的东西。 -tjhong(我检讨); 2009-8-17 (#5490506@0)

想过用xsl，不过对这不是很熟，会耗时日。下个version吧。 -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488891@0)

Data, Control, and View are three different entities. Separating them in software design is required in any serious project. -nicetomeetyou(蓦然回首彩虹跨天地); 2009-8-17 (#5488907@0)

You are absolutely Right! 只是有时杀鸡不用牛刀。:) -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488922@0)

I understand.:-) I just hate to integrate html into sql code. One concern would be: What if returned string contains "<", etc special characters? It breaks your work... -nicetomeetyou(蓦然回首彩虹跨天地); 2009-8-17 (#5488944@0)

写一段procedure用cursor就成了，不是很麻烦。 -canadiantire(轮胎 - Cornelius); 2009-8-17 (#5488794@0)

no cursor pls. :) -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488877@0)

Oracle用不着，但是T-SQL, 我是4，5年以前做过这个，当时也没什么好办法，只能用cursor.tree本身就是比较慢的结构，无论是用SQL还是显示到HTML上面，如果数比较大，cursor在这里不会是瓶颈，HTML和JS的速度将会是瓶颈。。。 -canadiantire(轮胎 - Cornelius); 2009-8-17 (#5488890@0)

oracle "connect by" ? -win(秋天的菠菜); 2009-8-17 (#5488801@0)

确是从一段oracle "connect by" qry来的灵感。 -sunday8(每个年头都有新的梦景); 2009-8-17 (#5488898@0)

@Calgary

I am good at VB.NET, So I use VB.NET. less than 20 minutes without LINQ.

Replies, comments and Discussions:

More Topics