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屋!