TSQL：选择父母根据条件对儿童

Question 1

我有一个母表 Orders 和一个孩子桌 Jobs 与以下样品的数据

我要选择的订单，根据下列要求

1>每个订单可能有0或更多的就业机会。没有选择了，如果它不具有任何工作。
2>用户无法工作超过一个工作的，属于相同的顺序。
例如户 1 无法工作的作业属于单1和2，因为他已经工作过的工作 1 和 4 从相同的顺序。
3>只有选择的订单有工作 Requested 状态

我有以下的查询，给我预期的结果

DECLARE @UserID INT = 2

SELECT O.OrderID
FROM Orders O
JOIN Jobs J ON J.OrderID = O.OrderID
WHERE 
J.JobStatus = 'Requested' AND
NOT EXISTS
(  
    --Must not have worked this Order
    SELECT 1 FROM Jobs J1
    WHERE J1.OrderID = O.OrderID AND J1.UserID = @UserID
)
Group By o.OrderID

SQL小提琴演示

查询加入 Jobs 表两次。我想要优化查询，并寻找一种方式来实现预期的结果，通过使用 Jobs 表只有一次，如果可能的。任何其他解决方案也表示赞赏。我可以改变的表架构，如果需要的话。

该工作表有几乎20米的行和有些时查询显示了贫穷的性能。 (是的，我们看看索引). 我认为其扫描工作表两次导致性能的问题。

Question 2

如果目的仅仅是"使用的工作表只有一次"我会尝试的东西，如：

DECLARE @UserID INT = 2
    
SELECT 
    O.OrderID
FROM 
    Orders O
    INNER JOIN Jobs J ON J.OrderID = O.OrderID  
GROUP BY
    O.OrderID
HAVING
    SUM(CASE WHEN J.JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN J.UserID = @UserId THEN 1 ELSE 0 END) = 0

SQL小提琴

优化此外，我谨建议代替 varchar 数据类型的 JobStatus 柱 tinyint 一个(并创建一个 JobStatuses 表)。一旦你 Job 表20米的记录，然后 varchar(10) 给你190兆，但是使用 tinyint 减少了列尺寸为19Mb—这个给你的小IO-阅读作业。

我将尝试分离的儿童的过滤加入它与父母。这种方法可以使用小的记忆一个单一的操作和赢得的表现，因为：

DECLARE @UserID INT = 2
DECLARE @OrderIDs TABLE (OrderID INT NOT NULL PRIMARY KEY)

INSERT IGNORE INTO @OrderIDs
SELECT
    OrderID
FROM
    Jobs
GROUP BY
    OrderID
HAVING
    SUM(CASE WHEN JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN UserID = @UserId THEN 1 ELSE 0 END) = 0

SELECT
    O.*
FROM
    Orders O
    INNER JOIN @OrderIDs ids on ids.OrderID = O.OrderID

Question 3

你可以考虑添加以下索引 Jobs 表：

CREATE INDEX idx_jobs ON Jobs (OrderID, UserID, JobStatus);

这个指数，如果使用的话，应该加速不存在子查询在您的查询以上。此外，它可用于加入 Orders 要 Jobs 在外顶级的查询(尽管SQL服务器可能会做一个索引扫描)。

Vadim Loboda · Answer 1 · 2021-11-16T09:14:13

如果目的仅仅是"使用的工作表只有一次"我会尝试的东西，如：

DECLARE @UserID INT = 2
    
SELECT 
    O.OrderID
FROM 
    Orders O
    INNER JOIN Jobs J ON J.OrderID = O.OrderID  
GROUP BY
    O.OrderID
HAVING
    SUM(CASE WHEN J.JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN J.UserID = @UserId THEN 1 ELSE 0 END) = 0

SQL小提琴

优化此外，我谨建议代替 varchar 数据类型的 JobStatus 柱 tinyint 一个(并创建一个 JobStatuses 表)。一旦你 Job 表20米的记录，然后 varchar(10) 给你190兆，但是使用 tinyint 减少了列尺寸为19Mb—这个给你的小IO-阅读作业。

我将尝试分离的儿童的过滤加入它与父母。这种方法可以使用小的记忆一个单一的操作和赢得的表现，因为：

DECLARE @UserID INT = 2
DECLARE @OrderIDs TABLE (OrderID INT NOT NULL PRIMARY KEY)

INSERT IGNORE INTO @OrderIDs
SELECT
    OrderID
FROM
    Jobs
GROUP BY
    OrderID
HAVING
    SUM(CASE WHEN JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN UserID = @UserId THEN 1 ELSE 0 END) = 0

SELECT
    O.*
FROM
    Orders O
    INNER JOIN @OrderIDs ids on ids.OrderID = O.OrderID

工作状况是实际上 ID int类型。只是为了了解目的，我保持它作为限
这一方法看起来象我甚至不必须加入与订单表。我可以直接利用的工作表获得订单id

Tim Biegeleisen · Answer 2 · 2021-11-16T08:40:46

你可以考虑添加以下索引 Jobs 表：

CREATE INDEX idx_jobs ON Jobs (OrderID, UserID, JobStatus);

这个指数，如果使用的话，应该加速不存在子查询在您的查询以上。此外，它可用于加入 Orders 要 Jobs 在外顶级的查询(尽管SQL服务器可能会做一个索引扫描)。

TSQL：选择父母根据条件对儿童

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别