针对 ID 的两个表中两列的 SQL 最小值 [英] SQL min values from two columns across two tables against ID
问题描述
我有下面提到的数据.我正在寻找开始消息的最小值和相应的成功消息的最小值.如果没有开始或成功消息，那么它应该显示为空.
I have below mentioned data. I am looking to get min of Start message and corresponding min of success message. If there is no start or success message present then it should show null.
开始消息表:
ID1 Timestamp_start_msg_recieved date jobid message time in seconds
1234 5/14/2014 10:02:29 5/14/2014 abc start 262
1234 5/14/2014 10:02:31 5/14/2014 abc start 264
1234 5/14/2014 10:02:45 5/14/2014 abc start 278
1234 5/14/2014 10:02:50 5/14/2014 abc start 285
1234 5/14/2014 10:09:04 5/14/2014 abc start 165
1234 5/14/2014 10:09:06 5/14/2014 abc start 2167
1234 5/14/2014 10:09:16 5/14/2014 abc start 2180
1234 5/14/2014 10:09:26 5/14/2014 abc start 2190
1234 5/14/2014 11:45:11 5/14/2014 abc start 8767
1234 5/14/2014 16:48:20 5/14/2014 abc start 878
1234 5/14/2014 19:02:52 5/14/2014 abc start 687
5678 5/14/2014 22:02:52 5/14/2014 pqr start 501
5678 5/14/2014 23:10:40 5/14/2014 abcd start 200
成功消息表:
ID1 Timestamp_success_msg_recieved date jobid message time in seconds
1234 5/14/2014 10:02:52 5/14/2014 abc successful 290
1234 5/14/2014 10:09:32 5/14/2014 abc successful 4280
1234 5/14/2014 11:45:15 5/14/2014 abc successful 8774
1234 5/14/2014 11:45:18 5/14/2014 abc successful 8777
1234 5/14/2014 11:45:19 5/14/2014 abc successful 8778
1234 5/14/2014 11:45:25 5/14/2014 abc successful 8784
1234 5/14/2014 16:48:22 5/14/2014 abc successful 880
1234 5/14/2014 19:03:00 5/14/2014 abc successful 699
5678 5/14/2014 22:03:00 5/14/2014 pqr successful 250
5678 5/19/2014 14:00:16 5/19/2014 pqr successful 400
预期结果:
ID1 IMESTAMP_for_start_message TIMESTAMP_for_success_message Date Jobid msg msg start_secs success_secs
1234 5/14/2014 10:02:29 5/14/2014 10:02:52 5/14/2014 abc start success 262 290
1234 5/14/2014 10:09:04 5/14/2014 10:09:32 5/14/2014 abc start success 165 4280
1234 5/14/2014 11:45:11 5/14/2014 11:45:25 5/14/2014 abc start success 8767 8784
1234 5/14/2014 16:48:20 5/14/2014 16:48:22 5/14/2014 abc start success 878 880
1234 5/14/2014 19:02:52 5/14/2014 19:03:00 5/14/2014 abc start success 687 699
5678 5/14/2014 22:02:52 5/14/2014 22:03:00 5/14/2014 pqr start success 501 699
5678 5/14/2014 23:10:40 null 5/14/2014 abcd start success 250 null
5678 null 5/19/2014 14:00:16 5/19/2014 pqr null success null 400
我试图将 start_timestamp 的 Min 与与 id1 和 jobid 对应的 success_timestamp 的下一个 Min 结合起来.如果给定的 id1 和 jobid 有一个开始消息列表并且没有成功消息，那么它应该显示为 NULL，反之亦然.尝试使用 WITH 子句使用临时表，也使用自连接方法.下面是我的查询，但 WITH 子句查询返回表中整体数据的 MIN.
I am trying to get Min of start_timestamp in combination with the very next Min of success_timestamp corresponding to id1 and jobid. If there is a list of start message and no success message for a given id1 and jobid, then it should show NULL and viceversa. Tried using Temporary table using WITH clause and also used self join method. Below is my query, But WITH clause query returns MIN of overall data in the table.
注意:TIME IN SECONDS 具有随机值而非实际数据.
NOTE: TIME IN SECONDS has random values and not actual data.
使用的查询:
WITH DATA AS
(SELECT MIN(smt.column13) timestamp_for_success_message
FROM success_table1 smt, start_table2 b
WHERE
(SMT.id1 = b.id1)
AND (SMT.jobid = b.jobid)
AND (SMT.timestamp_for_success_message_recieved >= b.timestamp_for_start_message_recieved)
)
SELECT distinct a.timestamp_for_success_message_recieved,
b.timestamp_for_start_message_recieved,
b.id1,
b.jobid
FROM data a,
start_table2 b
order by b.timestamp_start_message_recieved, a.timestamp_for_success_message_recieved, b.jobid, b.id1;
推荐答案
我对这个问题的理解是启动表中的每一行都代表某种启动的作业.成功表代表作业完成.要找出作业何时完成，您需要在成功表中找到与 id1 和 jobid 列匹配的行，其最低时间戳大于起始行时间戳，除非起始表中存在与成功匹配的较早行行.
My understanding of the issue is that each row in the start table represents a job of some kind starting. The success table represents that job finishing. To find out when a job finished, you need to find the row in the success table that matches id1 and jobid columns with the lowest timestamp that is greater that the start row timestamp unless there is an earlier row in the start table that matches the success row.
例如起始表中的第一行与成功表中的第一行匹配，但起始表中的第二行在成功表中没有匹配.
For example the first row in the start table matches the first row in the success table, but the second row in the start table has no match in the success table.
为了解决这个问题，我使用嵌套子查询来构建所需的每条数据.
To resolve this I've used nested sub-queries to construct each piece of data needed.
SELECT start.id1, start.jobid, start.TIMESTAMP_START_MSG_RECIEVED AS start, table2.end
FROM start
LEFT OUTER JOIN (
SELECT table1.id1, table1.jobid, MIN(table1.start) AS start, table1.end
FROM (
SELECT s.id1, s.jobid, s.TIMESTAMP_START_MSG_RECIEVED AS start, MIN(t.TIMESTAMP_SUCCESS_MSG_RECIEVED) AS end
FROM start AS s
LEFT OUTER JOIN success AS t ON t.id1 = s.id1 AND t.jobid = s.jobid AND t.TIMESTAMP_SUCCESS_MSG_RECIEVED >= s.TIMESTAMP_START_MSG_RECIEVED
GROUP BY s.id1, s.TIMESTAMP_START_MSG_RECIEVED, s.jobid, s.time
ORDER BY start) AS table1
GROUP BY table1.id1, table1.jobid, table1.end
ORDER BY table1.end) AS table2 ON table2.id1 = start.id1 AND table2.jobid = start.jobid AND table2.start = start.TIMESTAMP_START_MSG_RECIEVED
ORDER BY start
最里面的选择从成功表中获取每个开始行和最低结束时间.
The innermost select gets each start row and the lowest end time from the success table.
下一个选择然后从 table1 中获取具有最低开始时间的行然后外部选择将起始表与表 2 连接起来，以包含所有尚未完成的作业.
The next select then gets the rows with the lowest start time from table1 The outer select then joins the start table with table 2 to include all the jobs that have not finished.
这篇关于针对 ID 的两个表中两列的 SQL 最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
