一个复杂的SQL拼接问题, 请教

Question

表结构如下:id | count | number | type   
 0 10 123 t1
 1 8 123 t2  
 2 7 888 t3
 3 3 444 t1
 4 5 111 t1
 5 9 012 t2
 6 1 222 t2
 7 22 666 t3
 8 2 214 t2想达到的效果:   t1_number|t1_count|t2_number|t2_count|t3_number|t3_count
  123    | 10     | 012     | 9      | 666     | 22
  111    | 5      | 123     | 8      | 888     | 7
  444    | 3      | 214     | 2      | null    | null
  222    | 1      | null    | null   | null    | null所有值按count排序

Answer 1

SQL codemysql> SELECT MAX(IF(TYPE='t1',number,'')) AS t1_number,MAX(IF(TYPE='t1',COUNT,'
')) AS t1_count,
    -> MAX(IF(TYPE='t2',number,'')) AS t2_number,MAX(IF(TYPE='t2',COUNT,'')) AS
t2_count,
    -> MAX(IF(TYPE='t3',number,'')) AS t3_number,MAX(IF(TYPE='t3',COUNT,'')) AS
t3_count
    -> FROM (
    -> SELECT a.count,a.id,a.number,a.type,COUNT(b.id) AS gs FROM tth5 a LEFT JO
IN tth5 b ON a.type=b.type AND a.count<=b.count
    -> GROUP BY a.count,a.id,a.number,a.type
    -> ) aa GROUP BY gs
    -> ;
+-----------+----------+-----------+----------+-----------+----------+
| t1_number | t1_count | t2_number | t2_count | t3_number | t3_count |
+-----------+----------+-----------+----------+-----------+----------+
| 123       | 10       | 12        | 9        | 666       | 22       |
| 111       | 5        | 123       | 8        | 888       | 7        |
| 444       | 3        | 214       | 2        |           |          |
|           |          | 222       | 1        |           |          |
+-----------+----------+-----------+----------+-----------+----------+
4 rows in set (0.01 sec)mysql>