使用SQL选择第1列中具有相同值但在第2列和第3列中具有不同值的所有行

I have a table which looks like this

我有一张看起来像这样的桌子

Each [Order number] can have 1 or multiple [Line number] and each [Line number] can have status [SHIPPED] OR [UNSHIPPED].

每个[订单号]可以有1个或多个[行号],每个[行号]可以具有[SHIPPED]或[UNSHIPPED]状态。

I want to select all the [Order number] which contain [Line number] having both [SHIPPED] and [UNSHIPPED].

我想选择所有[订单号],其中包含[行号]同时包含[已运输]和[未签名]。

For example these [Order number] contain [Line number] with [SHIPPED] and [UNSHIPPED] at the same time so it should be selected

例如,这些[订单号]包含[行号]同时带[SHIPPED]和[UNSHIPPED],因此应该选择它

Here is my query but it doesn't return the correct result

这是我的查询但它没有返回正确的结果

SELECT [Order number], [Line number], [SHIPPED/UNSHIPPED] 
FROM [mytable]
WHERE [Order number] IN (SELECT [Order number]
                         FROM [mytable]
                         GROUP BY [Order number]
                         HAVING COUNT(*) > 1)
ORDER BY [Order number], [Line number]

Any suggestions please what is missing in my query ? Thank you.

请问我的查询中缺少什么?谢谢。

5 个解决方案

#1

Here is one approach:

这是一种方法:

WITH cte AS (
    SELECT [Order number]
    FROM mytable
    WHERE [SHIPPED/UNSHIPPED] IN ('SHIPPED', 'UNSHIPPED')
    GROUP BY [Order number]
    HAVING COUNT(DISTINCT [SHIPPED/UNSHIPPED]) = 2
)

SELECT *
FROM mytable
WHERE [Order number] IN (SELECT [Order number] FROM cte);

The CTE finds all order numbers which have both shipped and unshipped records. It works by first restricting a given order's records to only those having shipped/unshipped, then it asserts that the distinct count of that group is 2, implying both types of shipments are present.

CTE查找所有包含已发货和未发货记录的订单号。它的工作原理是首先将给定订单的记录限制为仅已发货/未发货的订单,然后声明该组的独特计数为2,这意味着两种类型的货物都存在。

#2

your query was almost right just need a little bit change i did that

你的查询几乎是正确的,我需要做一点改变

    SELECT [Order number],[Line number],[SHIPPED/UNSHIPPED] FROM [mytable]
    WHERE [Order number] IN (
    SELECT [Order number]
    FROM [mytable]
    WHERE [SHIPPED/UNSHIPPED] IN ('SHIPPED', 'UNSHIPPED') --added where clause
    GROUP BY [Order number]
    HAVING COUNT(DISTINCT [SHIPPED/UNSHIPPED]) >= 2 --changed this condition
    )
    ORDER BY [Order number],[Line number]

#3

In opposite to other answer, I think, you mean that [Line number] has to have both statuses, i.e.:

与其他答案相反,我认为,你的意思是[行号]必须具有两种状态,即:

OrderNumber LineNumber UNSHIPPED/SHIPPED
1           20         SHIPPED
1           20         UNSHIPPED
2           30         SHIPPEd
2           40         UNSHIPPED

then, required reuslt would be only [Order number] = 1, since it has line with both statuses.

然后,所需的reuslt将仅[订单号] = 1,因为它具有两种状态的行。

Accordingly to this logic, here's query:

根据这个逻辑,这里的查询:

SELECT OrderNumber,
       LineNumber,
       [Unshipped/Shipped]
FROM (
    SELECT OrderNumber,
           LineNumber,
           [Unshipped/Shipped],
           COUNT(DISTINCT [Unshipped/Shipped]) OVER (PARTITION BY OrderNumber, LineNumber) cnt
    FROM my_table
    WHERE [Unshipped/Shipped] IS NOT NULL
) a WHERE cnt > 1

Or with GROUP BY:

或者使用GROUP BY:

SELECT OrderNumber,
       LineNumber
FROM my_table
WHERE [Unshipped/Shipped] IS NOT NULL
GROUP BY OrderNumber, LineNumber
HAVING COUNT(DISTINCT [Unshipped/Shipped]) > 1

#4

another

with
    shipped as (select distinct [Order number] from mytable where [SHIPPED/UNSHIPPED] = 'SHIPPED'),
    unshipped as (select distinct [Order number] from mytable where [SHIPPED/UNSHIPPED] = 'UNSHIPPED'),
    both as (select [Order number] from shipped intersect select [Order number] from unshipped)

select * from mytable where [Order number] in (select [Order number] from both)

#5

I want to select all the [Order number] which contain [Line number] having both [SHIPPED] and [UNSHIPPED].

我想选择所有[订单号],其中包含[行号]同时包含[已运输]和[未签名]。

Your question is specifically asking only about orders, so the result set should have one row per order. I would generally approach this using aggregation:

您的问题只是询问订单,因此结果集每个订单应该有一行。我通常会使用聚合来解决这个问题:

select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] in ('SHIPPED', 'UNSHIPPED')
group by [Order number]
having min([SHIPPED/UNSHIPPED]) <> max([SHIPPED/UNSHIPPED]);

Another clever method uses intersect:

另一个聪明的方法使用intersect:

select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] = 'SHIPPED'
intersect
select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] = 'UNSHIPPED';

If you actually want the line numbers, use JOIN/IN/EXISTS with the above as a subquery. Another method uses window functions.

如果您确实需要行号,请将JOIN / IN / EXISTS与上面的子查询一起使用。另一种方法使用窗口函数。

#1