数据库设计问题 – SQL_MySQL
程序员文章站
2024-01-02 12:34:10
...
要求：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