代码之家 › 专栏 › 技术社区 › Pattatharasu Nataraj burakarasu

在mysql中得到明显的结果,条件很少[重复]

mysql php

0

Pattatharasu Nataraj burakarasu · 技术社区 · 7 年前

这个问题已经有了答案:

MySQL pivot table 8答

我有一些样品数据

 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,任何建议都会受到赞赏。

1 回复 | 直到 7 年前

1

Adrian Maxwell 7 年前

您可以使用“条件聚合”来“透视”您的数据。基本上,这意味着在聚合函数中放置一个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