我有 2 列，它們是 MOrderNum 和 SubOrderNum。MOrderNum 是為審批Sub Order No 后創建的Master Order No。如何只顯示有多個SubOrderNum 的MSubNum？
例子
| 訂單號 | 子訂單號 |
|---|---|
| A123 | A123-01 |
| A123 | A123-02 |
| M111 | B001 |
| M222 | C001 |
應該顯示的資料是
| 訂單號 | 子訂單號 |
|---|---|
| A123 | A123-01 |
| A123 | A123-02 |
uj5u.com熱心網友回復：
--it will check if MOrderNum exists in the counting list
SELECT MOrderNum, SubOrderNum
from tbl
WHERE MOrderNum in(
--It will take all the MOrderNum with their count more than one
SELECT MOrderNum FROM tbl
GROUP BY MOrderNum
HAVING COUNT(MOrderNum)>1
)
Order by MOrderNum
小提琴演示
uj5u.com熱心網友回復：
您可以使用視窗函式更有效地執行此操作
SELECT MOrderNum, SubOrderNum
FROM (
SELECT *,
cnt = COUNT(*) OVER (PARTITION BY MOrderNum)
FROM tbl
) tbl
WHERE cnt > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/341122.html
標籤：sql sql-server 存储过程
下一篇：選擇帶有SQLXML空元素的查詢