Mysql
如何在 SQL 中返回帶有內部列表的列表?
我準備了以下 SQL 語句:
SELECT tasks.name, profiles.email, types.specification, tasks.description, tasks.attachment_link, tasks.priority, assigned_tasks.progress_details, assigned_tasks.activation_date, assigned_tasks.expired_date FROM profiles, types, tasks, assigned_tasks WHERE profiles.id = assigned_tasks.profile_id AND assigned_tasks.task_id = tasks.id AND types.id = tasks.type_id ORDER BY tasks.name;
我想在視圖中顯示分配的電子郵件任務。像這樣的東西:
我看到具有單一分配的記錄,任務名稱重複。我想在表格的所有者單元格中顯示電子郵件列表 - 可以嗎?
Python 返回 touples:
('Backend', 'agnieszka.lasota1@gmail.com', 'Database managment', 'Create serwer full backend and copy data to SSD 1TB', 'NULL', 2, 'to do - 0% progress', datetime.datetime(2021, 6, 1, 10, 18, 36), None) ('call to customer', 'wolnygosc@interia.pl', 'IT online support', 'answer for questions about cloud', 'https://dianet.pl/cloud/index.html', 1, 'TO DO', datetime.datetime(2021, 6, 1, 14, 57, 23), None)
我想要這樣的東西:
('Backend', ('agnieszka.lasota1@gmail.com', 'wolnygosc@interia.pl'), 'Database managment', 'Create serwer full backend and copy data to SSD 1TB', 'NULL', 2, 'to do - 0% progress', datetime.datetime(2021, 6, 1, 10, 18, 36), None)
可能會將兩個使用者分配給我想在 HTML 表中顯示的一項任務。如何拆分記錄?
使用
GROUP_CONCAT
.使用
JOIN .. ON
語法而不是舊語法。SELECT tasks.name, GROUP_CONCAT(profiles.email) AS emails, types.specification, tasks.description, tasks.attachment_link, tasks.priority, ats.progress_details, ats.activation_date, ats.expired_date FROM profiles AS p JOIN assigned_tasks AS ats ON ats.profile_id = p.id JOIN tasks ON tasks.id = ats.task_id JOIN types ON types.id = tasks.type_id GROUP BY tasks.name, types.specification, tasks.description, tasks.attachment_link, tasks.priority, ats.progress_details, ats.activation_date, ats.expired_date ORDER BY tasks.name;
而不是
datetime.datetime(2021, 6, 1, 10, 18, 36)
,只需使用"2021-06-01 10:18:26"
或者,如果您只想要目前日期和時間,也可以使用它:
NOW()