SQL 通过多次操作选择已完成的订单

发布于 2024-07-30 09:57:17 字数 609 浏览 8 评论 0原文

我有一个包含子操作的订单列表。 如何创建已完成订单列表? 已完成的订单必须已完成所有子操作。

表“订单”:

order_no | suboperation | finished
1        | preparing    | 01/01/2009
1        | scrubbing    | 01/05/2009
1        | painting     | 01/10/2009
2        | preparing    | 02/05/09
2        | painting     | NULL
3        | preparing    | 03/01/2009
3        | scrubbing    | 03/15/2009
3        | painting     | 03/10/2009
4        | bending      | NULL
4        | crashing     | NULL
4        | staining     | NULL
4        | painting     | NULL

期望的输出(已完成的订单):

order_no
1
3

I have a list of orders with suboperations. How can I create a list of finished orders? Finished order must have finished all suboperations.

Table "orders":

order_no | suboperation | finished
1        | preparing    | 01/01/2009
1        | scrubbing    | 01/05/2009
1        | painting     | 01/10/2009
2        | preparing    | 02/05/09
2        | painting     | NULL
3        | preparing    | 03/01/2009
3        | scrubbing    | 03/15/2009
3        | painting     | 03/10/2009
4        | bending      | NULL
4        | crashing     | NULL
4        | staining     | NULL
4        | painting     | NULL

Desired output (finished orders):

order_no
1
3

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

半衬遮猫 2024-08-06 09:57:17

您还可以使用计数分组依据拥有。 这避免了必须执行任何更有效的表连接。

create table #Orders (
    order_no int,
    suboperation varchar(30),
    finished smalldatetime)

insert into #Orders values (1 , 'preparing' , '01/01/2009')
insert into #Orders values (1 , 'scrubbing' , '01/05/2009')
insert into #Orders values (1 , 'painting' , '01/10/2009')
insert into #Orders values (2 , 'preparing' , '02/05/09')
insert into #Orders values (2 , 'painting' , NULL)
insert into #Orders values (3 , 'preparing' , '03/01/2009')
insert into #Orders values (3 , 'scrubbing' , '03/15/2009')
insert into #Orders values (3 , 'painting' , '03/10/2009')
insert into #Orders values (4 , 'bending' , NULL)
insert into #Orders values (4 , 'crashing' , NULL)
insert into #Orders values (4 , 'staining' , NULL)
insert into #Orders values (4 , 'painting' , NULL)

select 
    order_no, 
    count(1) As NoOfSubtasks --count(1) gives the number of rows in the group
    count(finished) As NoFinished, --count will not count nulls
from #Stuff
group by 
     order_no
having 
    count(finished) = count(1) --if finished = number of tasks then it's complete

drop table #Orders

You'll could also use count, group by and having. This avoids having to do any table joins which is more efficient.

create table #Orders (
    order_no int,
    suboperation varchar(30),
    finished smalldatetime)

insert into #Orders values (1 , 'preparing' , '01/01/2009')
insert into #Orders values (1 , 'scrubbing' , '01/05/2009')
insert into #Orders values (1 , 'painting' , '01/10/2009')
insert into #Orders values (2 , 'preparing' , '02/05/09')
insert into #Orders values (2 , 'painting' , NULL)
insert into #Orders values (3 , 'preparing' , '03/01/2009')
insert into #Orders values (3 , 'scrubbing' , '03/15/2009')
insert into #Orders values (3 , 'painting' , '03/10/2009')
insert into #Orders values (4 , 'bending' , NULL)
insert into #Orders values (4 , 'crashing' , NULL)
insert into #Orders values (4 , 'staining' , NULL)
insert into #Orders values (4 , 'painting' , NULL)

select 
    order_no, 
    count(1) As NoOfSubtasks --count(1) gives the number of rows in the group
    count(finished) As NoFinished, --count will not count nulls
from #Stuff
group by 
     order_no
having 
    count(finished) = count(1) --if finished = number of tasks then it's complete

drop table #Orders
深陷 2024-08-06 09:57:17

一个好的 WHERE NOT EXISTS 子句应该在这里工作:

SELECT DISTINCT o.order_no
FROM orders o
WHERE NOT EXISTS (SELECT p.order_no
                  FROM orders p
                  WHERE p.order_no = o.order_no 
                  AND p.finished IS NULL)

A good ol' WHERE NOT EXISTS clause ought to work here:

SELECT DISTINCT o.order_no
FROM orders o
WHERE NOT EXISTS (SELECT p.order_no
                  FROM orders p
                  WHERE p.order_no = o.order_no 
                  AND p.finished IS NULL)
鲜肉鲜肉永远不皱 2024-08-06 09:57:17

我试图找到一种使用 MIN 函数的方法,并想出了这个:

SELECT order_no

FROM Orders

GROUP BY order_no

HAVING (MIN(ISNULL(finished, 0)) <> 0)

null 可能很烦人......

也许性能不高,但对我来说更容易理解。

I was trying to find a way to use the MIN function, and came up with this:

SELECT order_no

FROM Orders

GROUP BY order_no

HAVING (MIN(ISNULL(finished, 0)) <> 0)

null can be annoying...

not as performant, maybe, but easier for me to understand.

跨年 2024-08-06 09:57:17
SELECT order_no, suboperation, finished
FROM orders o1
WHERE NOT EXISTS(
    SELECT 1 
        FROM orders o2 
        WHERE o1.order_no = o2.order_no 
        AND o2 IS NULL )
SELECT order_no, suboperation, finished
FROM orders o1
WHERE NOT EXISTS(
    SELECT 1 
        FROM orders o2 
        WHERE o1.order_no = o2.order_no 
        AND o2 IS NULL )
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文