sql的case when；以及在thinkphp中的使用

SELECT
count(case when shenfenid like "11%" then "北京" end)北京,
count(case when shenfenid like "12%" then "天津" end)天津,
count(case when shenfenid like "13%" then "河北" end)河北,
count(case when shenfenid like "14%" then "北京" end)山西,
count(case when shenfenid like "15%" then "内蒙古" end)内蒙古,
count(case when shenfenid like "41%" then "河南" end)河南
FROM lp_lianpiao

 

$result = M('lianpiao')->field('count(case when shenfenid like "11%" then "北京" end)beijing,
count(case when shenfenid like "12%" then "天津" end)tianjin,
count(case when shenfenid like "13%" then "河北" end)hebei,
count(case when shenfenid like "14%" then "山西" end)shanxi1,
count(case when shenfenid like "15%" then "内蒙古" end)neimenggu,
count(case when shenfenid like "21%" then "辽宁" end)liaoning,
count(case when shenfenid like "22%" then "吉林" end)jilin,
count(case when shenfenid like "23%" then "黑龙江" end)heilongjiang,
count(case when shenfenid like "31%" then "上海" end)shanghai,
count(case when shenfenid like "32%" then "江苏" end)jiangsu,
count(case when shenfenid like "33%" then "浙江" end)zhejiang,
count(case when shenfenid like "34%" then "安徽" end)anhui,
count(case when shenfenid like "35%" then "福建" end)fujian,
count(case when shenfenid like "36%" then "江西" end)jiangxi,
count(case when shenfenid like "37%" then "山东" end)shandong,
count(case when shenfenid like "41%" then "河南" end)henan,
count(case when shenfenid like "42%" then "湖北" end)hubei,
count(case when shenfenid like "43%" then "湖南" end)hunan,
count(case when shenfenid like "44%" then "广东" end)guangdong,
count(case when shenfenid like "45%" then "广西" end)guangxi,
count(case when shenfenid like "46%" then "海南" end)hainan,
count(case when shenfenid like "50%" then "重庆" end)chongqing,
count(case when shenfenid like "51%" then "四川" end)sichuan,
count(case when shenfenid like "52%" then "贵州" end)guizhou,
count(case when shenfenid like "53%" then "云南" end)yunnan,
count(case when shenfenid like "54%" then "西藏" end)xizang,
count(case when shenfenid like "61%" then "陕西" end)shanxi3,
count(case when shenfenid like "62%" then "甘肃" end)gansu,
count(case when shenfenid like "63%" then "青海" end)qinghai,
count(case when shenfenid like "64%" then "宁夏" end)ningxia,
count(case when shenfenid like "65%" then "新疆" end)xinjiang')->select();