SQL将处理的ID添加到单个单元格中，并以逗号分隔 [英] SQL add processed ids to a single cell seperated with a comma

问题描述

我有以下sql查询来了解它的作用请阅读下面的描述

i have the following sql query to get an idea of what it does please read the description below

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表

oitems表

+---------+-----------+----------+
| 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
    +-----------------+------------+------------+

reusltant datatable

the reusltant datatable

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

想法是对具有以下条件的oitems表中的数据具有相同目录代码的产品的numitems列进行求和： p>

idea is to sum the numitems column for products that have the same catalogid depinding on the data in the oitems table with the following conditions

1. 如果 ocardtype 为空，则忽略 numitems 并将其视为 0
   ，并将忽略的项目与 ignoreditems 列


2. 如果某些订单的 ocardtype 是万事达卡或Visa，而 odate 是
   空，然后忽略 numitems ，并将其视为 0 并将
   忽略项到 ignoreditems 列


3. 如果ocardtype是PayPal或Sofort，那么只需执行 numitems sum
   ，而不检查 odate ，因为这些类型不需要 odate


4. 在另一个名为booked的表中，我有一列名为ignoredoid，这个列包含
   orderids 从上表中我想要
   忽略，即使上述3个条件是饱和的

1. if ocardtype is empty then ignore the numitems and consider it as 0 in the sum and sum the ignored items to the ignoreditems column
2. if ocardtype for some order is MasterCard or Visa and the odate is empty then ignore the numitems and consider it as 0 and sum the ignored items to the ignoreditems column
3. if ocardtype is Paypal or Sofort, then just do the numitems sum without checking the odate because those types require no odate
4. in another table called booked i have a column called ignoredoid, this columns contains orderids from the above table that i want to ignore even if the 3 conditions above are satsfied

为此，查询工作正常，感谢@Richard aka cyberkiwi在他的答案在这个问题

To this point the query is working perfectly thanks to @Richard aka cyberkiwi to his answer in this question

问题是，我需要结果datatable看起来像以下

The Question is, i need the result datatable to look like the following

+-----------+----------+--------------+-------------------+
| 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？

as you can see the only change is the addition of orderidcollection column, it have to add the orderid to the new column seperated by a comma only if that order is not ignored in the code, i've been googling for couple hours with no luck! is this even possible with SQL?

推荐答案

我的Linq-to-Sql答案你以前的问题扩展到这个一个（LINQpad查询）：

My Linq-to-Sql answer to your previous question extended to this one (LINQpad query):

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查询，以确定每个 catalogid 的 orderids 其中检查是 True 以累积每个 orderidcollection 。可能有一个更有效的解决方案（使用一些可能令人回味的Linq-to-Sql，导致生成的SQL执行相当于 String.Join 的获取使用Linq-to-Sql然后使用Linq对象进行最后的累加）

Note this solution issues multiple SQL queries to determine the orderids for each catalogid where check is True to accumulate each orderidcollection. There may be a more efficient solution (either using some, probably convoluted, Linq-to-Sql that causes the generated SQL to do the equivalent of String.Join, or get the catalogid, numitems, check, orderid using Linq-to-Sql and then use Linq-to-objects to do the final accumulations).

请注意，此查询还包括您的已预订表（使用LINQpad的复数）。

Note also this query includes your Booked table (with LINQpad's pluralisation).

这篇关于SQL将处理的ID添加到单个单元格中，并以逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！