MySQL 在显示所有联系人组时显示有限数量的联系人

Question

我有一个地址簿，称为联系人。为了帮助组织联系人，我建立了群组(例如家人、 friend 、工作)。我想为每个组显示 4 或 10 个联系人示例。这适用于列出所有组的组索引页面，但并不打算提供每个组的完整联系人列表(仅适用于专用组页面)。

对表和列进行分组

email_groups

id、id_user、名称

联系人表和列

email_contacts

id、id_group、id_user、名称

我一直在解决这个问题，但 c1 的列名称错误。

SELECT 
eg.id, 
eg.name, 
(SELECT c1.name FROM email_contacts AS c1 WHERE id_user='1' LIMIT 0,1) AS contact_1, 
(SELECT c2.name FROM email_contacts AS c1 WHERE id_user='1' LIMIT 1,1) AS contact_2, 
(SELECT c3.name FROM email_contacts AS c3 WHERE id_user='1' LIMIT 2,1) AS contact_3, 
(SELECT c4.name FROM email_contacts AS c4 WHERE id_user='1' LIMIT 3,1) AS contact_4 
FROM email_groups AS eg 
INNER JOIN email_contacts AS ec on ec.id=eg.id_group

Answer 1

您在子查询中提供了两次别名C1，我猜第二个子查询将是C2，您正在尝试选择c2.name > 但您提供的别名为 c1 SELECT c2.name FROM email_contacts AS c1

SELECT 
eg.id, 
eg.name, 
(SELECT c1.name FROM email_contacts AS c1 WHERE id_user='1' LIMIT 0,1) AS contact_1, 
(SELECT c2.name FROM email_contacts AS c2 WHERE id_user='1' LIMIT 1,1) AS contact_2, 
(SELECT c3.name FROM email_contacts AS c3 WHERE id_user='1' LIMIT 2,1) AS contact_3, 
(SELECT c4.name FROM email_contacts AS c4 WHERE id_user='1' LIMIT 3,1) AS contact_4 
FROM email_groups AS eg 
INNER JOIN email_contacts AS ec on ec.id=eg.id_group