×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Calgary

more three hours by T-SQL VS 10 minutes by LINQ

deep_blue(BLUE)
本文发表在 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
(#5486152@0)
Last Updated: 2009-8-15
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 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)

More Topics

  • Torvalds patch improves Linux performance by 2.6%
  • GitHub Spark
  • 脑洞大开，突发奇想，用力来传播信息
  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论