我有三列操作日期、文本和执行日期。
我想获取最小操作日期,为最小操作日期和最大性能日期输入的文本。以下是详细信息:
Create Table Performance (
PerformanceId int identity primary key,
ActionDate datetime,
Notes varchar(500),
PerformanceDate datetime,
jobId int
)
我想把行动日期定为2018-10-16 09:59:00.000,作为工作的文本将被搁置,执行日期为2018-10-26。
Id ActionDate Notes PerformanceDate JobId
1 2018-10-16 Job to be on hold 2018-10-26 10
2 2018-10-26 Job to be Released 2018-10-16 10
3 2018-10-05 Job hold back 2018-10-11 10
针对一个作业执行多个性能条目。我想显示一个摘要报告,显示完成的第一个条目,即最小Actin日期和与该日期无关的以及输入的最大PerformanceDate
首先选择这两个日期:
SELECT min(action_date) From table_name
UNION
SELECT max(performance_date) From table_name
然后得到这两个的文本:
SELECT text, action_date From table_name
WHERE action_date IN (
SELECT min(action_date) From table_name
UNION
SELECT max(performance_date) From table_name
)
如果不想使用子查询,可以使用窗口函数:
select distinct jobid,
min(action_date) over (partition by jobid),
max(performance_date) over (partition by jobid),
first_value(notes) over (partition by jobid order by action_date)
from t
where jobid = 10;
另一种方法是:
select top (1) t.*,
max(performance_date) over (partition by jobid)
from t
where jobid = 10
order by action_date;