mysql - 添加 header 以跨连接查询

Question

到目前为止，我有以下内容:

   SELECT D.department AS dept, C.name AS subdept
    FROM (SELECT DISTINCT department FROM classes WHERE web != 0 ORDER BY department,name LIMIT 5) D
    LEFT JOIN classes C ON (C.department = D.department)
    ORDER BY D.department,C.name

结果如下:

+------+-------------------------------+
| dept | subdept                       |
+------+-------------------------------+
| BOOK | CHILDRENS BOOKS               |
| BOOK | DVD'S                         |
| CLOT | ACCESSORIES                   |
| CLOT | APRONS                        |
| FEED | BIBS & BURP CLOTHS            |
| FEED | BOTTLE & FOOD WARMERS         |
+------+-------------------------------+

我试图获得的是每个部门的“标题”，其子部门值为空，例如:

+------+-------------------------------+
| dept | subdept                       |
+------+-------------------------------+
| BOOK | null                          |
| BOOK | CHILDRENS BOOKS               |
| BOOK | DVD'S                         |
| CLOT | null                          |    
| CLOT | ACCESSORIES                   |
| CLOT | APRONS                        |
| FEED | null                          |    
| FEED | BIBS & BURP CLOTHS            |
| FEED | BOTTLE & FOOD WARMERS         |
+------+-------------------------------+

表的结构:departments表有id主键到classes department字段作为外键。

部门:id |名称(部门)类:部门|名称(类)

根据 DanfromGermany 向我展示的内容，我有:

SELECT D.department AS dept, C.name AS subdept
FROM
 (SELECT DISTINCT department FROM classes WHERE web != 0 ORDER BY department,name  LIMIT 5) D
LEFT JOIN classes C ON (C.department = D.department)
GROUP BY D.department, C.name WITH ROLLUP

现在给出:

+--------+-------------------------------+
| dept   | subdept                       |
+--------+-------------------------------+
| BOOK   | CHILDRENS BOOKS               |
| BOOK   | DVD'S                         |
| BOOK   | [NULL]                        |
| CLOT   | ACCESSORIES                   |
| CLOT   | APRONS                        |
| CLOT   | [NULL]                        |
| FEED   | BIBS & BURP CLOTHS            |
| FEED   | BOTTLE & FOOD WARMERS         |
| FEED   | [NULL]                        |
| GEAR   | BOOSTER CAR SEATS             |
| GEAR   | CAR SEAT ACCESSORIES          |
| GEAR   | [NULL]                        |
| GIFT   | BABY BASKETS & DIAPER CAKES   |
| GIFT   | BANKS                         |
| GIFT   | [NULL]                        |
| [NULL] | [NULL]                        |
+--------+-------------------------------+

确定上次编辑:

它通过子查询来使用 order by:

SELECT * FROM
(SELECT D.department AS dept, C.name AS subdept
FROM
 (SELECT DISTINCT department FROM classes WHERE web != 0 ORDER BY department,name  LIMIT 5) D
LEFT JOIN classes C ON (C.department = D.department)
GROUP BY D.department, C.name WITH ROLLUP
) T
ORDER BY dept,subdept

Answer 1

将您的查询更改为具有 GROUP BY 子句，然后使用 WITH ROLLUP。

请参阅该页面的中间: http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html

或谷歌搜索“GROUP BY WITH ROLLUP mysql”

像这样(未经测试):

SELECT D.department AS dept, C.name AS subdept
FROM
 (SELECT DISTINCT department FROM classes WHERE web != 0 ORDER BY department,name  LIMIT 5) D
LEFT JOIN classes C ON (C.department = D.department)
GROUP BY dept, subdept WITH ROLLUP
ORDER BY D.department,C.name