tsql最小和最大日期

8xiog9wr 于 2021-07-26 发布在 Java

关注(0)|答案(4)|浏览(272)

我有一个包含datetime、jobid、taskid列的表

DateTime                 JobID            TaskID
2020-05-16 06:43:11.687   353              NULL
2020-05-16 06:45:11.687   353              123
2020-05-16 06:43:11.687   353              123
2020-05-16 06:43:12.297   353              456
2020-05-16 06:44:30.750   353              456
2020-05-16 06:44:30.750   353              456
2020-05-16 06:44:30.750   353              789
2020-05-16 06:45:05.000   353              789
2020-05-16 06:48:07.750   353              789
2020-05-16 06:49:01:110   353              011
2020-05-16 06:50:11:112   353              NULL

我需要编写一个查询，给出子taskid456的min（datetime）和父jobid353的max（datetime），输出如下

Startime                     Endtime
2020-05-16 06:43:12.297      2020-05-16 06:50:11:112

这只是一天，即16日，同样，我需要一周的数据。我试着在同一张table上做一个连接

select 
mt1.Starttime, MAX(t1.JobLogDateTime) AS Endtime
from 
JobLog t1
inner join (
    SELECT jobid, MIN(JobLogDateTime) AS Starttime
    FROM JobLog WHERE taskid=456
    group by JobLogDateTime, jobid

) mt1
    on t1.JobID=mt1.jobid
    GROUP BY t1.JobLogDateTime

这是由于group by子句不正确造成的问题，我还尝试了：

SELECT FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd') AS mainrundate, 
child.starttime, 
MAX(joblogdatetime) FROM JobLog jbl INNER JOIN 
(
SELECT FORMAT(JobLogDateTime, 'yyyy-MM-dd') AS rundate, MIN(joblogdatetime) 
AS Starttime FROM JobLog 
WHERE TaskID=456
GROUP BY FORMAT(joblogdatetime, 'yyyy-MM-dd')
) child ON child.rundate=jbl.mainrundate 
GROUP BY child.starttime, FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd')
ORDER BY FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd') desc

但是它说连接是不正确的

sql sql-server

来源：https://stackoverflow.com/questions/62431560/tsql-min-and-max-date

4条答案

按热度按时间

z9smfwbn1#

试试这个

WITH child
     AS (SELECT taskid, 
                MIN(JobLogDateTime) AS Endtime
         FROM JobLog
         WHERE taskid = 456
         GROUP BY taskid)

SELECT MAX(j.JobLogDateTime) AS Startime, 
    MIN(c.Endtime) AS Endtime
FROM JobLog AS j
JOIN child AS c ON j.TaskID = c.TaskID;

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

ubof19bj2#

我认为还需要在主查询中按jobid分组，然后在子查询中按字段添加group，t1.joblogdatetime是要聚合的字段，而不是要分组的字段，子查询中的情况也是一样，如果执行min（x），则不能按x进行group。尝试使用以下选项：

select 
t1.jobid, mt1.Starttime, MAX(t1.JobLogDateTime) AS Endtime
from 
JobLog t1
inner join (
    SELECT jobid, MIN(JobLogDateTime) AS Starttime
    FROM JobLog WHERE taskid=456
    group by jobid
) mt1
    on t1.JobID=mt1.jobid      
    GROUP BY t1.jobid, mt1.StartTime

我还删除了't1.jobid=mt1.jobid'末尾的点，我认为这是一个打字错误
编辑：几个编辑。。我的第一篇文章和格式不是很好：）

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

pprl5pva3#

我可以通过如下更改查询来修复它：

SELECT FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd') AS JobRunDate, MIN(child.starttime) 
AS Startime, MAX(jbl.joblogdatetime) AS EndTime
FROM JobLog jbl left JOIN 
(
SELECT  joblogdatetime AS Starttime FROM JobLog 
WHERE TaskID=456
) child ON child.Starttime=jbl.JobLogDateTime
WHERE jobid=353 
GROUP BY FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd')
ORDER BY FORMAT(jbl.JobLogDateTime, 'yyyy-MM-dd') desc

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

11dmarpk4#

SELECT 
    min( datetime ) OVER ( task_id )
    , max( datetime ) OVER ( task_id )
FROM 
    X
WHERE 
    job_id = y

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

我来回答

tsql最小和最大日期

4条答案

相关问题

热门标签

最新问答