count case when (仿携程 机票查询)
感慨一句，现在写的sql语句自己是越来越看不懂了 。。。囧
使用场景：最近做毕设的时候碰见这么一个场景（仿携程网）
先看大的，按航班号查询出3条数据。这个好弄 group by(航班)就行。点击订票，跳出两个栏目，经济舱和头等舱分别带余票数。查航班余票数好解决，条件那加 count(航班)就行。难点在于怎么得到对应航班下的经济舱和头等舱的各自的余票数？
[blockquote]
想法一：
[/blockquote]
一句sql搞定 有点复杂的样子，不会啊
[blockquote]
想法二：
[/blockquote]
先获取航班数，再在点击订票的时候，ajax异步得到对应航班的class_type(舱位类型)。当去携程上F12了一下，发现点击的时候浏览器并未发送请求，而且这种做法查询次数太多，大大加重了数据库的负担（不可取 放弃）
[blockquote]
想法三：
[/blockquote]
还是sql语句解决 查询余票的count(航班)提示了我...think 可不可以 count(class_type='经济舱')这种。于是百度 sql count能不能加条件 查到结果 http://bbs.pinggu.org/thread-4931381-1-1.html http://bbs.csdn.net/topics/390701551 结合以上两条结果得到解决(mysql)
SELECT *,COUNT(CASE class_type WHEN '经济舱' then 1 else null end) as ec_count,COUNT(CASE class_type WHEN '头等舱' then 1 else null end) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = '宁波市' AND `to_city` = '北京市' AND `flight_date` = '2017-4-1' GROUP BY flight
[blockquote]
那么问题来了，这样查询结果少了每种舱位对应的价格，它只是按航班分组了。
[/blockquote]
想法一： 在group by 后面加上class_type。价格是有了，但是会多出一个一样的航班(因为舱位分ec[经济舱]和fc[头等舱])。但是可以在得到数据后循环整合，if(res[1].航班号==res[2].航班号) 就合并。。。想想就很烦的样子 想法二： select字句查询select...class_type='经济舱' ...as ec_price。感觉好烦。。。囧 想法三： 学票数一样,用用新接触case when。但是怎么球呢？ 可以试着用sum函数
SELECT *,SUM(CASE WHEN class_type = '经济舱' THEN ticket_price ELSE 0 END) as ec_price, SUM(CASE WHEN class_type = '头等舱' THEN ticket_price ELSE 0 END) as fc_price, COUNT(CASE class_type WHEN '经济舱' then 1 else null end) as ec_count,COUNT(CASE class_type WHEN '头等舱' then 1 else null end) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = '宁波市' AND `to_city` = '北京市' AND `flight_date` = '2017-4-1' GROUP BY flight
这样就能求出对应座位价格的SUM和，在去/票数(ec_count,fc_count)，就能能到单价。可惜直接除以别名 sql语法会报错 难道用
SELECT *,COUNT(CASE class_type WHEN '经济舱' then 1 else null end) as ec_count, COUNT(CASE class_type WHEN '头等舱' then 1 else null end) as fc_count, (SUM(CASE WHEN class_type = '经济舱' THEN ticket_price ELSE 0 END)/COUNT(CASE class_type WHEN '经济舱' then 1 else null end)) as ec_price, (SUM(CASE WHEN class_type = '头等舱' THEN ticket_price ELSE 0 END)/COUNT(CASE class_type WHEN '头等舱' then 1 else null end)) as fc_price) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = '宁波市' AND `to_city` = '北京市' AND `flight_date` = '2017-4-1' GROUP BY flight
看着就好长，不过想到用SUM,自然想到用AVG函数了 duangduangduang! 最终结果
SELECT *,COUNT(CASE class_type WHEN '经济舱' then 1 else null end) as ec_count, COUNT(CASE class_type WHEN '头等舱' then 1 else null end) as fc_count, AVG(CASE WHEN class_type = '经济舱' THEN ticket_price END)as ec_price, AVG(CASE WHEN class_type = '头等舱' THEN ticket_price END) as fc_price) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = '宁波市' AND `to_city` = '北京市' AND `flight_date` = '2017-4-1' GROUP BY flight
