时间戳

首页 > 解决方案 > 在mysql中获得更多条件的不同结果

php - 在mysql中获得更多条件的不同结果

问题描述

海我有一些样本数据

 bookId     | bookPnr       | bookDate   | bookFullName | bookMobile | bookEmail         | bookSource
 9876543210 | BPT1100000000 | 2018-11-18 | User 1       | 9876543210 | test@gmail.com    | Redbus
 9876543211 | BPT1100000001 | 2017-11-18 | User 2       | 9876543211 | testOne@gmail.com | Redbus
 9876543212 | BPT1100000002 | 2017-11-18 | User 3       | 9876543214 | testtwo@gmail.com | TicketGoose

我需要一个类似的结果

Mobile      | 2018 | 2017 | 2016 | Redbus | TicketGoose | total

9876543210  |  2   | 3    | 6    | 2      | 2           | 11
9876543211  |  1   | 1    | 1    | 2      | 1           | 3

所以我需要基于年份和来源的不同手机号码，我确实查询过类似的东西，

SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info` 
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC

是否可以通过单个查询来完成，或者我们必须使用 PHP 任何建议将不胜感激。

标签： phpmysql

解决方案

您可以使用“条件聚合”来“透视”您的数据。基本上，这意味着在聚合函数中放置一个 case 表达式。这里我使用了 COUNT()：

SELECT
    bookMobile
  , count(case when year(bookDate) = 2016 then 1 end) as `2016`
  , count(case when year(bookDate) = 2017 then 1 end) as `2017`
  , count(case when year(bookDate) = 2018 then 1 end) as `2018`
  , count(case when bookSource = 'Redbus' then 1 end) as Redbus
  , count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
    bookMobile
ORDER BY
    bookMobile DESC

推荐阅读