oracle中的条件选择语句 [英] conditional select statement in oracle

问题描述

我有两个表，分别称为订单"和公共假期":

I have two tables called order and public holiday as following:

订单表:

OrderId      OrderDate
---------------------------
    1        10 Mar 2017
    2        12 Mar 2017
    3        30 Mar 2017

公众假期表:

HoliId |     HolidayDate
---------------------------
    1        10 Mar 2017
    1        16 Mar 2017
    1        17 Mar 2017

我想像下面的伪代码一样做SQL

I want to do SQL like following pseudo-code

SELECT OrderId, OrderDate, 
if OrderDate is in Holiday Table, 
  then "public holiday" 
  else to_char(to_date(DAY, 'dd/mm/yy'), 'OrderDate')
from Order

所以我想要的结果就像下面的

So the result that I want to have is like following

OrderId      OrderDate       DAY
-----------------------------------------
    1        10 Mar 2017     Public holiday
    2        12 Mar 2017     Monday
    3        30 Mar 2017     Friday

我想通过在公共假期表中查看订购日期是否为公共假期

I want to see if the order date is public holiday based on the public holiday table or not by putting a column

有可能这样做吗?

推荐答案

您可以像这样使用外部联接:

You can use an outer join like this:

SELECT OrderId,
       OrderDate, 
       case when holidaydate is not null then  'Public holiday' 
             else to_char(OrderDate, 'Day') end as DAY
from orders
     left outer join holidays
        on OrderDate = holiday_date ;

如果日期匹配holidaydate不为null，则CASE子句显示您想要的字符串，否则显示orderdate的日期.

If the dates match holidaydate is not null so the CASE clause displays your desired string otherwise it displays the day of the orderdate .

order是保留字.为了避免ora-00903错误，大概您的真实表具有不同的名称.我在示例中使用了orders，因此您需要编辑代码以匹配表名.

order is a reserved word. Presumably your real table has a different name, to avoid the ora-00903 error. I have used orders in my example, so you will need to edit my code to match your table name(s).

这篇关于oracle中的条件选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！