解决方案 »
- 如何实现优化查询?
- 急求一SQL 语句!要求由Table1得到Table2?
- 如何判断sql server数据表中是否新添加了记录?
- 请教日期分段SUM怎么写
- 请问:怎样将sql.sql脚本文件生成数据库结构
- 求一个分类统计的sql语句
- how can i get the sum from these three number in asp ?
- 数据库还原到不同的数据库名,且不想要日志文件;怎么整??
- 当我在SQL server数据库中删除一部分过时数据,但因记录太多,无法删除,显示超时错误!请众高手帮忙!!!
- 怎么引入计算机学习?初学者,先学那种语言为好呢?请教大家
- 请教MSSQL性能问题?
- 一个表中的几个列 更新到另一个表中的几个列
ROW_NUMBER() OVER(PARTITION BY dbo.cnt_loop_index.cnt_no ORDER BY begin_time DESC) rowId,
dbo.cnt_ie_info.cnt_type, dbo.cnt_ie_info.cnt_size, dbo.cnt_ie_info.cnt_category, dbo.cnt_ie_info.cnt_kind,
dbo.cnt_loop_index.begin_time, dbo.cnt_loop_index.end_time, dbo.cnt_loop_index.cnt_no,
dbo.cnt_ie_info.city_name AS begin_city_name,dbo.cnt_ie_info.yard_name AS begin_yard_name,
dbo.cnt_loop_index.begin_job_no, dbo.cnt_loop_index.end_job_no,cnt_ie_info_1.city_name AS end_city_name,
cnt_ie_info_1.yard_name AS end_yard_name,
DATEDIFF(day, dbo.cnt_loop_index.begin_time,
dbo.cnt_loop_index.end_time) + 1 AS days,
b.out_yard_time as lastOutRussia,
b.name_en as lastOutRussiaCity,datediff(day,a.in_yard_time,b.out_yard_time)+1 AS InRussiaDays,
datediff(day,dbo.cnt_loop_index.begin_time,c.out_yard_time)+1 AS InChinaDays,
datediff(day,c.out_yard_time,a.in_yard_time)+1 as OnWayDays,
a.in_yard_time as firstInRussia,
a.name_en as firstInRussiaCity,
case when isnull(dbo.cnt_loop_index.end_time,'')='' then
datediff(day, a.in_yard_time,getdate())+1
when dbo.cnt_loop_index.end_time <>'' then
datediff(day, a.in_yard_time,dbo.cnt_loop_index.end_time)+1
end useDays,
dbo.cnt_loop_index.col_1, dbo.cnt_loop_index.RIDFROM dbo.cnt_loop_index
outer apply (
select top 1 code_port.name_en ,in_yard_time from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where in_yard_time
between cnt_loop_index.begin_time and isnull(cnt_loop_index.end_time,getdate())
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='俄罗斯'
order by cnt_ie_info.create_time asc
)a
outer apply (
select top 1 out_yard_time,code_port.name_en from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where cnt_time_state.in_yard_time
between cnt_loop_index.begin_time and isnull(cnt_loop_index.end_time,getdate())
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='俄罗斯'
order by cnt_ie_info.create_time desc
)b
outer apply
(
select top 1 out_yard_time from cnt_ie_info
left join dbo.cnt_time_state on cnt_time_state.cnt_ie_fid = cnt_ie_info.rid
left join code_port on code_port.name_cn= cnt_ie_info.city_name
where cnt_time_state.in_yard_time < a.in_yard_time
and cnt_time_state.in_yard_time >=(cnt_loop_index.begin_time-1)
and cnt_no=cnt_loop_index.cnt_no
and code_port.country_name='中国'
order by out_yard_time desc
)c
LEFT OUTER JOIN
dbo.cnt_ie_info ON dbo.cnt_loop_index.begin_job_no = dbo.cnt_ie_info.job_no LEFT OUTER JOIN
dbo.cnt_ie_info AS cnt_ie_info_1 ON dbo.cnt_loop_index.end_job_no = cnt_ie_info_1.job_no
GO
CREATE NONCLUSTERED INDEX IX_cnt_time_state_1
ON [dbo].[cnt_time_state] ([cnt_ie_fid],[in_yard_time])
INCLUDE ([out_yard_time])
GO