从 SQL 到 LINQ, 第7部分: 合并, TOP, 子查询 (Bill Horst)

Categories: Data, LINQ
Comments: 6 Comments
Published on: 2008 年 01 月 28 日

[原文作者]Bill Horst
[原文地址]Converting SQL to LINQ, Part 7: UNION, TOP, Subqueries (Bill Horst)

    本文假设您已阅读了本系列中此前发表的文章:

          SQL LINQ, Part 1: 基础(Bill Horst)

          SQL LINQ, Part 2: FROM SELECT(Bill Horst)

          Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators

          Converting SQL to LINQ, Part 4: Functions

Converting SQL to LINQ, Part 5: GROUP BY and HAVING

Converting SQL to LINQ, Part 6: Joins

 

    本文中我们将讨论一下合并, TOP 和子查询. 下个星期, 我打算更深入地介绍一下LEFT, RIGHT FULL OUTER JOIN. 如果你想了解关于转换SQLLINQ的其他问题, 请回复本文.

 

合并(UNION)

 

SQL, 一个UNION子句合并两个SELECT查询结果为一个数据集. VB LINQ, 可以通过在一个查询中调用Union方法, 并把第二个查询传递给这个方法可以得到相同的结果. Intersect方法也一样可以这样调用, 它返回的是两个查询的交集. Except方法返回的结果只包括那些在第一个查询中出现但不在第二个查询中出现记录.

 

SQL

SELECT CustomerID ID FROM CustomerTable

UNION

SELECT OrderID ID From OrderTable

 

 

VB

(From Contact In CustomerTable _

 Select ID = Contact.CustomerID).Union(From Shipment In OrderTable _

                                       Select ID = Shipment.OrderID)

 

 

TOP

 

SQLTOP运算符返回一个查询结果的前n. VB LINQ中的Take子句可以达到同样效果. 下面的例子详细描述了Take的功能, 同时使用了一些其他相关的子句.

 

SQL

SELECT TOP 10 * FROM CustomerTable ORDER BY CustomerID

 

 

VB

From Contact In CustomerTable Order By Contact.CustomerID Take 10

 

 

Take/Skip/While

 

    Take子句作用于位于它前面的子句, 并指定一个表示要 或返回多少条结果的数字. 查询结果中其他的记录会被丢弃.

 

    Skip子句指定了一个数字, 表示要忽略查询结果中 多少条记录. 当前面子句的结果被传递给Skip子句后, 它只返回除了前n条记录外的其他结果.

 

    Take While子句指定一个条件, 并从一个查询结果的开头获取记录, 直到这个条件为False. 

 

    Skip While子句指定一个条件, 并从一个查询结果的开头获取记录, 它会忽略前面的记录, 直到这个条件为False.

 

   举几个具体的例子, 下面的几个查询返回如下的结果:

 

VB

 

Dim digits = New Integer() {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}

 

Dim AllDigits = From int In digits

 

返回结果:

        0        3        6        9

        1        4        7

        2        5        8

 

 

SKIP

 

Dim SkipFirstTwo = From int In digits Skip 2

 

返回结果:

        2        5        8

        3        6        9

        4        7

 

 

TAKE

 

Dim TakeFirstTwo = From int In digits Take 2

 

返回结果:

        0

        1

 

 

SKIP and TAKE together

 

Dim SkipTwoTakeFive = From int In digits Skip 2 Take 5

 

返回结果:

        2        5

        3        6

        4

 

 

 

SKIP WHILE

 

Dim SkipUntilFour = From int In digits Skip While int Mod 5 <> 4

 

返回结果:

        4        7

        5        8

        6        9

 

 

TAKE WHILE

 

Dim TakeUntilThree = From int In digits _

                     Take While int = 0 OrElse int Mod 3 <> 0

 

返回结果:

        0        2

        1

 

 

子查询

 

    SQLSELECT语句可以包含子查询, 就是指一个查询使用了另一个查询的结果. VB LINQ, 子查询可以出现在任何允许使用LINQ表达式的地方, SQL子查询一样, 你可以使用括号来避免语法中的歧义.

 

SQL

SELECT OrderID, OrderDate

FROM OrderTable

WHERE CustomerID = (SELECT CustomerID

                    FROM CustomerTable

                    WHERE City = “Seattle”)

 

 

VB

From Shipment In OrderTable _

Where (From Contact In CustomerTable _

       Where Contact.City = “Seattle” _

       Select Contact.CustomerID).Contains(Shipment.CustomerID) _

Select Shipment.OrderID, Shipment.OrderDate

 

 

    还要注意的是, 因为一个查询返回的是IEnumerable, 所以你还可以对查询结果进行查询:

 

VB

Dim SeattleOrders = From Contact In CustomerTable _

                    Join Shipment In OrderTable _

                    On Contact.CustomerID Equals Shipment.CustomerID _

                    Where Contact.City = “Seattle”

 

Dim FilteredOrders = From Shipment In SeattleOrders _

                     Select Shipment.OrderID, Shipment.OrderDate

 

 

我很有兴趣知道你们对主题的看法. 下次, 我会仔细地讨论一下LEFT/RIGHT/FULL OUTER JOIN.

-      Bill Horst, VB IDE Test

6 Comments - Leave a comment
  1. Hi – I want to say thanks for an interesting post about a subject I have had an interest in for a while now. I have been looking in and reading the comments avidly so just wanted to express my thanks for providing me with some very good reading material. I look forward to more, and taking a more proactive part in the discussions here, whilst learning too!!

  2. Substantially, the post is actually the freshest on this notable topic. I fit in with your conclusions and will thirstily look forward to your future updates. Just saying thanks will not just be enough, for the phenomenal clarity in your writing. I will right away grab your rss feed to stay abreast of any updates. Good work and much success in your business dealings!

  3. Hey, nice blog with good info. I really like coming back here often. There?s only one thing that annoys me and that is the misfunctioning of comment posting. I usually get to 500 error page, and have to do the post twice.

  4. Fransisca Lai说道:

    Hello. remarkable job. I did not imagine this. This is a splendid story. Thanks!

  5. 51zzww说道:

    有点意思,思路是非常不错的……

  6. solidworks说道:

    挺详细的,谢,楼主分享!

Leave a comment


Welcome , today is 星期六, 2017 年 02 月 25 日