SQL将处理后的ID添加到用逗号分隔的单个单元格中

Question

我有以下SQL查询来了解它的功能，请阅读下面的描述

select catalogid, numitems, allitems - numitems ignoreditems
    from (
      select i.catalogid,
        sum(case when (ocardtype in ('PayPal','Sofort') OR
                       ocardtype in ('mastercard','visa') and
                       odate is not null) AND NOT EXISTS (
                         select * from booked b
                         where b.ignoredoid = o.orderid
                       ) then numitems
                       else 0 end) numitems,
        sum(numitems) allitems
      from orders o
      join oitems i on i.orderid=o.orderid
      group by i.catalogid
    ) X

和以下sql表

项目表

+---------+-----------+----------+
| orderid | catalogid | numitems |
+---------+-----------+----------+
| O737    |       353 |        1 |
| O738    |       364 |        4 |
| O739    |       353 |        3 |
| O740    |       364 |        6 |
| O741    |       882 |        2 |
| O742    |       224 |        5 |
| O743    |       224 |        2 |
+---------+-----------+----------+

订单表

 +-----------------+------------+------------+
    |         orderid | ocardtype  |   odate    |
    +-----------------+------------+------------+
    |     O737        | Paypal     |            | 'OK
    |     O738        | MasterCard | 01.02.2012 | 'OK
    |     O739        | MasterCard | 02.02.2012 | 'OK
    |     O740        | Visa       | 03.02.2012 | 'OK
    |     O741        | Sofort     |            | 'OK
    |     O742        |            |            | 'ignore because ocardtype is empty
    |     O743        | MasterCard |            | 'ignore because Mastercard no odate
    +-----------------+------------+------------+

重用数据表

+-----------+----------+--------------+
| catalogid | numitems | ignoreditems |
+-----------+----------+--------------+
|       353 |        4 |            0 |
|       364 |       10 |            0 |
|       882 |        2 |            0 |
|       224 |        0 |            7 |
+-----------+----------+--------------+

想法是在以下条件下，对在oitems表中的数据上具有相同catalogid的产品的numitems列求和

1. 如果ocardtype为空，则忽略numitems并将其视为0 总和并将被忽略的项目求和到ignoreditems列中
2. 如果ocardtype某些订单是MasterCard或Visa，并且odate为空，则忽略numitems并将其视为0并将被忽略的项目求和到ignoreditems列中
3. 如果ocardtype是Paypal或Sofort，则只需做numitems总和而不检查，odate因为这些类型不需要odate
4. 在另一个称为booked的表中，我有一列称为ignoreoid的列orderids，即使满足上述3个条件，我也想忽略上表中的这些列

至此，由于@Richard aka
cyberkiwi对这个问题的回答，查询工作正常

问题是，我需要结果数据表如下所示

+-----------+----------+--------------+-------------------+
| catalogid | numitems | ignoreditems | orderidcollection |
+-----------+----------+--------------+-------------------+
|       353 |        4 |            0 | O737,O739         |
|       364 |       10 |            0 | O738,O740         |
|       882 |        2 |            0 | O741              |
|       224 |        0 |            7 |                   |'O742 & O743 are ignored      
+-----------+----------+--------------+-------------------+

如您所见，唯一的变化是添加了orderidcollection列，orderid只有在代码中未忽略该顺序的情况下，它才必须将其添加到由逗号分隔的新列中，我已经在谷歌上搜索了好几个小时而没有运气！SQL甚至有可能吗？

Answer 1

我对上一个问题的Linq-to-Sql回答扩展到了这个问题（LINQpad查询）：

Dim odateRequired = {"MasterCard", "Visa"}
Dim odateNotRequired = {"Paypal", "Sofort"}

Dim result = From o In Orders Join i In Oitems On o.orderid Equals i.orderid _
             Let check = Not (From b In Bookeds Where b.ignoredoid=i.orderid).Any _
                     AndAlso o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitems = Sum(If(check, i.numitems, 0)), _
         ignoreditems = Sum(If(check, 0, i.numitems)), _
         group
         Select catalogid, numitems, ignoreditems, _
             orderidcollection = String.Join(",", (From g in group Where g.check Select g.i.orderid))
result.Dump

请注意，此解决的问题多SQL查询，以确定orderids每个catalogid地方check是True累积的每个orderidcollection。可能有一个更有效的解决方案（或者使用一些可能是复杂的Linq-
to-Sql来使生成的SQL等效于String.Join，或者catalogid, numitems, check, orderid使用Linq-
to-Sql进行获取，然后使用Linq-to-objects做最后的积累）。

还要注意，此查询包括您的Booked表（使用LINQpad的复数形式）。