改个小代码，谁能支个招。。。

Question

现在所有部门的补贴金额都是乘7的，小弟现在想把depart_id的开头为18的部门补贴金额变成乘8，而其它部门不变仍然是乘7的，应该如何改？谢谢
select 
  
  departs.depart_name '部门名称',
  sum(mealrecords.times_money) '补贴次数',
  sum(mealrecords.times_money)*7 '补贴金额'from 
  mealrecords,
  departs,
  employee
where 
  employee.emp_id=mealrecords.emp_id and
  departs.depart_id= employee.depart_id and
  mealrecords.flag='5' and
  convert(varchar(7),mealrecords.sign_time,120)=
  substring( :补贴月份 ,1,4)+'-'+substring( :补贴月份 ,5,2)
group by departs.depart_name,departs.depart_id
order by departs.depart_id

Answer 1

'补贴金额' =case depart_id 
when depart_id=18
         then sum(mealrecords.times_money)*8 '
else
         sum(mealrecords.times_money)*7  
end

Answer 2

[Quote=引用 2 楼 fei80317 的回复:]不是depart_id=18  应该是用LIKE '18%'  请问这个like 条件加什么地方呢貌似加select不太行？

Answer 3

'补贴金额' =case 
when depart_id like '18%'
         then sum(mealrecords.times_money)*8 
else
         sum(mealrecords.times_money)*7  
end

Answer 4

case when depart_id like '18%' then sum(mealrecords.times_money)*8  ELSE sum(mealrecords.times_money)*7 END AS '补贴金额'

Answer 5

select  
   
  departs.depart_name '部门名称',
  sum(mealrecords.times_money) '补贴次数',
  sum(mealrecords.times_money)*7 '补贴金额'
,sum(case when convert(varchar(7),mealrecords.sign_time,120)=
  substring( :补贴月份 ,1,4)+'-'+substring( :补贴月份 ,5,2) and ltrim(depart_id) like '18%'
then mealrecords.times_money else 0 end)*8 '你要的补贴金额'from  
  mealrecords,
  departs,
  employee
where  
  employee.emp_id=mealrecords.emp_id and
  departs.depart_id= employee.depart_id and
  mealrecords.flag='5' and
  convert(varchar(7),mealrecords.sign_time,120)=
  substring( :补贴月份 ,1,4)+'-'+substring( :补贴月份 ,5,2)
group by departs.depart_name,departs.depart_id
order by departs.depart_id