我想在mysql查询中处理连接列上的条件或大小写

问题描述

select m.Main_Test_Name,sum(datediff(s.job_submit_date,s.job_assign_date))/count(s.Sample_Details_Id) as averagetimetocompleteTest from main_test m inner join sample_details s on m.Main_Test_Id=s.Main_Test_Id group by m.Main_Test_Id;

我的查询工作正常（但在某些情况下我有插入日期

my query is working fine (but in some cases i have insert date of

job_assign_date

为

'0001-01-01 00:00:00'

同样适用于

same for

job_submit_date '0001-01-01 00:00:00'

但是当这些日期是

but when thes dates are

'0001-01-01 00:00:00'

然后我想把它设为null



但是它给我错误



我尝试了什么：



我试过了



为1）

then i want set it as null

but it gives me error

What I have tried:

I have tried

as 1)

select m.Main_Test_Name,sum(datediff(s.job_submit_date , case job_submit_date when '0001-01-01 00:00:00' then null else s.job_submit_date,s.job_assign_date))/count(s.Sample_Details_Id) as averagetimetocompleteTest from main_test m inner join sample_details s on m.Main_Test_Id=s.Main_Test_Id group by m.Main_Test_Id;";

2）

2)

select m.Main_Test_Name,sum(datediff(s.job_submit_date,if(s.job_assign_date='0001-01-01 00:00:00',null,s.job_assign_date) s.job_assign_date)/count(s.Sample_Details_Id) as averagetimetocompleteTest from main_test m inner join sample_details s on m.Main_Test_Id=s.Main_Test_Id group by m.Main_Test_Id;

推荐答案

NULLIF

Returns a null value if the two specified expressions are equal. For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. The second column returns the first value (5) because the two input values are different.

Syntax


NULLIF ( expression , expression )  

这篇关于我想在mysql查询中处理连接列上的条件或大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！