SQL 将处理后的 id 添加到以逗号分隔的单个单元格

2022-01-16 00:00:00 sql conditional sql-server sql-server-2008-r2 datatable

我有以下 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表

项目表

+---------+-----------+----------+
| 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 数据表

the reusltant datatable

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

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

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在 sum 和 sum 忽略的项目到 ignoreditems 列
  2. 如果某个订单的 ocardtype 是 MasterCard 或 Visa，并且 odate 是空然后忽略 numitems 并将其视为 0 并将ignoreditems 列中被忽略的项目
  3. 如果 ocardtype 是 Paypal 或 Sofort，则只需执行 numitems 和不检查 odate 因为这些类型不需要 odate
  4. 在另一个名为booked 的表中，我有一个名为ignoreoid 的列，此列包含上表中我想要的 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

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

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，其中 check 为 True累积每个 orderidcollection.可能有更有效的解决方案(或者使用一些可能很复杂的 Linq-to-Sql 导致生成的 SQL 执行相当于 String.Join 的操作，或者获取 catalogid、numitems,check,orderid使用Linq-to-Sql然后使用Linq-to-objects做最后的累加).

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).

还请注意，此查询包括您的 Booked 表(带有 LINQpad 的复数形式).

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

相关文章