Oracle-根据条件计算行数

格雷格

我想创建一个“票”,计算每个ID的通过次数。当我们在任何ID上都有黄金通行证时,这意味着该通行证适用于所有预订的人。因此,对于此示例,我们要计数5。对于其他pass_code,我们只想计算通过次数,并排除那些为空的次数。我在下面有一个预期的输出。

说我有这个数据:

 Passes
    ID   | GuestID |  Pass_code
    ----------------------------
    100  |   001   | Bronze 
    100  |   002   | Bronze 
    101  |   103   | Gold
    101  |   104   | NULL
    101  |   105   | NULL
    101  |   106   | NULL
    101  |   107   | NULL
    102  |   208   | Silver
    103  |   209   | Steel
    103  |   210   | Steel
    103  |   211   | NULL
    
    Passengers
    ID   |  Passengers
    -----------------
    100  |  2
    101  |  5
    102  |  1
    103  |  3
    

我想算一下,然后在以下输出中创建票证:

    ID 100 | 2 pass (bronze)
    ID 101 | 5 pass (because it is gold, we count all passengers)
    ID 102 | 1 pass (silver)
    ID 103 | 2 pass (steel) (2 passes rather than than 3 as we just want to count only the passes for steel, bronze silver)

我想做这样的事情,但作为组合查询。

DECLARE @ID = 101; -- i will want to pass in IDs 

   -- for gold, we want to count all passengers when the gold pass is on 
   SELECT pp.Passengers
                 FROM passes
                 JOIN Passengers pp ON p.ID = pp.ID
                 WHERE p.pass_code IN'%gold%'
                 AND PP.id = @id

  -- for bronze, silver and steel
  SELECT 
       count(p.ID)
  FROM Passes
  WHERE p.ID = @id
  AND P.pass_code IN ('Bronze', 'silver', 'steel') -- Dont want to check based on NUlls as this may chnage to something else.

任何帮助或建议,将不胜感激。

普利基

这对您有用吗?

with Passes as (
select  100 as id, 001  as guestid, 'Bronze' as passcode from dual union all
select  100 as id, 002  as guestid, 'Bronze' as passcode from dual union all
select  101 as id, 103  as guestid,'Gold'   as passcode from dual union all
select  101 as id, 104  as guestid, NULL   as passcode from dual union all
select  101 as id, 105  as guestid, NULL   as passcode from dual union all
select  101 as id, 106  as guestid, NULL   as passcode from dual union all
select  101 as id, 107  as guestid, NULL   as passcode from dual union all
select  102 as id, 208  as guestid, 'Silver' as passcode from dual union all
select  103 as id, 209  as guestid, 'Steel'  as passcode from dual union all
select  103 as id, 210  as guestid, 'Steel'  as passcode from dual union all
select  103 as id, 211  as guestid, NULL   as passcode from dual
)
SELECT 
  id,passcode,count(ID)
  FROM Passes
  where passcode is not null and passcode<>'Gold'
  group by id,passcode
   union all
 SELECT 
     id,'Gold',count(ID)
  FROM Passes
  where id in 
   (
  select id from Passes where  passcode='Gold' 
  ) 
  group by id
  order by id

结果:

100 Bronze  2
101 Gold    5
102 Silver  1
103 Steel   2

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章