mysql - 如何将这些行连接到同一个表中的一条记录中

Question

X表的原始数据

     Date       #  Method    #   Count     #     
    ======================================
    2012-08-24 #   A        #    44       #
    2012-08-24 #   B        #    5        #
    2012-08-24 #   D        #    1        #

*p/s 一共有8种方法a,b,c,d,e,f,g,h

下面是我要显示的内容:

Date        # A  # B # C # D # E # F # G # H #
2012-08-24  # 44 # 5 # 0 # 1 # 0 # 0 # 0 # 0 #

Answer 1

试试这个:

SELECT A.Date, 
       SUM(CASE WHEN A.Method = 'A' THEN A.Count ELSE 0 END) AS 'A', 
       SUM(CASE WHEN A.Method = 'B' THEN A.Count ELSE 0 END) AS 'B', 
       SUM(CASE WHEN A.Method = 'C' THEN A.Count ELSE 0 END) AS 'C', 
       SUM(CASE WHEN A.Method = 'D' THEN A.Count ELSE 0 END) AS 'D', 
       SUM(CASE WHEN A.Method = 'E' THEN A.Count ELSE 0 END) AS 'E', 
       SUM(CASE WHEN A.Method = 'F' THEN A.Count ELSE 0 END) AS 'F', 
       SUM(CASE WHEN A.Method = 'G' THEN A.Count ELSE 0 END) AS 'G', 
       SUM(CASE WHEN A.Method = 'H' THEN A.Count ELSE 0 END) AS 'H'
FROM tableA A 
GROUP BY A.Date