Oracle- एक शर्त के आधार पर संख्या पंक्तियों की गणना करें

Question

मैं एक 'टिकट' बनाना चाहता हूं जो प्रत्येक आईडी के लिए पास की संख्या की गणना करता है। जब हमारे पास किसी भी आईडी पर गोल्ड पास होता है, तो इसका मतलब है कि बुकिंग में शामिल सभी लोगों के लिए पास लागू होता है। तो इस उदाहरण के लिए, हम 5 गिनना चाहते हैं। अन्य पास_कोड के लिए, हम केवल पास की संख्या गिनना चाहते हैं और जो शून्य हैं उन्हें बाहर करना चाहते हैं। मेरे पास नीचे अपेक्षित आउटपुट है।

कहो मेरे पास यह डेटा है:

 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.

)

किसी भी मदद या सलाह की बहुत सराहना की जाएगी।

oracle plsqldeveloper

1

Greg 24 सितंबर 2020, 12:09

Answer 1

क्या यह आपके लिए कार्य करता है?

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

1

Plirkee 24 सितंबर 2020, 12:35

Answer 2

अगर मैं आपके प्रश्न को सही समझता हूं तो प्रश्न निम्न की तरह होना चाहिए

** टेबल

create table test (ID number, GuestId number, Pass_code varchar2(10));
insert into test values(100,001,'Bronze');
insert into test values(100,002,'Bronze');
insert into test values(101,103,'Gold');
insert into test values(101,104,NULL);
insert into test values(101,105,NULL);
insert into test values(101,106,NULL);
insert into test values(101,107,NULL);
insert into test values(102,208,'Silver');
insert into test values(103,209,'Steel');
insert into test values(103,210,'Steel');
insert into test values(103,211,NULL);
commit;


SQL> select * from test order by 1,2;

        ID    GUESTID PASS_CODE
---------- ---------- ------------------------------
       100          1 Bronze
       100          2 Bronze
       101        103 Gold
       101        104
       101        105
       101        106
       101        107
       102        208 Silver
       103        209 Steel
       103        210 Steel
       103        211

11 rows selected.

** पूछताछ

WITH PASSES AS (
SELECT T1.ID,T1.PASS_CODE, COUNT(T2.ID) QUANTITY FROM TEST T1, TEST T2
WHERE T1.PASS_CODE='Gold' AND T1.ID=T2.ID
GROUP BY T1.ID,T1.PASS_CODE
UNION ALL
SELECT ID,PASS_CODE, COUNT(*) QUANTITY FROM TEST
WHERE PASS_CODE IS NOT NULL AND 
      PASS_CODE != 'Gold'
GROUP BY ID,PASS_CODE)
SELECT ID, QUANTITY || ' (' || PASS_CODE || ')' RESULT FROM PASSES
ORDER BY ID;

** नतीजा

        ID RESULT
---------- --------------------
       100 2 (Bronze)
       101 5 (Gold)
       102 1 (Silver)
       103 2 (Steel)

1

Vic VKh 25 सितंबर 2020, 09:01

Oracle- एक शर्त के आधार पर संख्या पंक्तियों की गणना करें

2 जवाब

संबंधित सवाल

नए सवाल

oracle