SQL Server Remove duplicate value available in all order

mfpqipee 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(127)

I have a table with values as shown below
| Sno | orderno | partno |
| ------------ | ------------ | ------------ |
| 101 | OCT1 | oRing |
| 101 | OCT1 | Gasket |
| 101 | OCT1 | Case |
| 101 | OCT2 | gRing |
| 101 | OCT2 | Gasket |
| 101 | OCT2 | Cover |
| 101 | OCT3 | Case |
| 101 | OCT3 | Gasket |
| 102 | OCT1 | Gasket |

I want to remove the values that are common in all orders (OCT1,OCT2,OCT3) per Sno. Output should also include orderno as well something like below . Here Gasket is available in all order for 101, so i want to remove it but Case is used in only 2 order out of 3, so i cannot remove it.

Sno	orderno	partno
101	OCT1	oRing
101	OCT1	Case
101	OCT2	gRing
101	OCT2	Cover
101	OCT3	Case
102	OCT1	Gasket

I have tried below code but not working. Please help/suggest

select * from mytable where partno not in (
select max(partno) from mytable
group by sno
having count(orderno)=count(partno))

sql-server

来源：https://stackoverflow.com/questions/77286806/remove-duplicate-value-available-in-all-order

1条答案

按热度按时间

4jb9z9bj1#

We can try the following approach. The first CTE finds the distinct number of orders within each Sno . We then also find, in the second CTE, the number of times a product appears within a given Sno . A matching record is one for which the product did not appear in every order.

WITH cte1 AS (
    SELECT Sno, COUNT(DISTINCT orderno) AS ordernocnt
    FROM mytable
    GROUP BY Sno
),
cte2 AS (
    SELECT t1.*, t2.ordernocnt,
           COUNT(*) OVER (PARTITION BY Sno, partno) partcnt
    FROM mytable t1
    INNER JOIN cte1 t2 ON t2.Sno = t1.Sno
)

SELECT Sno, orderno, partno
FROM cte2
WHERE partcnt < ordernocnt OR ordernocnt = 1
ORDER BY Sno, orderno;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Remove duplicate value available in all order

1条答案

相关问题

热门标签

最新问答