sql : case when使用

1.选择不同的判断条件：如果payTime相等就判断millisecond ，否则直接判断payTime

select  *  from  web_1rmb_snatch_pay_log   where  

case  when  payTime = '2016-06-02 10:33:47'  then  millisecond  <=  '562'  else   payTime < '2016-06-02 10:33:47'  end

order by payTime desc, millisecond desc limit 0,49

2.

select   animal0_.id as id0_, 

 animal0_.name as name0_,

 animal0_.zoo_id as zoo3_0_, 

animal0_1_.age as age1_, 

animal0_2_.kind as kind2_, 

case when animal0_1_.animal_id is not null  then 1 

when animal0_2_.animal_id is not null then 2 

when animal0_.id is not nullthen0end as clazz_ from t_animal animal0_

 left outer join  

  t_tiger animal0_1_    on   

animal0_.id=animal0_1_.animal_id    

 left outer  join t_monkey    animal0_2_   

on animal0_.id=animal0_2_.animal_id where animal0_.zoo_id=1

3. hql:

select staff.staffId, count(record.warningDate) from WarningRecord record,Staff staff
 where record.warningTrans.staffId = staff.staffId and staff.monitor = 1 and staff.deleted = 0 
and record.warningDate <=:toDate 
and record.warningDate >=(case when staff.mdDelTime>:fromDate then staff.mdDelTime else :fromDate end) group by staff")

4.怎么把这样一个表(转的)
year months amount
1991  1     1.1
1991  2     1.2
1991  3     1.3
1991  4     1.4
1992  1     2.1
1992  2     2.2
1992  3     2.3
1992  4     2.4
查成这样一个结果
year  m1    m2   m3   m4
1991  1.1   1.2  1.3  1.4
1992  2.1   2.2  2.3  2.4

a.建表：

create table sales(
 years int,
 months int,
 amount float
)
insert into sales values
(1991,1,1.1),
(1991,2,1.2),
(1991,3,1.3),
(1991,4,1.4),
(1992,1,2.1),
(1992,2,2.2),
(1992,3,2.3),
(1992,4,2.4)

b.转换：

语句：
(A).select a.years,
(select m.amount from sales m where months = 1 and m.years = a.years) as m1,
(select m.amount from sales m where months = 2 and m.years = a.years) as m2,
(select m.amount from sales m where months = 3 and m.years = a.years) as m3,
(select m.amount from sales m where months = 4 and m.years = a.years) as m4
from sales a group by a.years
(B).select a.years,
sum(case months when 1 then amount else 0 end) as m1,
sum(case months when 2 then amount else 0 end) as m2,
sum(case months when 3 then amount else 0 end) as m3,
sum(case months when 4 then amount else 0 end) as m4
from sales a group by a.years