英文:
inner join 3 tables in sql
问题
我有3个表格：orders（订单），stocks（库存），status（状态）
最终结果：
订单 >> 订单日期 = 2023-10-01 到 2023-10-30
状态 >> 状态ID = 2, 3, 5
库存 >> 关键用户，库存位置
查询：
select
s.keyuser, o.order_date
from
stocks s
inner join
orders o on o.order_stock_id = s.stocks_id
inner join
status sta on sta.status_id = o.order_status_id
where
o.order_date <= date '2023-10-01'
and o.order_date >= date '2023-10-30'
and s.keyuser = 'kiki'
and sta.status_id in (2, 3, 5)
结果：无输出
英文:
I have 3 tables: orders, stocks, status
Final result:
> orders >> order_date = 2023-10-01 to 2023-10-30
> status >> status_id = 2,3,5
> stocks >> keyuser, stock_loc
Query:
select
s.keyuser, o.order_date
from
stocks s
inner join
orders o on o.order_stock_id = s.stocks_id
inner join
status sta on sta.status_id = o.order_status_id
where
o.order_date <= date '2023-10-01'
and o.order_date >= date '2023-10-30'
and s.keyuser = 'kiki'
and sta.status_id in (2, 3, 5)
Result: no output
答案1
得分: 2
你的日期范围比较方式颠倒了，并且 KIKI 应该使用大写以匹配你的数据：
select s.keyuser,
o.order_date
from stocks s
inner join orders o
on o.order_stock_id = s.stocks_id
inner join status sta
on sta.status_id = o.order_status_id
where o.order_date >= date '2023-10-01'
and o.order_date < date '2023-10-31'
and s.keyuser = 'KIKI'
and sta.status_id in (2, 3, 5)
注意：在Oracle中，DATE 总是带有时间部分；许多客户端应用程序（如SQL*Plus、SQL Developer等）对于 DATE 使用默认格式，不显示时间部分，但即使不显示，时间部分仍然存在。因此，如果你的 order_date 的时间部分不是午夜，你应该使用 < DATE '2023-10-31' 而不是 <= DATE '2023-10-30'，以便在最后一天获得完整的 24 小时范围。
英文:
Your date range comparisons are the wrong way round and KIKI should be in upper-case to match your data:
select s.keyuser,
o.order_date
from stocks s
inner join orders o
on o.order_stock_id = s.stocks_id
inner join status sta
on sta.status_id = o.order_status_id
where o.order_date >= date '2023-10-01'
and o.order_date < date '2023-10-31'
and s.keyuser = 'KIKI'
and sta.status_id in (2, 3, 5)
Note: In Oracle a DATE always has a time component; many client applications (i.e. SQL*Plus, SQL Developer, etc.) use a default format for DATEs that does not show the time component but it is still there even if it is not displayed. Therefore, if your time component of order_date is anything other than midnight you want to use < DATE '2023-10-31' rather than <= DATE '2023-10-30' so that you get a full 24 hour range on the final day.
答案2
得分: 1
select order_date, keyuser, stock_loc
from orders as o
inner join status as s ON o.order_status_id = s.status_id
inner join stocks as sk ON o.order_stock_id = sk.stocks_id
where order_date >= DATE '2023-10-01' and order_date <= DATE '2023-10-30'
and status_id in (2,3,5)
英文:
select order_date, keyuser, stock_loc
from orders as o
inner join status as s ON o.order_status_id = s.status_id
inner join stocks as sk ON o.order_stock_id = sk.stocks_id
where order_date >= DATE '2023-10-01' and order_date <= DATE '2023-10-30'
and status_id in (2,3,5)
答案3
得分: 0
你的日期比较不正确，如果你不希望记录的日期既小于 '2023-10-01' 又大于 '2023-10-31'。另外，如果你希望它对 keyuser 比较不区分大小写，你需要单独指定它。
select
s.keyuser, o.order_date
from
stocks s
inner join
orders o on o.order_stock_id = s.stocks_id
inner join
status sta on sta.status_id = o.order_status_id
where
o.order_date >= date '2023-10-01'
and o.order_date <= date '2023-10-30'
and s.keyuser = 'kiki' collate binary_ci
and sta.status_id in (2, 3, 5)
英文:
Your date comparison is incorrect if you don't expect the recorded date to be both less than '2023-10-01' and greater than '2023-10-31'. Also, if you want it to work as case insensitive for your keyuser comparison, you need to specify it separately.
select
s.keyuser, o.order_date
from
stocks s
inner join
orders o on o.order_stock_id = s.stocks_id
inner join
status sta on sta.status_id = o.order_status_id
where
o.order_date >= date '2023-10-01'
and o.order_date <= date '2023-10-30'
and s.keyuser = 'kiki' collate binary_ci
and sta.status_id in (2, 3, 5)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论