oracle 根据不同列中的最小值从连接表中选择值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4674891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select value from joined table based on lowest value in a different column
提问by pablo
(SQL query on Oracle DB)
（Oracle DB 上的 SQL 查询）
I'm trying to display an equipment id number on a job record based on the highest priority (lowest number) of all tasks associated with the job. I also need to display all tasks for the job so I cannot simply limit the query to the task with the highest priority. As the highest priority is 1 (with 2 as second highest and so on), querying for the task with priority 1 would always work. The problem is that sometimes the task with priority 1 gets deleted and so the priority 2 task becomes the highest priority (you cannot have duplicate priorities and priority is always an integer).
我正在尝试根据与作业相关的所有任务的最高优先级（最低编号）在作业记录上显示设备 ID 编号。我还需要显示作业的所有任务，因此我不能简单地将查询限制为具有最高优先级的任务。由于最高优先级为 1（2 为第二高，依此类推），查询优先级为 1 的任务将始终有效。问题是有时优先级为 1 的任务会被删除，因此优先级为 2 的任务成为最高优先级（您不能有重复的优先级，并且优先级始终是整数）。
Here is a sample query that works when pull the equipment id based on priority 1 task:
这是一个示例查询，它在根据优先级 1 任务提取设备 ID 时起作用：
SELECT j.title,
j.jobnum,
a.eqid,
a.prior,
a.desc,
b.eqid peqid
FROM JOB j
LEFT JOIN TASK a ON a.jobnum = j.jobnum
LEFT JOIN TASK b ON b.jobnum = j.jobnum
AND b.prior = 1
WHERE j.jobnum = '123'
ORDER BY a.prior
The above query would produce the following results if it has 3 tasks:
如果上面的查询有 3 个任务，它会产生以下结果：
TITLE JOBNUM EQID PRIOR DESC PEQID
newjob 123 HAQ7 1 fix this HAQ7
newjob 123 PDL 2 clean this HAQ7
newjob 123 ACCH 3 move this HAQ7
However, if task with priority 1 is deleted from the job you now must find the lowest priority task to assign the job an equipment id.
但是，如果从作业中删除优先级为 1 的任务，您现在必须找到优先级最低的任务来为作业分配设备 ID。
I was trying to essentially do something along these lines but it doesn't work (gives message that group function is not allowed here):
我试图基本上按照这些方式做一些事情，但它不起作用（给出了此处不允许使用组功能的消息）：
select job.title, job.jobnum, task.eqid, task.prior, task.desc, tp.eqid peqid
from job
left join task on job.jobnum = task.jobnum
left join task tp on job.jobnum = tp.jobnum
and tp.prior = min(tp.prior)
I researched using a subquery in the join with a group function but can never seem to find one that works for what I'm trying to accomplish. Any help would be greatly appreciated.
我研究了在连接中使用子查询和组函数，但似乎永远找不到适合我想要完成的任务的子查询。任何帮助将不胜感激。
回答by RichardTheKiwi
Maybe something like this?
也许是这样的？
select job.title, job.jobnum, task.eqid, task.prior, task.desc, tp.eqid peqid
from job
left join task on job.jobnum = task.jobnum
left join (
select jobnum, MIN(prior) prior
from task
group by jobnum) m on m.jobnum = job.jobnum
left join task tp on m.jobnum = tp.jobnum and m.prior = tp.prior
where job.jobnum = '123'
order by task.prior asc
This two-level subquery works in SQL Server, it may also work in Oracle
这个两级子查询适用于SQL Server，也可以适用于Oracle
select job.title, job.jobnum, task.eqid, task.prior, task.desc,
(select tp2.eqid from task tp2
where tp2.jobnum=job.jobnum and tp2.prior =
(select MIN(tp.prior) from task tp
where tp.jobnum=job.jobnum)) peqid
from job
left join task on job.jobnum = task.jobnum
where job.jobnum = '123'
order by task.prior asc
