Today helped the friend to adjust a website, turned sp accidentally, saw a section of very long select sentence, the reason that this select sentence was long, was because had several general complex case sentences to follow in select behind.We select a field the logical rule and the data make our test data:
create table tbl (id int, type_a int)
insert into tbl values (1000,1000)
insert into tbl values (999,999)
insert into tbl values (998,998)
insert into tbl values (997,997)
insert into tbl values (996,996)
insert into tbl values (995,null)
insert into tbl values (994,null)
insert into tbl values (993,null)
insert into tbl values (992,null)
insert into tbl values (991,null)
OK, this SQL sentence has many kinds of mode of writing, friend's sql is certainly writes like this:
select
case
when (type_a is null or type_a=997) then 0
else type_a
end as type_a
from tbl
If needs to control the field more than one, that this prompt already used select which shrinks also to look like has been very complex, the time long has wanted to modify this sp the logic somewhat to be strenuous, we when make the plan will frequently say that “a half hour will do decides this question”, but often will do time could surpass this time, the reason lies in us always to have from a group mess finds obtains the spot.The complex code and logic often solve the bone which in the question difficult to gnaw.What good means then has to optimize?
select coalesce(nullif(type_a,997),0) as type_a from tbl
Well, above wrote 6 line of sql to substitute by these 1 line of institutes.
nullif accepts two parameters, if two parameters are equal, then returns to null, otherwise returns to the first parameter
coalesce accepts N parameter, returns to first is not the null parameter
So, when you meet process computation wages question time which as follows shows, might as well like this solves:
create table salary (e_id uniqueidentifier, byMonth int, byHalfYear int, byYear int)
insert into salary values (newid(),9000,null,null)
insert into salary values (newid(),null,60000,null)
insert into salary values (newid(),null,null,150000)
Each employee has 3 salary computing mode (monthly, according to a half year, provides the wages yearly), if we want to count each staff's yearly salary, that such has sufficed:
select e_id,coalesce(byMonth*12,byHalfYear*2,byYear) as salary_amount from salary
Finally:
e_id salary_amount
------------------------------------ -------------
8935330D-2B73-4FEF-941A-768D7A8CCB6C 108000
52A3CE16-74FD-4D5D-BB4F-F5F67A1E9D2F 120000
06B6B924-EAB2-4187-B733-EBB56B62E793 150000