正在進行一項分析，我想獲得在我的專案表中購買的某種產品。假設我有這個資料：
| 產品編號 | 產品名稱 | 訂單編號 |
|---|---|---|
| 1 | T恤 | 1 |
| 4 | 口紅 | 1 |
| 4 | 口紅 | 2 |
| 6 | 牛仔褲 | 3 |
| 4 | 口紅 | 3 |
| 4 | 口紅 | 4 |
| 1 | T恤 | 5 |
| 6 | 牛仔褲 | 5 |
| 4 | 口紅 | 5 |
如何獲得只購買唇膏的完整訂單？
輸出應該是這樣的：
| 產品編號 | 產品名稱 | 訂單編號 |
|---|---|---|
| 4 | 口紅 | 2 |
| 4 | 口紅 | 4 |
uj5u.com熱心網友回復：
您可以檢查任何訂單專案是否不存在其他專案：
SELECT *
FROM t AS t1
WHERE ProductName = 'Lipstick'
AND NOT EXISTS (
SELECT *
FROM t AS t2
WHERE t2.OrderId = t1.OrderId
AND t2.ProductName <> 'Lipstick'
)
說明：外部查詢選擇所有口紅訂單項。這將回傳訂單 #1、2、3、4 和 5。讓我們將這些行稱為口紅訂單項。
然后，子查詢針對口紅訂單項中的每一行，選擇不包含口紅 (t2.ProductName <> 'Lipstick') 的相關訂單項 (t2.OrderId = t1.OrderId)。如果不存在這樣的行，則唇膏訂單項是唯一的。
uj5u.com熱心網友回復：
獲取僅包含口紅的訂單串列的一種方法：
SELECT OrderId
FROM Item
GROUP
BY OrderId
HAVING Min(ProductName) = 'Lipstick'
AND Max(ProductName) = 'Lipstick'
;
uj5u.com熱心網友回復：
僅檢索前一行產品和當前產品相同的產品。這就是為什么使用 lag() 函式獲取前一行產品的原因。
SELECT t.ProductId, t.ProductName, t.OrderId
FROM (SELECT *
, LAG(ProductId) OVER (ORDER BY OrderId) prev_product
FROM item) t
WHERE t.ProductId = t.prev_product
AND t.ProductName = 'Lipstick'
另一種使用前一個和當前行順序的方法
SELECT t.ProductId, t.ProductName, t.OrderId
FROM (SELECT *
, LAG(OrderId) OVER (ORDER BY OrderId) prev_order
FROM item) t
WHERE t.OrderId != t.prev_order
AND t.ProductName = 'Lipstick'
請從 url https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=8cf359bb9bd73fd9fc9b4726117ff872檢查
根據資料檢索偶數orderid。
SELECT *
FROM item
WHERE ProductName = 'Lipstick'
AND OrderId % 2 = 0
注意：但我只推薦第一個選項。
uj5u.com熱心網友回復：
另一種選擇，使用條件聚合
SELECT i.OrderId
FROM Item i
GROUP BY
i.OrderId
HAVING COUNT(CASE WHEN ProductName = 'Lipstick' THEN 1 END) > 1
AND COUNT(CASE WHEN ProductName <> 'Lipstick' THEN 1 END) = 0;
uj5u.com熱心網友回復：
您可以以這樣一種方式構建查詢，它可以顯示包含單個商品的所有訂單，然后您可以決定檢索哪個商品，假設該商品并不總是“口紅”。此外，現實生活中的資料庫通常很復雜，您可能有一個包含多行單個專案的 OrderId。在這種情況下，您將需要一個不同的計數。這可以通過多種方式完成，但將顯示兩個選項。
準備資料庫
CREATE TABLE tablename (ProductId INTEGER,
ProductName VARCHAR(100),
OrderId INTEGER);
INSERT INTO tablename VALUES
(1, 'Tshirt', 1),
(4, 'Lipstick', 1),
(4, 'Lipstick', 2),
(6, 'Jeans', 3),
(4, 'Lipstick', 3),
(4, 'Lipstick', 4),
(1, 'Tshirt', 5),
(6, 'Jeans', 5),
(4, 'Lipstick', 5);
選項 1：使用交叉應用
WITH Product_count AS (
SELECT t1.*, t2.cnt
FROM tablename t1 CROSS APPLY
(SELECT COUNT(DISTINCT t2.ProductName) AS cnt
FROM tablename t2
WHERE t2.OrderId = t1.OrderId
) t2
)
SELECT ProductId, ProductName, OrderId
FROM Product_count
WHERE cnt = 1 AND ProductName = 'Lipstick';
選項 2：使用視窗函式
WITH Product_count AS (
SELECT *,
DENSE_RANK() OVER (PARTITION BY OrderId ORDER BY ProductName)
DENSE_RANK() OVER (PARTITION BY OrderId ORDER BY ProductName DESC) - 1 AS cnt
FROM tablename
)
SELECT ProductId, ProductName, OrderId
FROM Product_count
WHERE cnt = 1 AND ProductName = 'Lipstick';
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363706.html
標籤：sql sql-server 查询语句 sql-server-2012