有关 C#多表查询学习

导言

在后端多表查询这一块还是不太会，主要是在左连接和innerjoin这块，上课混的时间总是要还回来的...主要是举后端的几个案例来相应学习查询的知识。所用到的例子是自己搞的C#后端，数据库用的是若依的表，有些有些微改变。

多表查询概述

1. 内连接（INNER JOIN）

• 定义：内连接只返回两个表中符合连接条件的匹配记录。如果其中一个表没有匹配的记录，则不会包含在结果中。
• 用途：适用于需要两个表中的共同数据的场景。

2. 外连接（OUTER JOIN）

外连接用于返回一个表中的所有记录，并根据条件获取另一个表中匹配的记录。外连接分为三种：左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。

a. 左连接（LEFT JOIN 或 LEFT OUTER JOIN）

• 定义：左连接返回左表中的所有记录，不论右表中是否有匹配的记录。如果右表中没有匹配的记录，则返回 NULL。
• 用途：适用于需要左表的所有数据，而右表的数据是可选的场景。

b. 右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

• 定义：右连接返回右表中的所有记录，不论左表中是否有匹配的记录。如果左表中没有匹配的记录，则返回 NULL。
• 用途：适用于需要右表的所有数据，而左表的数据是可选的场景。

c. 全连接（FULL JOIN 或 FULL OUTER JOIN）

• 定义：全连接返回两个表中的所有记录。包括左表和右表中不匹配的数据，如果某一表中没有匹配的记录，则返回 NULL。
• 用途：适用于需要获取左表和右表的所有记录，包括匹配和不匹配的数据。

举例

内连举例：

表：

用户角色表：

角色菜单表：

菜单表：

查询语句：

            
            //根据用户id返回用户所拥有的id
            //根据用户信息查所拥有的角色，
            //根据所拥有的角色查所拥有的菜单,注意去重
            //拿用户的菜单信息,返回
            var source = Db.Queryable<Sys_UserRole, Sys_Role_Menu,Sys_Menu>((a, b, c) => new object[]{
                JoinType.Inner,a.UserID == userInfo.UserID,
                JoinType.Inner,a.RoleID == b.RoleId,
                JoinType.Inner,b.MenuId == c.ID

            })//查到多余(不属于所查询用户的菜单)原因:筛选条件少了 b.MenuId == c.ID，猜测是内联和外联没筛完全数据
             .Where((a,b,c) =>(c.menuType == 'M' || c.menuType == 'C')&& b.MenuId == c.ID)
            .Select((a,b,c) => new Sys_Menu
            {
                ID = c.ID,
                name = c.name,
                ....
            })
            .Distinct()
            .ToList();

按顺序依次连接角色用户，角色菜单，菜单表，每个表之间都有一样的字段进行连接，但不知道为什么。这个语句在没有 b.MenuId == c.ID限制的话会查到多余的数据，由于不知道如何排查，故只能做些猜测，如果有大佬知道的话可以指导一下。

外连：左连接举例

表：

设备表;

关系表：

部门表：

用户表：

查询语句：

            //查设备VM，包括所属部门，正在使用的用户
            var source = Db.Queryable<Base_Equipment, Sys_DataRelation, Base_Dept, Sys_Users>((a, b, c,d) => new object[] {
                JoinType.Inner,a.ID == b.Form,
                JoinType.Inner,b.To == c.ID,
                JoinType.Left,a.UsingUserID == d.UserID,
            })
            .WhereIF(!string.IsNullOrEmpty(parm.QueryText), (a, b, c,d) => a.EquipNo.Contains(parm.QueryText) || a.EquipName.Contains(parm.QueryText))
            .Select((a, b, c,d) => new EquipmentVM
            {
                ID = a.ID,
                EquipNo = a.EquipNo,
                EquipName = a.EquipName,
                EquipmentStatus = a.EquipmentStatus,
                Remark = a.Remark,
                DeptName = c.DeptName,
                UsingUser = string.IsNullOrEmpty(a.UsingUserID) ? d.UserName:null,
                CreateTime = a.CreateTime,
                UpdateTime = a.UpdateTime,
                CreateID = a.CreateID,
                CreateName = a.CreateName,
                UpdateID = a.UpdateID,
                UpdateName = a.UpdateName
            })
            .MergeTable();

具体介绍三条连接语句：

JoinType.Inner, a.ID == b.Form,
JoinType.Inner, b.To == c.ID,

这两条语句使用内连接，意味着只返回在 Base_Equipment 和 Sys_DataRelation 之间、以及 Sys_DataRelation 和 Base_Dept 之间存在匹配记录的结果。如果某一表没有对应的匹配记录，则相关的设备信息将不会出现在最终结果中。

JoinType.Left, a.UsingUserID == d.UserID,

这里使用左连接，意味着即使在 Base_Equipment 表中的 UsingUserID 和 Sys_Users 表中的 UserID 之间没有匹配的记录，设备信息仍会被返回，UsingUser 将会是 null。这通常用于需要保留主表（在此为设备表）所有记录的情况下，同时获取相关表的可选信息。

使用内连/外连的场合

• 内连接的使用场景：

  • 确保数据完整性: 当你只希望获取两张表中匹配的数据时，使用内连接非常合适。例如，查询某个设备时，你想确认它是否有对应的部门和关系。
  • 多表联合查询时需要的主数据: 如果业务逻辑要求必须存在相关记录才能显示设备数据（如设备必须有关联部门和关系），那么内连接是理想的选择。

• 外连接的使用场景：

  • 保留主表的记录: 当你需要从主表中获取所有记录，而不管它们在其他表中是否有匹配项时，使用外连接（尤其是左连接）是适合的。例如，在设备查询中，即使没有正在使用的用户，你仍希望显示所有设备的信息。
  • 可选关联数据: 当某些关联数据是可选的，而不是必须的，比如可能某个设备当前没有分配用户，依然需要显示设备的基础信息，则适合使用左连接。

多表查询的建议

1.明确要查的数据，是要都对应，还是要保留主表，附表可以为空，选择适合的连接方式。

2.只保留关键的连接语句，减少不必要的连接语句。有多余的连接语句会产生重复的数据。

3.可适当使用去重,上面举例的.Distinct()。

4.如果查询条件过于复杂，可分多次查询。