sql—根据另一个查询返回的值运行查询

wwwo4jvm 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(551)

考虑以下表格：

WorkOrder     Date
   101       1/2/2020
   101       1/2/2020
   102       1/2/2020
   102       1/3/2020
   101       1/3/2020
   103       1/4/2020
   104       1/4/2020
   104       1/5/2020
   103       1/5/2020
   104       1/5/2020
   102       1/5/2020
WorkOrder    Operation
   101        Process
   101        Run
   102        Process
   102        Run
   101        Ship
   103        Process
   104        Process
   104        Run
   103        Run
   104        Ship
   102        Ship

如果我要运行查询1：

SELECT *
FROM table1
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE Date = '1/4/2020'

我需要一个查询，该查询将返回该日期范围内未结工单的所有行（即包括流程、运行和发货）。这相当于我想要的回报：

SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE WorkOrder = '102' AND Work Order = '103' AND Work Order = '104'

因此，预期输出为：

102          Process    1/2/2020
   102           Run       1/3/2020
   103          Process    1/4/2020
   104          Process    1/4/2020
   104           Run       1/5/2020
   103          Ship       1/5/2020
   104          Ship       1/5/2020
   102          Ship       1/5/2020

但我不想指定从query1返回的每个工单。另外，这在日期范围内如何工作：

SELECT *
FROM table
JOIN table2 on table1.WorkOrder = table2.WorkOrder 
WHERE Date <= 'X' AND Date >= 'Y'

sql

来源：https://stackoverflow.com/questions/63025268/run-a-query-based-on-the-values-returned-by-another-query

3条答案

按热度按时间

bqucvtff1#

如果您希望工单的所有行：
在该日期或之前开始，以及
在该日期之后于结束，并且
包括所有三个操作（进程、运行和装运）。
你可以做：

select *
from t
where workorder in (
  select a.workorder
  from t a
  join t b on b.workorder = a.workorder
  join t c on c.workorder = a.workorder
  where a.operation = 'Process'
    and b.operation = 'Run'
    and c.operation = 'Ship'
    and a.date <= '2020-01-04'
    and c.date >= '2020-01-04'
) x

赞(0）回复(0）举报 2021-07-24

bihw5rsg2#

（如果您使用的是oracle数据库）可以使用with子句定义时态表

WITH query1 as   -- query1 is the name of this temporal table 
(
    SELECT *
    FROM table
    WHERE Date = '1/4/2020'
)
SELECT *
FROM query1     -- you can do querys in query1 (the temporal table)
WHERE   WorkOrder  = '102' 
    AND Work Order = '103' 
    AND Work Order = '104'

赞(0）回复(0）举报 2021-07-24

d4so4syb3#

我不确定这是不是你要找的东西。。
我假设“过程”被视为未结订单。顺便说一下，下面的查询没有经过测试：

Select a.WorkOrder, a.Date, a.Operation yourtable a WHERE a.Operation IN
(Select Operation from yourtable where Date ='1/4/2020') ;

赞(0）回复(0）举报 2021-07-24

我来回答

sql—根据另一个查询返回的值运行查询

3条答案

相关问题

热门标签

最新问答