gpt4 book ai didi

mysql - 将新的行集添加到现有查询的结果集中

转载 作者:行者123 更新时间:2023-11-29 14:15:02 26 4
gpt4 key购买 nike

这是查询中使用的表:

帮助(相关字段:id、id_user)

enter image description here

friend

enter image description here

SHARED_HELPS

enter image description here

所以:

此查询(有效)返回来自用户及其引用的其他用户的所有帮助:

$sql = 'SELECT
helps.*,CASE WHEN shared_helps.userid IS NULL THEN 0 ELSE shared_helps.userid END as is_shared, CASE WHEN shared_helps.userid IS NULL THEN helps.fecha ELSE shared_helps.fecha END as ffecha
FROM
helps
LEFT JOIN shared_helps
ON shared_helps.helpid = helps.id
AND shared_helps.userid = '.$value.'

WHERE (helps.id_user = '.$value.' AND helps.id_group <= 0) OR shared_helps.userid = '.$value.'



ORDER BY ffecha DESC';

此外,此查询(有效)列出了用户及其 friend 的所有帮助

$sql = 'SELECT  id, title, content, id_user, id_group, id_type, id_loc, avatar, attached, fecha, likes, lan, needsCount, recivedCount
FROM
(
SELECT *, 1 as OrderBy
FROM helps
WHERE id_user = '.$value.' or id_user IN (SELECT helpid FROM shared_helps WHERE userid = '.$value.')
UNION
SELECT h.*, 2 as OrderBy
FROM (
SELECT id AS friendsId,
CASE followerid
WHEN '.$value.' THEN followingid
ELSE followerid
END AS friend_id
FROM friends
WHERE acepted = 1 AND
(followerid ='.$value.' OR followingid = '.$value.')
) AS f
INNER JOIN helps AS h
ON h.id_user = f.friend_id where id_group < 0
) x
ORDER BY ID DESC
';

问题是到最后一个,我需要添加来自其他用户但他引用的内容(就像我发布的两个查询的混合......)

我尝试过这个:

$sql = 'SELECT  id, title, content, id_user, id_group, id_type, id_loc, avatar, attached, fecha, likes, lan, needsCount, recivedCount,
CASE WHEN shared_helps.userid IS NULL THEN helps.fecha ELSE shared_helps.fecha END as ffecha
FROM
(
SELECT *, 1 as OrderBy
FROM helps
WHERE id_user = '.$value.' or id_user IN (SELECT helpid FROM shared_helps WHERE userid = '.$value.')
UNION
SELECT h.*, 2 as OrderBy
FROM (
SELECT id AS friendsId,
CASE followerid
WHEN '.$value.' THEN followingid
ELSE followerid
END AS friend_id
FROM friends
WHERE acepted = 1 AND
(followerid ='.$value.' OR followingid = '.$value.')
) AS f
INNER JOIN helps AS h
ON h.id_user = f.friend_id where id_group < 0
) x
LEFT JOIN shared_helps
ON shared_helps.helpid = x.id
AND shared_helps.userid = '.$value.'

WHERE (x.id_user = '.$value.' AND helps.id_group <= 0) OR shared_helps.userid = '.$value.'



ORDER BY ffecha DESC
';

但是我得到了:

Column 'id' in field list is ambiguous

但这显然超出了我的知识范围。有人可以告诉我光线吗?

最佳答案

如果我没记错的话,您需要指定列 ID 的来源,因为所有表都有列 ID

尝试添加 tableName Helps ,因为它包含在子查询中 x

您的查询中如此,

SELECT x.ID, ....
FROM...
WHERE ....

关于mysql - 将新的行集添加到现有查询的结果集中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12824177/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com