如何将IF ELSE CONDITION放入Oracle查询中? [英] How can i put IF ELSE CONDITION in Oracle query?

问题描述

我有一个表A和B，如下所示，

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

表 答:

• id
• idB
• 名称
• 教师

B:

• id
• 名称

表B有2条记录，如下所示.

Table B has 2 records as below.

SELECT *
FROM B;

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

表A具有8条记录，如下所示.

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

我的问题是:

我该如何从表B中选择数据，其中教师应为IT人员，如果存在多个，则应使用最大ID.而且，如果没有任何IT，那应该生意吗?

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和B记录.

1. 8， 1 ，H，IT，第一
2. 15， 2 ，G，业务，第二

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?

推荐答案

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

输出

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

演示

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

这篇关于如何将IF ELSE CONDITION放入Oracle查询中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！