请教一个比较复杂的SQL 语句

leon51 2017-09-19 09:21:28

这两天连续发了几个贴，怕是没有描述清楚。
有以上两个表，要求首先根据时间判断状态，再由状态得到各自的标准时间（如TimeCriteria中所示），
得出各状态的标准时间后，再用当前的时间-对应的时间-标准时间,如果结果为正则为已超出时间，为负则为剩余时间。

所谓对应时间是指:
待安排:recieve_time;
待发料:micheck_time
待发EQ:mi_time
待安排审核:mi_finish_time
MI制作中:answer_time
对应时间统一放一列可能比较方便点

最终的结果如下图所示:

多谢大家的帮助！

...全文

419 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

leon51 2017-09-21

打赏
举报

引用

正好也有这个需求，不用再提问了，非常感谢楼主和上面回答的朋友~谢谢

莫非是同行？

lixizheng 2017-09-20

打赏
举报

正好也有这个需求，不用再提问了，非常感谢楼主和上面回答的朋友~谢谢

leon51 2017-09-20

打赏
举报

引用

兄弟，我那个时间处理的不是很好。剩下你的看看怎么处理时间的减法吧

非常感谢，我上班再测下。

leon51 2017-09-20

打赏
举报

引用

SELECT a.job 料号, CASE WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time)='' THEN '未安排' WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time)='' THEN '待发料' WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time)='' THEN '待发EQ' WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time)='' THEN '待安排审核' WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time)='' THEN 'MI制作中' WHEN TRIM(a.sent_time)!='' THEN '已完成' END AS '订单状态', CASE WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time)='' THEN b.'未安排' WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time)='' THEN b.'待发料' WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time)='' THEN b.'待发EQ' WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time)='' THEN b.'待安排审核' WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time)='' THEN b.'MI制作中' WHEN TRIM(a.sent_time)!='' THEN '已完成' END AS 标准时间, '' 还剩多少时间, '' 已超时时间 FROM PartList a, table2 b

版主，关键是还要计算剩余时间和已超过的时间，劳驾了。。。

leon51 2017-09-20

打赏
举报

谢谢你了，周六上班测一下

ITVin 2017-09-20

打赏
举报

兄弟，我那个时间处理的不是很好。剩下你的看看怎么处理时间的减法吧

SELECT 
    a.job 料号,
    CASE
        WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time) is null THEN '待安排'   
        WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time) is null THEN '待发料'       
        WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time) is null THEN '待发EQ'
        WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time) is null THEN '待安排审核'
        WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time) is null THEN 'MI制作中'
        WHEN TRIM(a.sent_time)!='' THEN '已完成'
    END AS '订单状态',
    ( CASE 
				WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time) is null THEN (SELECT to_be_Arrange FROM TimeCriteria where 1=1 limit 1 )
				WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time) is null THEN (SELECT To_Be_IssueMaterial FROM TimeCriteria where 1=1 limit 1 )  
				WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time) is null THEN (SELECT To_Be_IssueQuery FROM TimeCriteria where 1=1 limit 1 ) 
        WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time) is null THEN (SELECT To_Be_Check FROM TimeCriteria where 1=1 limit 1 ) 
        WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time) is null THEN (SELECT PrapareMI FROM TimeCriteria where 1=1 limit 1 ) 
			END ) 标准时间,
    ( CASE 
				WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time) is null AND (NOW()- a.recieve_time - (SELECT to_be_Arrange FROM TimeCriteria where 1=1 limit 1 )*60*60)>0 THEN (NOW()- a.recieve_time - (SELECT to_be_Arrange FROM TimeCriteria where 1=1 limit 1 )*60*60)

			END ) 还剩多少时间,
    '' 已超时时间
FROM PartList a

ACMAIN_CHM 2017-09-20

打赏
举报

SELECT a.job 料号, CASE WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time)='' THEN '未安排' WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time)='' THEN '待发料' WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time)='' THEN '待发EQ' WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time)='' THEN '待安排审核' WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time)='' THEN 'MI制作中' WHEN TRIM(a.sent_time)!='' THEN '已完成' END AS '订单状态', CASE WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time)='' THEN b.'未安排' WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time)='' THEN b.'待发料' WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time)='' THEN b.'待发EQ' WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time)='' THEN b.'待安排审核' WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time)='' THEN b.'MI制作中' WHEN TRIM(a.sent_time)!='' THEN '已完成' END AS 标准时间, '' 还剩多少时间, '' 已超时时间 FROM PartList a, table2 b

leon51 2017-09-20

打赏
举报

是小时，多谢了，我真需要帮助

ITVin 2017-09-20

打赏
举报

我想问那个标准时间是分钟吗？还是小时？

leon51 2017-09-19

打赏
举报

SQL我写了一点，其余的写不出来了

SELECT 
	a.job 料号,
	CASE
		WHEN TRIM(a.recieve_time)!='' AND TRIM(a.mi_time)='' THEN '未安排'	
		WHEN TRIM(a.micheck_time)!='' AND TRIM(a.sent_time)='' THEN '待发料'		
		WHEN TRIM(a.mi_time)!='' AND TRIM(a.ask_time)='' THEN '待发EQ'
		WHEN TRIM(a.mi_finish_time)!='' AND TRIM(a.micheck_time)='' THEN '待安排审核'
		WHEN TRIM(a.answer_time)!='' AND TRIM(a.mi_finish_time)='' THEN 'MI制作中'
		WHEN TRIM(a.sent_time)!='' THEN '已完成'
	END AS '订单状态',
	'' 标准时间,
	'' 还剩多少时间,
	'' 已超时时间
FROM PartList a