时间戳

首页 > 解决方案 > 操作数应在我的 sql 查询中包含 1 列错误

mysql - 操作数应在我的 sql 查询中包含 1 列错误

问题描述

不知道这是从哪里来的。我的意图是让 ROUND(AVG(duration)) 与lead_at 和qualified_at 的差异（第二个子查询）

SELECT (SELECT first_name 
        FROM users_permissions p WHERE p.user_id=a.assistant_id LIMIT 1) as Name, (SELECT u.lead_at, u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id WHERE u.qualified_at - u.lead_at) AS Difference, ROUND(AVG(duration)), count(id)
FROM aircalls a
WHERE assistant_id IS NOT NULL 
AND status='Call Connected' 
AND type='user' 
AND EXISTS (SELECT u.id FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id AND u.status NOT IN (1,7))
AND EXISTS (SELECT p.user_id FROM users_permissions p INNER JOIN aircalls ON p.user_id=aircalls.user_id AND p.is_agent NOT IN (1))
AND year(a.created_at)=2019
GROUP BY(SELECT first_name 
         FROM users_permissions p WHERE p.user_id=a.assistant_id LIMIT 1)

标签： mysqlsqldatabase

解决方案

问题在于您的第二个选择（差异），而不是推导出两个值，您只是将它们都用逗号分隔。此外，where其中的子句select没有有效值。

它是：

SELECT u.lead_at, u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id WHERE u.qualified_at - u.lead_at) AS Difference

这是对该错误行的更正：

(SELECT u.lead_at - u.qualified_at FROM users u INNER JOIN aircalls ON u.id=aircalls.user_id) AS Difference

推荐阅读