五维思考

学习要加,骄傲要减,机会要乘,懒惰要除。 http://www.5dthink.cn

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

一、准备工作

1、建表

先创建两个表Group、User,两表的关系是N:N

CREATE TABLE [dbo].[Group](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [GroupName] [nvarchar](50) NULL,
        CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE TABLE [dbo].[User](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserName] [nvarchar](50) NULL,
        [GroupId] [int] NULL,
        CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

2、测试数据

INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (1, N'张1', 1)
INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (2, N'张2', 2)
INSERT [dbo].[User] ([Id], [UserName], [GroupId]) VALUES (3, N'张3', 4)

INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (1, N'A')
INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (2, N'B')
INSERT [dbo].[Group] ([Id], [GroupName]) VALUES (3, N'C')

二、连接大全

1、内连接(INNER JOIN)

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g JOIN User u ON g.Id = u.GroupId

//Linq to Sql:
from g in Groups
join u in Users
on g.Id  equals u.GroupId
select new { GroupName=g.GroupName, UserName=u.UserName}

//Lambda:
Groups.Join 
(
    Users, 
    g => (Int32?)(g.Id), 
    u => u.GroupId, 
    (g, u) => new
    {
        GroupName = g.GroupName, 
        UserName = u.UserName
    }
)

2、左连接(LEFT JOIN)

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g LEFT JOIN User u ON g.Id = u.GroupId
    
//Linq to Sql:
from g in Groups
join u in Users
on g.Id  equals u.GroupId into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=g.GroupName, UserName=(grp==null)?"":grp.UserName}

//Lambda:
Groups.GroupJoin (
    Users, 
    g => (Int32?)(g.Id), 
    u => u.GroupId, 
    (g, Grp) => new  
    {
        g = g, 
        Grp = Grp
    }
).SelectMany (
    temp0 => temp0.Grp.DefaultIfEmpty (), 
    (temp0, grp) => new  
    {
        GroupName = temp0.g.GroupName, 
        UserName = (grp == null) ? "" : grp.UserName
    }
)

3、右连接(RIGHT JOIN)

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g RIGHT JOIN User u ON g.Id = u.GroupId

//Linq to Sql:
from u in Users
join g in Groups
on u.GroupId equals g.Id into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=(grp==null)?"":grp.GroupName, UserName=u.UserName}

//Lambda:
Users.GroupJoin (
    Groups, 
    u => u.GroupId, 
    g => (Int32?)(g.Id), 
    (u, Grp) => new  
    {
        u = u, 
        Grp = Grp
    }
    ).SelectMany (
    temp0 => temp0.Grp.DefaultIfEmpty (), 
    (temp0, grp) => new  
    {
        GroupName = (grp == null) ? "" : grp.GroupName, 
        UserName = temp0.u.UserName
    }
)

4、全连接(FULL OUTER JOIN)

//Sql:
SELECT g.GroupName,u.UserName FROM GroupName g FULL JOIN User u ON g.Id = u.GroupId

//Linq to Sql:
var a=from g in Groups
join u in Users
on g.Id  equals u.GroupId into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=g.GroupName, UserName=(grp==null)?"":grp.UserName};

var b=from u in Users
join g in Groups
on u.GroupId equals g.Id
into Grp
from grp in Grp.DefaultIfEmpty()
select new { GroupName=(grp==null)?"":grp.GroupName, UserName=u.UserName};

var c=a.Concat(b).Distinct();
c.Dump();

//Lambda:
Groups
   .GroupJoin (
      Users, 
      g => (Int32?)(g.Id), 
      u => u.GroupId, 
      (g, Grp) => new  
         {
            g = g, 
            Grp = Grp
         }
   )
   .SelectMany (
      temp0 => temp0.Grp.DefaultIfEmpty (), 
      (temp0, grp) => new  
         {
            GroupName = temp0.g.GroupName, 
            UserName = (grp == null) ? "" : grp.UserName
         }
   )
   .Concat (
      Users
         .GroupJoin (
            Groups, 
            u => u.GroupId, 
            g => (Int32?)(g.Id), 
            (u, Grp) => new  
               {
                  u = u, 
                  Grp = Grp
               }
         )
         .SelectMany (
            temp2 => temp2.Grp.DefaultIfEmpty (), 
            (temp2, grp) => new  
               {
                  GroupName = (grp == null) ? "" : grp.GroupName, 
                  UserName = temp2.u.UserName
               }
         )
   )
   .Distinct ()

5、笛卡儿积(CROSS JOIN)

Sql:
SELECT [t0].[GroupName], [t1].[UserName]
FROM [Group] AS [t0], [User] AS [t1]

Linq to Sql:
from g in Groups
from u in Users
select new { GroupName=g.GroupName, UserName=u.UserName}

Lambda:
Groups.SelectMany 
(
      g => Users, 
      (g, u) => 
         new  
         {
            GroupName = g.GroupName, 
            UserName = u.UserName
         }
)
posted on 2019-09-08 10:36  五维思考  阅读(3335)  评论(0编辑  收藏  举报

QQ群:1. 全栈码农【346906288】2. VBA/VSTO【2660245】