数据库设计问题 – SQL_MySQL

要求：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)

于是，我们得到了结果；

NT80 购物系统

功能说明：1 会员可申请开店功能2 购买在线扣除金额3 冲值卡自动生成4 支持2级分类5 数据库压缩和备份6 会员分5个级别7 商品带讨论8 自带融合论坛，可关闭打开9 密码找回功能10 新闻``滚动新闻``帮助中心11 后台设置前台会员的上传权限12 可关闭/打开商店13 会员自助发布商品功能14 用户问题咨询管理

下载

-------

补充一些基础知识：

如何修改已有表的列：http://www.w3school.com.cn/sql/sql_alter.asp

GROUP BY 相关知识：http://www.w3school.com.cn/sql/sql_groupby.asp