PHP/返回多于1行

Question

这可能已经被问到了，但是由于我是一个庞大的PHP/MySQL，我真的不知道我在寻找什么。我正在执行以下查询：

SELECT shortlink_analytics.shortlink AS short, 
COUNT(shortlink_analytics.shortlink) AS shortcount, 
(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink)    AS shLink 
FROM shortlink_analytics JOIN shortlinks ON shortlink_analytics.shortlink =   shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC LIMIT 10

这过去是有效的，但是在上个星期重新检查之后，我注意到输出没有工作，而是显示了以下错误：

Subquery returns more than 1 row

在玩了一遍代码之后，我找到了问题所在(我想！)(但我不太确定)：

GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC LIMIT 10

有谁能解释：

• 我哪里出错了？
• 可能的原因，为什么它工作一个月一次，然后它停止工作？
• 怎样才能解决我的问题？

Answer 1

此部分是子查询：

(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink)

你能简单地把它LIMIT到1行吗？

(SELECT link FROM shortlinks WHERE shortlinks.shortlink = shortlink_analytics.shortlink LIMIT 1)

您应该尝试找出数据返回多行的原因，甚至可能需要ORDER BY来确保返回正确的行。

Answer 2

如果您想获取关系上的所有链接，请使用group_concat()

SELECT shortlink_analytics.shortlink AS short, 
       COUNT(shortlink_analytics.shortlink) AS shortcount, 
       group_concat(link) as shlinks
FROM shortlink_analytics JOIN
     shortlinks ON
     shortlink_analytics.shortlink = shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink)
DESC LIMIT 10;

如果这种猜测是错误的，并且需要子查询，则仍然可以通过在子查询中执行group_concat()获得所有链接：

SELECT shortlink_analytics.shortlink AS short, 
       COUNT(shortlink_analytics.shortlink) AS shortcount, 
       (SELECT group_concat(link)
        FROM shortlinks
        WHERE shortlinks.shortlink = shortlink_analytics.shortlink
       ) AS shLinks
FROM shortlink_analytics JOIN
     shortlinks
     ON shortlink_analytics.shortlink = shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC
LIMIT 10