jquery - 复杂4个链表MYSQL查询

Question

我有一个复杂的查询要写(至少对我来说)。我有4张 table - ressources_department(ressource_record_id、account_id、department_id、事件) - ressources_type_users(ID、电子邮件、密码) - 资源记录(id、ressource_main_id、ressource_id、account_id) - ressources_records_details(ressource_record_id、ressource_field_id、ressource_data)

• ressource_department 保存特定部门和帐户的用户 (ressources_records)。
• ressource_type_users 保存用户的基本信息(电子邮件、密码)及其唯一 ID。
• ressource_records 保存用户的唯一 ID (ressource_main_id)，也是指向用户详细信息 (ressources_records_details) 的链接
• ressources_records_details 保存用户数据。 ressource_fields(int)是ressource_date的类型。前任。 : 113 是电子邮件地址的代码。

我必须编写一个查询来显示所有内联信息。

到目前为止我所做的是:

SELECT a.ressource_record_id, a.account_id, a.active, a.department_id, b.ressource_main_id, b.ressource_id 
FROM ressources_department a 
join ressources_records b on a.ressource_record_id = b.id
WHERE a.active=1

除了“ressources_records_details”数据之外，这几乎满足了我的需要。我可以通过 php 循环并查询数据库中的每一行，但我不认为这是正确的方法。

我尝试过:

SELECT a.ressource_record_id, a.account_id, a.active, a.department_id, b.ressource_main_id, b.ressource_id, max(if((c.ressource_type = 113),c.ressource_data,NULL)) AS email
FROM ressources_department a 
join ressources_records b on a.ressource_record_id = b.id
join ressources_records_details c on a.ressource_record_id = c.ressource_record_id 
group by c.ressource_record_id

但是有些记录丢失了......需要帮助，我被困住了。谢谢!

更新:我通过大量子查询得到了我想要的结果，但这会花费大量服务器时间不是吗？

SELECT a.ressource_record_id, a.account_id, a.active, a.department_id, b.ressource_main_id, b.ressource_id,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=113) as email,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=101) as lastname,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=100) as firstname,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=114) as address,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=115) as zip,
(select ressource_data from ressources_records_details where ressource_record_id=a.ressource_record_id and ressource_type=116) as city
FROM ressources_department a 
join ressources_records b on a.ressource_record_id = b.id

Answer 1

您可以使用 CASE 语句在没有子查询的情况下执行此操作:

SELECT rd.ressource_record_id, rd.account_id, rd.active, 
    rd.department_id, rr.ressource_main_id, rr.ressource_id,
CASE WHEN rrd.resource_type = 113 THEN ressource_data ELSE NULL END AS email,
CASE WHEN rrd.resource_type = 101 THEN ressource_data ELSE NULL END AS lastname,
CASE WHEN rrd.resource_type = 100 THEN ressource_data ELSE NULL END AS firstname,
CASE WHEN rrd.resource_type = 114 THEN ressource_data ELSE NULL END AS address,
CASE WHEN rrd.resource_type = 115 THEN ressource_data ELSE NULL END AS zip,
CASE WHEN rrd.resource_type = 116 THEN ressource_data ELSE NULL END AS city
FROM ressources_department rd 
JOIN ressources_records rr 
    ON rb.ressource_record_id = rr.id
JOIN ressources_records_details rrd 
    ON rrd.ressource_record_id = rd.ressource_record_id
GROUP BY rd.ressource_record_id, rd.account_id, 
    rd.active, rd.department_id, rr.ressource_main_id, rr.ressource_id