通过多个分组联接多个表 [英] Join multiple tables by multiple grouping

问题描述

我们有一个传递控制系统，每个传递动作都存储在MSSQL Server中的Event表中.我们希望根据它们之间的关系将多个表与Event表联接，如下图所示.但是，我不确定我使用的分组方法是否正确，因为查询需要花费大量时间.您能给我澄清一下吗?如何通过多个分组将这些表联接在一起?这是我使用的JOIN子句:

We have a passing control system and every pass action is stored Event table in MSSQL Server. We want to join multiple tables with the Event table according to their relations as shown on the image below. However, I am not sure if the grouping approach that I used is correct or not because the query takes a lot of time. Could you please clarify me oh how to join these tables by multiple grouping? Here is the JOIN clause I used:

SELECT t.CardNo, t.EventTime, t1.EmployeeName, 
    t1.Status, t2.EventCH, t3.DoorName, t4.JobName, t5.DeptName 
FROM Event t 

LEFT JOIN Employee AS t1 ON t.EmployeeID = t1.ID 
LEFT JOIN EventType AS t2 ON t.EventTypeID = t2.ID 
LEFT JOIN Door AS t3 ON t.DoorID = t3.ID 
LEFT JOIN Job AS t4 ON t1.JobID = t4.ID 
LEFT JOIN Department AS t5 ON t1.DepartmentID = t5.ID

ORDER BY t.EventID Desc

更新:在下面发布了执行计划:

Update: Posted execution plan below:

推荐答案

您好吗?您尝试过创建两个CTE来对联接进行分组吗?

Hey have you tried creating two CTE's to group the joins?

因此，在一个CTE中，为Employee，Department和Job创建一个联接. 对于其他CTE，请为事件"，事件类型"和门"创建一个联接.然后最后，使用Employee ID和ID加入两个CTE.

So in one CTE create a join for Employee, Department and Job. For the other CTE create a join for Event, Eventtype and Door. Then in the end, join the two CTE's using Employee ID and ID.

将连接的表聚合在一起可能比一次性完成连接要快.顺便问一下，每个表的唯一约束如何?

Aggregating the joined tables together might be quicker than doing the joins all in one go. By the way, hows the unique constraint for each table?

;WITH a AS

(
SELECT
    t1.ID
   ,t1.EmployeeName
   ,t1.Status
   ,t4.JobName
   ,t5.DeptName
FROM
    Employee t1
LEFT JOIN Job t4
    ON t1.JobID = t4.ID
LEFT JOIN Department t5
    ON t1.DepartmentID = t5.ID
)
,b AS
(
SELECT
    t.EmployeeID
   ,t.CardNo
   ,t.EventTime
   ,t2.EventCH
   ,t3.DoorName
FROM
    [Event] t
LEFT JOIN EventType t2
    ON t.EventTypeID = t2.ID
LEFT JOIN Door t3
    ON t.DoorID = t3.ID 
)

SELECT
    *
FROM
    b
LEFT JOIN a
    ON b.EmployeeID = a.ID

这篇关于通过多个分组联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！