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 &#39;经济舱&#39; then 1 else null end) as ec_count,COUNT(CASE class_type WHEN &#39;头等舱&#39; then 1 else null end) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = &#39;宁波市&#39; AND `to_city` = &#39;北京市&#39; AND `flight_date` = &#39;2017-4-1&#39; 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 = &#39;经济舱&#39; THEN ticket_price ELSE 0 END) as ec_price, SUM(CASE WHEN class_type = &#39;头等舱&#39; THEN ticket_price ELSE 0 END) as fc_price, COUNT(CASE class_type WHEN &#39;经济舱&#39; then 1 else null end) as ec_count,COUNT(CASE class_type WHEN &#39;头等舱&#39; then 1 else null end) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = &#39;宁波市&#39; AND `to_city` = &#39;北京市&#39; AND `flight_date` = &#39;2017-4-1&#39; GROUP BY flight

这样就能求出对应座位价格的SUM和，在去/票数(ec_count,fc_count)，就能能到单价。可惜直接除以别名 sql语法会报错难道用

SELECT *,COUNT(CASE class_type WHEN &#39;经济舱&#39; then 1 else null end) as ec_count, COUNT(CASE class_type WHEN &#39;头等舱&#39; then 1 else null end) as fc_count, (SUM(CASE WHEN class_type = &#39;经济舱&#39; THEN ticket_price ELSE 0 END)/COUNT(CASE class_type WHEN &#39;经济舱&#39; then 1 else null end)) as ec_price, (SUM(CASE WHEN class_type = &#39;头等舱&#39; THEN ticket_price ELSE 0 END)/COUNT(CASE class_type WHEN &#39;头等舱&#39; then 1 else null end)) as fc_price) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = &#39;宁波市&#39; AND `to_city` = &#39;北京市&#39; AND `flight_date` = &#39;2017-4-1&#39; GROUP BY flight

看着就好长，不过想到用SUM,自然想到用AVG函数了 duangduangduang! 最终结果

SELECT *,COUNT(CASE class_type WHEN &#39;经济舱&#39; then 1 else null end) as ec_count, COUNT(CASE class_type WHEN &#39;头等舱&#39; then 1 else null end) as fc_count, AVG(CASE WHEN class_type = &#39;经济舱&#39; THEN ticket_price END)as ec_price, AVG(CASE WHEN class_type = &#39;头等舱&#39; THEN ticket_price END) as fc_price) as fc_count FROM `airticket` WHERE `flight_type` = 0 AND `from_city` = &#39;宁波市&#39; AND `to_city` = &#39;北京市&#39; AND `flight_date` = &#39;2017-4-1&#39; GROUP BY flight

count case when (仿携程 机票查询)

相关推荐

count case when (仿携程机票查询)