SQL 选择案例 [英] SQL Select case

问题描述

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

the reusltant datatable called result

 +-----------+----------+--------------+
| 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 并将忽略的项目与 ignoreditems 相加专栏
2. 如果某些订单的 ocardtype 是 MasterCard 或 Visa 并且odate 为空然后忽略 numitems 并将其视为0 并将忽略的项目与 ignoreditems 列相加
3. 如果 ocardtype 是 Paypal 或 Sofort，则只需执行 numitems和不检查日期，因为这些类型不需要 odate

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 date because those types require no odate

基本上我想将结果数据表保存到临时数据表并将其加载到 vb.net 数据表

basicly i want to save the result datatable to a temporary datatable and load it to a vb.net datatable

我很难弄清楚如何在 sql 查询中执行此操作！我需要这个作为 vb.net 的 sql 命令，能够使用循环和大量检查以编程方式使用 vb.net 数据表来完成使用 linq 是一种选择，但我只需要从服务器获取这个

i am having a hard time figuring out how to do this in an sql query! i need this as sql command for vb.net , was able to do it programmatically using vb.net datatables using loops and alot of checking using linq is an option, but i just need to get this from the server

推荐答案

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 选择案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！