請參閱下表和欄位。
Create an “employee” database and 4 tables (hobby, employee, employee_salary, employee_hobby).
hobby: id, name
employee: id, first_name, last_name, age, mobile_number, address
employee_salary: id, foreign key of employee, salary
employee_hobby: id, foreign key of the employee, foreign key of hobby
我按如下方式執行此查詢：
SELECT CONCAT(e.first_name, ' ', e.last_name) AS full_name, SUM(es.salary) AS total_salary,
(SELECT GROUP_CONCAT(h.name)
FROM hobby AS h
INNER JOIN hobby ON h.id = eh.fk_hobby_id) AS hobby_name
FROM employee_hobby AS eh
INNER JOIN employee AS e ON e.id = eh.fk_employee_id
INNER JOIN employee_salary AS es ON es.fk_employee_id = eh.fk_employee_id
GROUP BY eh.fk_employee_id;
但是我在單行中獲取所有相同的愛好名稱以獲取特定記錄。 輸出影像
我需要每條記錄的 hobby_name，例如運動、游戲或游戲、旅行等。
uj5u.com熱心網友回復：
這是不正確的：
...
(SELECT GROUP_CONCAT(h.name)
FROM hobby AS h
INNER JOIN hobby ON h.id = eh.fk_hobby_id) AS hobby_name
FROM employee_hobby AS eh
...
只需hobby像其他表一樣在查詢中加入表：
SELECT CONCAT(e.first_name, ' ', e.last_name) AS full_name,
SUM(es.salary) AS total_salary,
GROUP_CONCAT(h.name)
FROM employee_hobby AS eh
INNER JOIN hobby h ON h.id = eh.fk_hobby_id
INNER JOIN employee AS e ON e.id = eh.fk_employee_id
INNER JOIN employee_salary AS es ON es.fk_employee_id = eh.fk_employee_id
GROUP BY eh.fk_employee_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/409076.html
標籤：