# 创建表 Create Table tA ( id int ,a varchar(10) ,b varchar(10) ,c varchar(10) ,d varchar(10) ); #插入数据insert into tA select '1','a1','b1','c1','d1' union select '2','a2','b2','c2','d2' union select '3','a2','b2','c2','d2' union select '4','a5','b8','c1','d1'; # 检查数据 select * from tA; # 结果显示方式1SELECT Id, Value, Num FROM ( SELECT 'cA' `Id`, a `Value`, count(a) `Num` FROM tA GROUP BY a ORDER BY Num DESC LIMIT 20 ) tA UNION ALL SELECT Id, Value, Num FROM ( SELECT 'cB' `Id`, b `Value`, count(b) `Num` FROM tA GROUP BY b ORDER BY Num DESC LIMIT 20 ) tB UNION ALL SELECT Id, Value, Num FROM ( SELECT 'cC' `Id`, c `Value`, count(c) `Num` FROM tA GROUP BY c ORDER BY Num DESC LIMIT 20 ) tC UNION ALL SELECT Id, Value, Num FROM ( SELECT 'cD' `Id`, d `Value`, count(d) `Num` FROM tA GROUP BY d ORDER BY Num DESC LIMIT 20 ) tD; # 结果显示方式2 SET @a = 0, @b = 0, @c = 0, @d = 0; SELECT a, num, b, numb, c, numc, d, numd FROM ( SELECT tA.id, tA.a, tA.num , IF(ta.id = tb.id, tb.id, null)idb, IF(ta.id = tb.id, tb.b, null)b, IF(ta.id = tb.id, tb.num, null)numb , IF(ta.id = tc.id, tc.id, null)idc, IF(ta.id = tc.id, tc.c, null)c, IF(ta.id = tc.id, tc.num, null)numc , IF(ta.id = td.id, td.id, null)idd, IF(ta.id = td.id, td.d, null)d, IF(ta.id = td.id, td.num, null)numd FROM ( SELECT @a := @a + 1 `Id`, a, count(a) `Num` FROM tA GROUP BY a ORDER BY Num DESC LIMIT 20 ) tA JOIN ( SELECT @b := @b + 1 `Id`, b, count(b) `Num` FROM tA GROUP BY b ORDER BY Num DESC LIMIT 20 ) tB JOIN ( SELECT @c := @c + 1 `Id`, c, count(c) `Num` FROM tA GROUP BY c ORDER BY Num DESC LIMIT 20 ) tC JOIN ( SELECT @d := @d + 1 `Id`, d, count(d) `Num` FROM tA GROUP BY d ORDER BY Num DESC LIMIT 20 ) tD ORDER BY 1 DESC, 4 DESC, 7 DESC, 10 DESC ) tt group by id;# 删除测试表 Drop Table tA;
我用你提供的表做了如下一个测试不知道是不是你想要的?只不过是把我第一个回答的再用union连接起来而已. mysql> create table de(id int, a varchar(10),b varchar(10),c varchar(10), -> d varchar(10)); Query OK, 0 rows affected (0.09 sec)mysql> insert into de values(1,'a1','b1','c1','d1'); Query OK, 1 row affected (0.00 sec)mysql> insert into de values(2,'a2','b2','c2','d2'); Query OK, 1 row affected (0.00 sec)mysql> insert into de values(3,'a2','b2','c2','d2'); Query OK, 1 row affected (0.00 sec)mysql> insert into de values(4,'a5','b8','c1','d1'); Query OK, 1 row affected (0.00 sec)mysql> select * from de; +------+------+------+------+------+ | id | a | b | c | d | +------+------+------+------+------+ | 1 | a1 | b1 | c1 | d1 | | 2 | a2 | b2 | c2 | d2 | | 3 | a2 | b2 | c2 | d2 | | 4 | a5 | b8 | c1 | d1 | +------+------+------+------+------+ 4 rows in set (0.00 sec)mysql> select a,count(*) from de group by a limit 10; +------+----------+ | a | count(*) | +------+----------+ | a1 | 1 | | a2 | 2 | | a5 | 1 | +------+----------+ 3 rows in set (0.00 sec)mysql> select a,count(*) from de group by a limit 10 union -> select b,count(*) from de group by b limit 10 union -> select c,count(*) from de group by c limit 10 union -> select d,count(*) from de group by d limit 10; +------+----------+ | a | count(*) | +------+----------+ | a1 | 1 | | a2 | 2 | | a5 | 1 | | b1 | 1 | | b2 | 2 | | b8 | 1 | | c1 | 2 | | c2 | 2 | | d1 | 2 | | d2 | 2 | +------+----------+ 10 rows in set (0.01 sec)
# 创建表
Create Table tA (
id int
,a varchar(10)
,b varchar(10)
,c varchar(10)
,d varchar(10)
);
#插入数据insert into tA
select '1','a1','b1','c1','d1'
union
select '2','a2','b2','c2','d2'
union
select '3','a2','b2','c2','d2'
union
select '4','a5','b8','c1','d1';
# 检查数据
select * from tA;
# 结果显示方式1SELECT Id, Value, Num FROM
(
SELECT 'cA' `Id`, a `Value`, count(a) `Num` FROM tA GROUP BY a ORDER BY Num DESC LIMIT 20
) tA
UNION ALL
SELECT Id, Value, Num FROM
(
SELECT 'cB' `Id`, b `Value`, count(b) `Num` FROM tA GROUP BY b ORDER BY Num DESC LIMIT 20
) tB
UNION ALL
SELECT Id, Value, Num FROM
(
SELECT 'cC' `Id`, c `Value`, count(c) `Num` FROM tA GROUP BY c ORDER BY Num DESC LIMIT 20
) tC
UNION ALL
SELECT Id, Value, Num FROM
(
SELECT 'cD' `Id`, d `Value`, count(d) `Num` FROM tA GROUP BY d ORDER BY Num DESC LIMIT 20
) tD;
# 结果显示方式2
SET @a = 0, @b = 0, @c = 0, @d = 0;
SELECT a, num, b, numb, c, numc, d, numd FROM
(
SELECT
tA.id, tA.a, tA.num
, IF(ta.id = tb.id, tb.id, null)idb, IF(ta.id = tb.id, tb.b, null)b, IF(ta.id = tb.id, tb.num, null)numb
, IF(ta.id = tc.id, tc.id, null)idc, IF(ta.id = tc.id, tc.c, null)c, IF(ta.id = tc.id, tc.num, null)numc
, IF(ta.id = td.id, td.id, null)idd, IF(ta.id = td.id, td.d, null)d, IF(ta.id = td.id, td.num, null)numd
FROM
(
SELECT @a := @a + 1 `Id`, a, count(a) `Num` FROM tA GROUP BY a ORDER BY Num DESC LIMIT 20
) tA
JOIN
(
SELECT @b := @b + 1 `Id`, b, count(b) `Num` FROM tA GROUP BY b ORDER BY Num DESC LIMIT 20
) tB
JOIN
(
SELECT @c := @c + 1 `Id`, c, count(c) `Num` FROM tA GROUP BY c ORDER BY Num DESC LIMIT 20
) tC
JOIN
(
SELECT @d := @d + 1 `Id`, d, count(d) `Num` FROM tA GROUP BY d ORDER BY Num DESC LIMIT 20
) tD
ORDER BY 1 DESC, 4 DESC, 7 DESC, 10 DESC
) tt
group by id;# 删除测试表
Drop Table tA;
mysql> create table de(id int, a varchar(10),b varchar(10),c varchar(10),
-> d varchar(10));
Query OK, 0 rows affected (0.09 sec)mysql> insert into de values(1,'a1','b1','c1','d1');
Query OK, 1 row affected (0.00 sec)mysql> insert into de values(2,'a2','b2','c2','d2');
Query OK, 1 row affected (0.00 sec)mysql> insert into de values(3,'a2','b2','c2','d2');
Query OK, 1 row affected (0.00 sec)mysql> insert into de values(4,'a5','b8','c1','d1');
Query OK, 1 row affected (0.00 sec)mysql> select * from de;
+------+------+------+------+------+
| id | a | b | c | d |
+------+------+------+------+------+
| 1 | a1 | b1 | c1 | d1 |
| 2 | a2 | b2 | c2 | d2 |
| 3 | a2 | b2 | c2 | d2 |
| 4 | a5 | b8 | c1 | d1 |
+------+------+------+------+------+
4 rows in set (0.00 sec)mysql> select a,count(*) from de group by a limit 10;
+------+----------+
| a | count(*) |
+------+----------+
| a1 | 1 |
| a2 | 2 |
| a5 | 1 |
+------+----------+
3 rows in set (0.00 sec)mysql> select a,count(*) from de group by a limit 10 union
-> select b,count(*) from de group by b limit 10 union
-> select c,count(*) from de group by c limit 10 union
-> select d,count(*) from de group by d limit 10;
+------+----------+
| a | count(*) |
+------+----------+
| a1 | 1 |
| a2 | 2 |
| a5 | 1 |
| b1 | 1 |
| b2 | 2 |
| b8 | 1 |
| c1 | 2 |
| c2 | 2 |
| d1 | 2 |
| d2 | 2 |
+------+----------+
10 rows in set (0.01 sec)