要求：a 表：`id`, `name` ; 作为词表，存放不同的词；b 表：`id`, `attr` ; 作为属性表，存放各种属性；其中，一个词可以有不同的多个属性；而每个词的属性的个数也不一定相同；c 表：`id`, `aid`, `bid` ; 作为关系表，存放每个词的对应关系；写出 sql 语句，来得到每个词拥有属性总数的逆向（desc）排序：
各种表的信息如下：
mysql> desc `a`; desc `b`; desc `c`;+-------+---------------------+------+-----+---------+----------------+| field | type | null | key | default | extra |+-------+---------------------+------+-----+---------+----------------+| id | bigint(20) unsigned | no | pri | null | auto_increment || name | varchar(255) | no | | null | |+-------+---------------------+------+-----+---------+----------------+2 rows in set (0.00 sec)+-------+---------------------+------+-----+---------+----------------+| field | type | null | key | default | extra |+-------+---------------------+------+-----+---------+----------------+| id | bigint(20) unsigned | no | pri | null | auto_increment || attr | varchar(255) | no | | null | |+-------+---------------------+------+-----+---------+----------------+2 rows in set (0.01 sec)+-------+---------------------+------+-----+---------+----------------+| field | type | null | key | default | extra |+-------+---------------------+------+-----+---------+----------------+| id | bigint(20) unsigned | no | pri | null | auto_increment || aid | int(8) | no | | null | || bid | int(8) | no | | null | |+-------+---------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)
我们预先放入测试的数据，如下：
mysql> select * from `a`; select * from `b`; select * from `c`;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |+----+------+5 rows in set (0.00 sec)+----+------+| id | attr |+----+------+| 1 | 111 || 2 | 112 || 3 | 113 || 4 | 123 || 5 | 221 || 6 | 231 || 7 | 252 || 8 | 278 || 9 | 292 || 10 | 256 || 11 | 578 || 12 | 653 || 13 | 521 || 14 | 502 |+----+------+14 rows in set (0.00 sec)+----+-----+-----+| id | aid | bid |+----+-----+-----+| 1 | 1 | 1 || 2 | 1 | 2 || 3 | 1 | 4 || 4 | 1 | 7 || 5 | 2 | 8 || 6 | 2 | 11 || 7 | 3 | 3 || 8 | 3 | 5 || 9 | 3 | 6 || 10 | 4 | 9 || 11 | 4 | 10 || 12 | 5 | 12 || 13 | 5 | 13 || 14 | 5 | 14 |+----+-----+-----+14 rows in set (0.00 sec)
首先执行下列语句：
mysql> select count(`bid`) as `attrcounts` from `c` group by `aid` order by `attrcounts` desc;+------------+| attrcounts |+------------+| 4 || 3 || 3 || 2 || 2 |+------------+5 rows in set (0.00 sec)
进而，我们再连表：
mysql> select a.name, count(c.bid) as `attrcounts` from `c` left join `a` on a.id = c.aid group by c.aid order by `attrcounts` desc;+------+------------+| name | attrcounts |+------+------------+| a | 4 || c | 3 || e | 3 || b | 2 || d | 2 |+------+------------+5 rows in set (0.00 sec)
于是，我们得到了结果；
-------
补充一些基础知识：
如何修改已有表的列：http://www.w3school.com.cn/sql/sql_alter.asp
group by 相关知识：http://www.w3school.com.cn/sql/sql_groupby.asp