物品表：
| ID | 專案名稱 | 代碼 | cust_id |
|---|---|---|---|
| 1 | 芒果 | 111 | u1 |
| 2 | 牛奶 | 112 | u2 |
| 3 | 巧克力 | 113 | u3 |
| 4 | 牛奶 | 112 | u1 |
| 5 | 芒果 | 111 | u2 |
| 6 | 漿果 | 114 | u3 |
| 7 | 巧克力 | 113 | u1 |
| 8 | 漿果 | 114 | u2 |
| 9 | 冰淇淋 | 114 | u3 |
| 10 | 芒果 | 111 | u4 |
這些是客戶表，我必須在 SQL Server 中撰寫一個存盤程序來查找購買所有商品的 cust_id，這些商品以逗號分隔值傳遞，例如“芒果、牛奶、巧克力”
CREATE PROCEDURE Items_find
@items_value nvarchar(max)
AS
BEGIN
END
EXEC Items_find 'Mango,Milk,Chocolate'
EXEC Items_find 'Mango,Milk'
cust_id = u1如果'Mango,Milk' then 輸出應該是cust_id=u1, u2，或者如果'Mango'then 輸出是，則存盤程序應該回傳cust_id=u1, u2, u3
我試圖過濾掉cust_id以逗號分隔的物品數量的wrt，但之后我找不到cust_id專門購買這些物品的人
uj5u.com熱心網友回復：
一種解決方案是首先計算搜索中有多少單詞，
然后選擇包含搜索的所有行并使用計數僅保留具有相同組的那些計數
這是一個例子
declare @Items table (id int, item_name varchar(50), code int, cust_id varchar(10))
insert into @Items values (1, 'Mango', 111, 'u1'),
(2, 'Milk', 112, 'u2'), (3, 'Chocolate', 113, 'u3'), (4, 'Milk', 112, 'u1'), (5, 'Mango', 111, 'u2'),
(6, 'Berry', 114, 'u3'), (7, 'Chocolate', 113, 'u1'), (8, 'Berry', 114, 'u2'), (9, 'Ice-cream', 114, 'u3'),
(10, 'Mango', 111, 'u4')
declare @search varchar(50) = 'Mango,Milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', '')) 1)
select cust_id
from @Items
where @search like '%' item_name '%'
group by cust_id
having count(1) = @cnt
這將回傳
cust_id
u1
u2
如果你想要它在一個字串中，你可以使用 string_agg
declare @search varchar(50) = 'Mango,milk'
declare @cnt int = (select len(@search) - len(replace(@search, ',', '')) 1)
select 'cust_id = '
( select string_agg(t.cust_id, ', ')
from ( select top 100000
i.cust_id
from @Items i
where @search like '%' i.item_name '%'
group by i.cust_id
having count(1) = @cnt
) t
) as Result
這將回傳
cust_id = u1, u2
uj5u.com熱心網友回復：
DECLARE @InProducts VARCHAR(100);
--SET @InProducts='Mango,Milk,Chocolate';
SET @InProducts='Mango,Milk';
WITH CTE(id, item_name, code, cust_id) AS
(
SELECT 1,'MANGO',111,'U1'
UNION ALL
SELECT 2,'MILK',112,'U2'
UNION ALL
SELECT 3,'CHOCOLATE',113,'U3'
UNION ALL
SELECT 4,'MILK',112,'U1'
UNION ALL
SELECT 5,'MANGO',111,'U2'
UNION ALL
SELECT 6,'BERRY',114,'U3'
UNION ALL
SELECT 7,'CHOCOLATE',113,'U1'
UNION ALL
SELECT 8,'BERRY',114,'U2'
UNION ALL
SELECT 9,'ICE-CREAM',115,'U3'
UNION ALL
SELECT 10,'MANGO',111,'U4'
)
SELECT Z.CUST_ID FROM
(
SELECT C.*FROM CTE AS C
JOIN string_split(@InProducts,',')X ON C.item_name=X.value
)Z
GROUP BY Z.cust_id HAVING COUNT(Z.CUST_ID)=
(
SELECT COUNT(X.VALUE) FROM string_split(@InProducts,',')X
)
希望，你可以使用這樣的東西
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/422009.html
標籤：
上一篇：分隔表格，每個名字跟在表格后面
下一篇：無法創建資料庫表：語法錯誤