SQL选择案例

Question

我有以下sql表

oitems table

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

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

名为result的reusltant数据表

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

思想是将具有相同catalogid的产品的numitems列之和到oitems表中的数据，具有以下条件

1. 如果ocardtype为空，则忽略numitems，并在和中将其视为0，并将忽略的项和为ignoreditems列。
2. 如果某个顺序的ocardtype为MasterCard或Visa，且odate为空，则忽略numitems并将其视为0，并将被忽略的项与ignoreditems列相加
3. 如果ocardtype为Paypal或Sofort，则只需执行numitems和而不检查日期，因为这些类型不需要odate。

基本上，我希望将结果数据表保存到临时数据表中，并将其加载到vb.net数据表中。

我很难弄清楚如何在sql查询中完成这个任务！我需要这样做，因为sql命令对于vb.net来说是可以编程的，使用循环使用vb.net数据，使用linq进行大量的检查是一种选择，但是我只需要从服务器上获得它。

Answer 1

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

Answer 2

类似于：

SELECT
     oi.catalog_id,
     SUM(CASE
            WHEN ocardtype in ('Paypal','Sofort') THEN numitems
            WHEN ocardtype in ('Mastercard','Visa') and odate is not null THEN numitems
            ELSE 0 END) as numitems,
     SUM(CASE
            WHEN ocardtype is null then numitems
            WHEN ocardtype in ('Mastercard','Visa') and odate is null THEN numitems
            ELSE 0 END) as ignoreditems
FROM
   oitems oi
      inner join
   Orders o
      on
         oi.orderid = o.orderid
GROUP BY
   oi.catalog_id

(假设您在叙述中使用了“空”一词，您的意思是列是NULL)

Answer 3

下面是原始请求的LINQpad版本(作为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 = o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) _
                               AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitem = Sum(If(check, i.numitems, 0)), _
         ignored = Sum(If(check, 0, i.numitems))

result.Dump

在对RichardTheKiwi的答复的评论中，您提出了额外的请求(它只包括Not (From b In Bookeds Where b.ignoredoid=i.orderid).Any AndAlso在check前面)：

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 _
         numitem = Sum(If(check, i.numitems, 0)), _
         ignored = Sum(If(check, 0, i.numitems))

result.Dump