将不同层次的记录输入到一个结果集中

【问题】

Is there a way to get one count of connected items to the parent level and one count for the child level without braking this into two query’s?

I have two (relevant) mysql tabels. Courses_regions is used to connect courses to regions.

Regions{id, name, parent_id}
Courses_regions{id, region_id, course_id}

The data that I want to display on my front-end is a list of Region, sub-region and number of items (courses) connected to the regions like so.

Parent_region_name_a ( 5 )
 Child_region_name_a (2)
 Child_region_name_b (3)
Parent_region_name_b ( 8 )
 Child_region_name_a (5)
 Child_region_name_b (3)

This query gives me the result I want, but without the count

 SELECT
t1.name AS lev1_name,
t1.id AS lev1_id,
t2.name AS lev2_name,
t2.id AS lev2_id
 FROM regions AS t1
 LEFT  JOIN regions AS t2 ON t2.parent_id = t1.id
 WHERE t2.name IS  NOT  NULL
 ORDER  BY t1.name

The result is:

 lev1_name lev1_id lev2_name lev2_id
-----------------------  -------  ---------------  ---------
Blekinge län 3Olofström 188
Blekinge län 3Karlshamn 191
Blekinge län 3Sölvesborg 192
Dalarnas län 4Vansbro 319
Test län 5Gagnef 321
Test län 5Leksand 322
Test län 5Rättvik 323

This allow me to display the parent and sub regions with one query, but I can’t figure out how to add separate Count for level 1 and level 2.

Any suggestions?

表内容如下：

Table regions:
id name parent_id
3 Blekinge 1
4 Dalarnas 2
5 Test 6
188 Olofstrom  3
191 Karlshamn 3
192 Solvesborg 3
319 Vansbro 4
321 Gagnef 5
322 Leksand 5
323 Rattvik 5
1 T1
2 T2

Table course:
id courseName
1 english
2 Chinese
3 Karlshamn
4 Vansbro
5 Blekinge Lan

Table courses_regions:
id region_id  course_id
1 3 4
2 5 3
3 3 5
4 4 2
5 188 4
6 4 1

【回答】

将三层数据按顺序拼接到一个结果集，SQL 的办法通常是把三层数据分别计算出来后再 UNION，同时为每层数据设定排序项用于最后输出成希望的次序，整个代码显得非常冗长且不直观。

这种情况下用集算器 SPL 辅助实现，代码更直观易懂：

	A
1	$select ts.id id,ts.name name,ts.parent_ID parentID,tp.name as parentName, cr.course_ID courseID,c.courseName courseName from regions ts left join regions tp on tp.id=ts.parent_ID left join courses_regions cr on cr.region_ID=ts.id join course c on c.id=cr.course_id where ts.parent_ID is not null
2	=A1.group(parentID)
3	=A2.conj([~.parentID,parentName,~.count(),1]|~.group(id).conj([id,name,~.count(),2])|~.group@s(id).conj([courseID,courseName,null,3]))
4	=create(ID,Name,Count,Levl).record(A3)

A1: 简单的 join 语句，建立 3 表关联。

A2: 对数据表按照 parentID 分组。

A3: 对序列做合并，生成最终序表要显示的记录。

A4: 创建空序表，用序列 A3 成员组成序表的新记录。

集算器是结构化数据计算工具，可以通过 JDBC 接口与报表或 JAVA 集成，很适合解决此类问题，可参考Java 如何调用 SPL 脚本。