代码之家 › 专栏 › 技术社区 › Omran Moh'd

获取与某些类别一起购买的项目,并排除不需要的类别

sql-server-2012 sql-server sql

Omran Moh'd · 技术社区 · 7 年前

我有两张桌子,一张是主菜单桌,另一张是点菜桌。我想从主菜单表中获取基于我设置的类别购买的所有订单,并排除使用某些类别购买的订单。例如:

ItemCode       Category
-------------------------
10001         VM1
10002         VM1
10003         VM2
10004         VM3
10005         VM3
10006         HOT DRINKS
10007         HOT DRINKS
10008         COLD DRINKS
10009         COLD DRINKS
10066         DESSERT

订单表为:

SiteID   BusinessDate   ItemName   Units Sold   Units Sale   ItemCode  OrderNo
----------------------------------------------------------------------------
1        06/08/2018     Apple         1             5        10001    122-1
1        06/08/2018     Coffee        1             16       10006    122-1
1        06/08/2018     Ice Tea       2             7        10008    122-1
1        06/08/2018     Beans         9             18       10004    122-1

4        06/08/2018     Donuts        7             17       10066    122-7

1        06/08/2018     Bread         1             7        10003    122-4
1        06/08/2018     Beans         4             8        10004    122-4

2        06/08/2018     OrangeJuice   2             5        10009    122-2
2        06/08/2018     Coffee        1             6        10006    122-2

3        06/08/2018     Bread         3             5        10003    122-3
3        06/08/2018     Beans         7             17       10004    122-3
3        06/08/2018     Coffee        17            17       10006    122-3

3        06/08/2018     Ice Tea       7             17       10008    122-5

4        06/08/2018     OrangeJuice   7             17       10009    122-6

结果:

SiteID   BusinessDate   ItemName   Units Sold   Units Sale   ItemCode  OrderNo
----------------------------------------------------------------------------
4        06/08/2018     Donuts        7             17       10066    122-7

2        06/08/2018     OrangeJuice   2             5        10009    122-2
2        06/08/2018     Coffee        1             6        10006    122-2

3        06/08/2018     Ice Tea       7             17       10008    122-5

4        06/08/2018     OrangeJuice   7             17       10009    122-6

预期结果 :

我想得到所有订单的细节,是购买的类别设置在(热饮,冷饮,甜点),并排除那些OrderNo的类别设置在(VM1,VM2,VM3)。请记住,如果一个order同时包含这两个部分,则整个OrderNo不应出现在我的输出查询中。例如:我不应该获取OrderNo:122-3,因为它包含一个ItemCodes,它存在于我的主项目表类别(VM2、VM3、热饮料)中。

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

如果我理解正确, group by having 解决此问题:

select o.orderno
from orders o join
     mastermenu mm
     on o.itemcode = mm.itemcode
group by o.orderno
having sum(case when mm.category in ('HOT DRINKS', 'COLD DRINKS', 'DESSERT') then 1 else 0 end) > 0 and
       sum(case when mm.category in ('VM1', 'VM2', 'VM3') then 1 else 0 end) = 0;

这个 sum(case . . . ) 计算这些类别的匹配项目数。这个 > 0 说至少有一个符合顺序。这个 = 0 说没有一个符合顺序。

这就是你想要的吗?

with om as (
      select o.*, mm.category
      from orders o join
           mastermenu mm
           on o.itemcode = mm.itemcode
     )
select om.*
from om
where om.category in ('HOT DRINKS', 'COLD DRINKS', 'DESSERT') and
      not exists (select 1 
                  from om om2
                  where om2.orderno = om.orderno and 
                        om2.category in ('VM1', 'VM2', 'VM3')
                 );

Sahi 7 年前

试试这个:

     Create Table #MasterMenu(itemCode Bigint,Category Varchar(50))

     Insert into #MasterMenu
    SElect 10001,'VM1' Union All
    SElect 10002,'VM1' Union All
    SElect 10003,'VM2' Union All
    SElect 10004,'VM3' Union All
    SElect 10005,'VM3' Union All
    SElect 10006,'HOT DRINKS' Union All
    SElect 10007,'HOT DRINKS' Union All
    SElect 10008,'COLD DRINKS' Union All
    SElect 10009,'COLD DRINKS' Union All
    SElect 10066,'DESSERT'


    Create Table #Order(SiteId int, BusinessDate Date,ItemName Varchar(50), UnitsSold int,UnitsSale int,ItemCode Bigint , OrderNo Varchar(50))

    Insert Into #Order
    SELECT 1,'06/08/2018','Apple  ',1 ,5 ,10001,'122-1' Union All
    SELECT 1,'06/08/2018','Coffee ',1 ,16,10006,'122-1' Union All
    SELECT 1,'06/08/2018','Ice Tea',2 ,7 ,10008,'122-1' Union All
    SELECT 1,'06/08/2018','Beans  ',9 ,18,10004,'122-1' Union All
    SELECT 4,'06/08/2018','Donuts ',7 ,17,10066,'122-7' Union All
    SELECT 1,'06/08/2018','Bread  ',1 ,7 ,10003,'122-4' Union All
    SELECT 1,'06/08/2018','Beans  ',4 ,8 ,10004,'122-4' Union All
    SELECT 2,'06/08/2018','OrangeJuice',2 ,5 ,10009,'122-2' Union All
    SELECT 2,'06/08/2018','Coffee ',1 ,6 ,10006,'122-2' Union All
    SELECT 3,'06/08/2018','Bread  ',3 ,5 ,10003,'122-3' Union All
    SELECT 3,'06/08/2018','Beans  ',7 ,17,10004,'122-3' Union All
    SELECT 3,'06/08/2018','Coffee ',17,17,10006,'122-3' Union All
    SELECT 3,'06/08/2018','Ice Tea',7 ,17,10008,'122-5' Union All
    SELECT 4,'06/08/2018','OrangeJuice',7 ,17,10009,'122-6' 

    ;with cte
    As
    (
        Select OrderNo,SUM(CASE WHEN ItemCode in (10001,10002,10003) then 1 ELSE 0 END) AS ItemCount
        from #Order
        Group by OrderNo
    )

    Select o.* from cte c
    INNER JOIN #Order o on c.OrderNo=o.OrderNo
    Where c.ItemCount=0


    Drop Table #MasterMenu
    Drop Table #Order

输出:

SiteId  BusinessDate    ItemName    UnitsSold   UnitsSale   ItemCode    OrderNo
  2      2018-06-08     OrangeJuice    2            5         10009      122-2
  2      2018-06-08     Coffee         1            6         10006      122-2
  3      2018-06-08     Ice Tea        7            17        10008      122-5
  4      2018-06-08     OrangeJuice    7            17        10009      122-6
  4      2018-06-08     Donuts         7            17        10066      122-7