逗号在SQL
问题描述:
分隔值在SELECT子句中我有两个表:CONFIRMATION & CONFIRMATION_PRESS逗号在SQL
构象表看起来如下:
ID_CONF | ID_LOT | QTY
1005 175 25
1006 175 24
1007 175 23
1008 176 50
CONFIRMATION_PRESS表看起来如下:
ID_CONF | ID_PRESS
1005 11
1005 22
1005 33
1006 12
1006 13
1007 14
现在,我要为ID的所有数据_LOT = 175从CONFIRMATION表中,但我需要ID_PRESS逗号分隔值。
例如,对于ID_LOT 175,应该会显示以下结果:
ID CONF | ID_LOT | QTY | ID_PRESS
--------+--------+-----+----------
1005 175 25 11,22,33
1006 175 24 12,13
1007 175 23 14
我的查询看起来是这样的:
SELECT
C.ID_CONF,
C.QTY,
C.ID_LOT,
STUFF((
SELECT CAST(',' AS VARCHAR(MAX)) + CAST(CP.ID_PRESS AS VARCHAR(5))
FROM CONFIRMATION C,
CONFIRMATION_PRESS CP
WHERE
C.ID_CONF = CP.ID_CONF
FOR XML PATH('')), 1, 1, '') PRESS_CSV
FROM
CONFIRMATION C
WHERE
C.ID_LOT = 175
但它返回的输出:
ID CONF | ID_LOT | QTY | ID_PRESS
--------+--------+-----+------------------
1005 175 25 11,22,33,12,13
1006 175 24 11,22,33,12,13
1007 175 23 11,22,33,12,13
我在这里做错了什么?
请帮助!
商祺!
答
不要再参加确认表上的子查询。您需要与您的主要查询相关的行。
SELECT
C.ID_CONF,
C.QTY,
C.ID_LOT,
STUFF((
SELECT CAST(',' AS VARCHAR(MAX)) + CAST(CP.ID_PRESS AS VARCHAR(5))
FROM CONFIRMATION_PRESS CP
WHERE CP.ID_CONF = C.ID_CONF
FOR XML PATH('')), 1, 1, '') PRESS_CSV
FROM CONFIRMATION C
WHERE C.ID_LOT = 175
+0
像魅力一样工作。傻我!万分感谢 :) –
答
Begin Tran
Create Table CONFIRMATION (ID_CONF Int,ID_LOT Int,QTY numeric)
INSert into CONFIRMATION
Select 1005,175,25 Union All
Select 1006,175,24 Union All
Select 1007,175,23 Union All
Select 1008,176,50
Create Table CONFIRMATION_PRESS (ID_CONF int ,ID_PRESS INt)
Insert into CONFIRMATION_PRESS
Select 1005,11 Union All
Select 1005,22 Union All
Select 1005,33 Union All
Select 1006,12 Union All
Select 1006,13 Union All
Select 1007,14
--Select *from CONFIRMATION Inner Join
--CONFIRMATION_PRESS On CONFIRMATION_PRESS.ID_CONF=CONFIRMATION.ID_CONF
--Where CONFIRMATION.ID_LOT=175
SELECT
C.ID_CONF,
C.QTY,
C.ID_LOT,
STUFF((
SELECT ', ' + CONVERT(Nvarchar,CP.ID_PRESS)
from
CONFIRMATION_PRESS CP
WHERE
C.ID_CONF = CP.ID_CONF
FOR XML PATH('')), 1, 2, '') PRESS_CSV
FROM
CONFIRMATION C
WHERE
C.ID_LOT = 175
RollBack Tran
[不良习惯踢:用旧式的JOIN(http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick- using-old-style-joins.aspx) - 在ANSI-** 92 ** SQL标准中,旧式*逗号分隔的表*样式列表已替换为* proper * ANSI'JOIN'语法超过20年**之前),其使用是不鼓励的 –
我只写了这个查询。我通常在我的查询中使用INNER JOIN/OUTER JOIN。感谢您的反馈! –