sql - 带有 2 个内部联接的 SQL Server 2014 STUFF
问题描述
我的问题非常类似于Use STUFF with INNER JOIN Query,但我一直在尝试如果第一列是一种varchar
类型,目前这是我的表。至于注意我使用的是 SQL Server 2014
表客户
Cust_ID | Cust_Name
--------+---------
1 | Name1
2 | Name2
表顺序
Order_ID | Order_NO | Cust_ID
---------+----------+---------
1 | No.001 | 1
2 | No.002 | 2
表项
Item_ID | Order_ID | Item_Name | Quantity | Price
--------+----------+-----------+----------+-------
1 | 1 | A | 1 | 10
2 | 1 | B | 1 | 20
3 | 1 | C | 1 | 30
4 | 2 | D | 1 | 40
我尝试过的一些查询:
SELECT
TBL_Sales_SO.SO_NO AS int, Tbl_Customer.Customer_Name,
ITEMS = STUFF ((SELECT DISTINCT ',' + TBL_SO_LIITEM.Item_Name
FROM Tbl_Customer
INNER JOIN TBL_Sales_SO ON Tbl_Customer.Com_Customer_ID = TBL_Sales_SO.Com_Customer_ID
INNER JOIN TBL_SO_LIITEM ON TBL_Sales_SO.Sales_SO_ID = TBL_SO_LIITEM.Sales_So_ID
WHERE TBL_Sales_SO.SO_NO = TBL_Sales_SO.Sales_SO_ID
FOR XML PATH('')), 1, 1,'')
FROM
TBL_Sales_SO, Tbl_Customer
ORDER BY
SO_NO
以及对我来说我认为几乎命中的查询
SELECT
TBL_Sales_SO.SO_NO, Tbl_Customer.Customer_Name, TBL_SO_LIITEM.Item_Name,
TBL_SO_LIITEM.Quantity, TBL_SO_LIITEM.Price
FROM
((Tbl_Customer
INNER JOIN
TBL_Sales_SO on Tbl_Customer.Com_Customer_ID = TBL_Sales_SO.Com_Customer_ID)
INNER JOIN
TBL_SO_LIITEM on TBL_Sales_SO.Com_Customer_ID = TBL_SO_LIITEM.Sales_So_ID)
在修改第一个代码时给了我一些不同的错误,第二个我只是不确定如何使像我的目标这样的东西成为可能
Order_No | Name | Item_Name | Quantity | Price
no.001 | Name1 | A,B,C | 3 | 60
no.002 | Name2 | D | 1 | 40
更新 在戈登回答之后,我再次修改代码,管理到下表
Order_No| Name | Item_Name
no.001 | Name1 | A,B,C
no.001 | Name2 | D
no.002 | Name2 | D
no.002 | Name1 | A,B,C
和我使用的查询:
SELECT
s.SO_NO AS int, c.Cust_Name,
ITEMS = STUFF((SELECT DISTINCT ','+ Item_Name
FROM TBL_SO_LIITEM item
INNER JOIN TBL_Sales_SO s ON s.Sales_SO_ID = item.Sales_So_ID
WHERE c.Cust_ID = s.Cust_ID
FOR XML PATH('')), 1, 1, '')
FROM
TBL_Sales_SO s, Tbl_Customer c
WHERE
c.Cust_Name IN ('Name1','Name2')
ORDER BY
SO_NO
解决方案
你似乎想要:
SELECT s.SO_NO AS int, c.Customer_Name,
STUFF( (SELECT DISTINCT ',' + oi.Item_Name
FROM TBL_SO_LIITEM oi
WHERE s.Sales_SO_ID = oi.Sales_So_ID
FOR XML PATH('')), 1, 1,''
) as items
FROM TBL_Sales_SO s JOIN
Tbl_Customer c
ON s.cust_id = c.cust_id
ORDER BY SO_NO;
请注意,这些表只包含一次,无论是在子查询中还是在外部查询中。您的数据的命名约定非常不一致,所以以上是我最好的猜测。