Server :在分组语句中连接主键ID

Question

我正在尝试选择一组成组的行，并将这些行的主键值连接到select语句中，并对行进行计数并选择该值。

表：

• JobTable - JobID，ExpressJob，ItemID
• ItemTable - ItemID，颜色，尺寸

Jobs中的值

10001, true, 3
10002, true, 3
10003, false, 4

Items中的值

3, Blue, 1-2
4, Pink, 5-6

结果集：

3,Blue,1-2,10001|10002
3,Pink,5-6,10003

我在select语句中研究了以下内容：

SELECT 
    i.ItemID, i.Colour, i.Size, 
    COUNT(i.ItemID) AS Quantity,      
    j.ExpressJob, 
    JobIDArray = STUFF((SELECT CONVERT(VARCHAR(10), jb.JOBID) 
                        FROM Jobs jb 
                        WHERE jb.JobID = j.JobID 
                        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''))
FROM 
    Jobs j
INNER JOIN 
    Items i ON i.ItemID = j.ItemID
GROUP BY 
    i.ItemID, i.Colour, i.Size, j.ExpressJob

但是我一直在JobID上得到一个聚合组错误。从我在网上研究的情况来看，XML是可行的，但由于某些原因，在选择ID列时并不有效。

Answer 1

只要稍微调整一下你已经拥有的东西，你就能做到这一点。试试看：

DECLARE @Jobs TABLE
    (
        [JobID] INT
      , [ExpressJob] NVARCHAR(100)
      , [ItemID] INT
    );

DECLARE @Items TABLE
    (
        [ItemID] INT
      , [Colour] NVARCHAR(100)
      , [Size] NVARCHAR(100)
    );

INSERT INTO @Jobs (
                      [JobID]
                    , [ExpressJob]
                    , [ItemID]
                  )
VALUES ( 10001, 'true', 3 )
     , ( 10002, 'true', 3 )
     , ( 10003, 'false', 4 );

INSERT INTO @Items (
                       [ItemID]
                     , [Colour]
                     , [Size]
                   )
VALUES ( 3, 'Blue', '1-2' )
     , ( 4, 'Pink', '5-6' );

SELECT     [i].[ItemID]
         , [i].[Colour]
         , [i].[Size]
         , [j].[ExpressJob]
         , COUNT([i].[ItemID]) AS [Quantity]
         --Added '|' as that was how you wanted the results delimited
         , STUFF((
                     SELECT '|' + CONVERT(VARCHAR(10), [jb].[JobID])
                     FROM   @Jobs [jb]
                     WHERE  [jb].[ItemID] = [i].[ItemID] --Change here as you're looking for JobID associated to the Item.
                     FOR XML PATH('') --No need to set TYPE or use '.value'
                 )
               , 1
               , 1
               , ''
                ) AS JobIDArray 
FROM       @Jobs [j]
INNER JOIN @Items [i]
    ON [i].[ItemID] = [j].[ItemID]
GROUP BY   [i].[ItemID]
         , [i].[Colour]
         , [i].[Size]
         , [j].[ExpressJob];