im试图从sub Table applyment_types 获得与主表 hrms_emp_type 相关的值。子表记录可以是多个,但是在获得结果时,应该这样:
salaryProcessID | description
1360 | General
1397 | Consultant
1557 | General, Trainee, Consultant
1566 | General, Trainee
链接:
http://sqlfiddle.com/#!9/e1645/1
SELECT salaryProcessID,
GROUP_CONCAT(description ORDER BY empType SEPARATOR ', ') AS description
FROM hrms_emp_type AS spTB
JOIN employment_types AS empType ON empType.id = spTB.empType
GROUP BY salaryProcessID
ORDER BY salaryProcessID;
输出
salaryProcessID description
1360 General
1397 Consultant
1557 General, Trainee, Consultant
1566 General, Trainee
演示
http://sqlfiddle.com/#!9/e1645/24
group_concat((函数
mysql group_concat((函数返回一个带有串联的字符串 来自组的非零值。
语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
您可以使用GROUP_CONCAT
SELECT
salaryProcessID,
GROUP_CONCAT(description) descr_list1,
GROUP_CONCAT(description ORDER BY description SEPARATOR '; ') descr_list2
FROM hrms_emp_type AS spTB
JOIN employment_types AS empType ON empType.id = spTB.empType
GROUP BY salaryProcessID
ORDER BY salaryProcessID
sql小提琴-http://sqlfiddle.com/#!9/e1645/16