select A.number,A.count,B.number,B.count,C.number,C.count from tb A,tb B,tb C where A.id+1=B.id and B.id+1=C.id and A.id%3=0 and B.id%3=1 and C.id%3=2 order by A.count
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 JOIN 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 222应该为T2吧
mysql> 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>
from tb A,tb B,tb C
where A.id+1=B.id and B.id+1=C.id and A.id%3=0 and B.id%3=1 and C.id%3=2
order by A.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 JOIN 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
222应该为T2吧
mysql> 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>
MAX(IF(TYPE='t1',number,'')) AS t1_number,MAX(IF(TYPE='t1',COUNT,'
')) AS t1_count,
中的T1部份