How can i put IF ELSE CONDITION in Oracle query?

问题:

I have a table, A and B which are shown below,

Table A:

• id
• idB
• name
• faculty

B:

• id
• name

Table B has 2 records as below.

SELECT *
FROM B;

 1, 1, 'First'
 2, 2, 'Second'

Table A has 8 records as below.

SELECT *
FROM A;

 1, 1, A, IT
 2, 1, B, IT
 3, 1, C, IT
 4, 1, D, Medicine
 5, 1, E, Medicine
 6, 1, F, Business
 7, 1, G, Business
 8, 1, H, IT

 9, 2, A, Medicine
 10, 2, B, Medicine
 11, 2, C, Medicine
 12, 2, D, Medicine
 13, 2, E, Medicine
 14, 2, F, Medicine
 15, 2, G, Business
 16, 2, H, Medicine

My question is:

How can I select data from table B where faculty should be IT and if there are multiple it should get with max ID. AND if there is no any IT, it should be get business?

My select view should be look like this below:

A and B records.

1. 8, 1, H, IT, First
2. 15, 2, G, Business, Second

Please could you advise and help in which way we can retrieve these data?

回答1:

this query will help you to get your desired result

SELECT id
    ,name
    ,faculty
FROM A
WHERE faculty IN ('IT', 'Business')

回答2:

How can I select data from table B where faculty should be IT and if there are multiple it should get with max ID. AND if there is no any IT, it should be get business?

This will get the row with the maximum ID that is in IT and if there are no IT rows then Business

SELECT *
FROM   (
  SELECT A.id,
         A.idB,
         A.name,
         A.faculty,
         B.name AS bname
  FROM   A
         INNER JOIN B
         ON ( A.idB = B.id )
  WHERE  A.faculty IN ( 'IT', 'Business' )
  ORDER BY
         DECODE( A.faculty, 'IT', 1, 'Business', 2 ),
         A.id DESC
)
WHERE  ROWNUM = 1;

If you want the top row from each group then:

SELECT id,
       idB,
       name,
       faculty,
       bname
FROM   (
  SELECT A.id,
         A.idB,
         A.name,
         A.faculty,
         B.name AS bname,
         ROW_NUMBER() OVER (
           PARTITION BY A.faculty
           ORDER BY     A.id DESC
         ) AS rn
  FROM   A
         INNER JOIN B
         ON ( A.idB = B.id )
  WHERE  A.faculty IN ( 'IT', 'Business' )
)
WHERE  rn = 1;

回答3:

SELECT A.*,T.Name
FROM 
TableA A
INNER JOIN
(
SELECT MAX(A.id) AS id,A.FACULTY,B.NAME,
  ROW_NUMBER() OVER (PARTITION BY B.NAME ORDER BY MAX(A.id) DESC) AS RN FROM TableA A
INNER JOIN TableB B
ON A.idB=B.id
WHERE faculty IN ('IT', 'Business')
GROUP BY A.FACULTY,B.NAME
) T
ON A.id = T.id
AND A.FACULTY = T.FACULTY
WHERE T.RN=1

Output

ID  IDB  NAME   FACULTY   NAME
8   1    H      IT        First
15  2    G      Business  Second

Demo

http://sqlfiddle.com/#!4/98b84/24