如何在SubSelection Mysql中正确选择两列

Question

大家好，我正在尝试在另一个select中使用select，我得到了opperan的错误，应该包含1个值。我看到了其他答案，但无法找出如何应用该解决方案。我的问题是：

SELECT a.date_insert AS date
,HOUR(a.date_insert) AS hour
,AVG(spood)
,AVG(factor)
,(SELECT AVG(dd.spood) as median_val1,AVG(dd.factor) as median_val2
FROM (
SELECT d.spood, d.factor, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM traf d, (SELECT @rownum:=0) r
WHERE d.spood is NOT NULL 
ORDER BY d.spood 
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) ))

FROM traf a 
INNER JOIN mycolumn b
ON a.ref_id = b.ref_id where value_3 > 100
GROUP BY 1,2

请提供任何帮助，将不胜感激

**我在(SELECT AVG(处得到错误，它是子查询**opperand应该包含1列，而我希望检索2列

Answer 1

您正在从SELECT子句中的子查询中选择两个表达式。

如果在SELECT子句中使用子查询，则它在子查询的SELECT子句中必须只有一个值，并且必须只返回一行。

尝试从子查询中删除一个表达式，您会发现成功的。

Answer 2

错误消息非常清楚。SELECT子句中有一个子查询，它是标量子查询：

(SELECT AVG(dd.spood) as median_val1,AVG(dd.factor) as median_val2
FROM (SELECT d.spood, d.factor, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM traf d, (SELECT @rownum:=0) r
WHERE d.spood is NOT NULL 
ORDER BY d.spood 
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2
)

标量子查询只能返回一列，最多只能返回一行。简单的解决方案是在子查询中只返回一个值。如果需要多个值，请使用多个子查询。

可能会重写整个查询。但是，您的问题没有提供示例数据、所需的结果，也没有解释查询应该做什么。

-更新

尝试使用带有子查询的LEFT JOIN，如下所示：

SELECT a.date_insert AS date
,HOUR(a.date_insert) AS hour
,AVG(spood)
,AVG(factor)
,MAX(AVG_VIEW.median_val1) -- usgae of the values from sub-query
,MAX(AVG_VIEW.median_val1) -- usgae of the values from sub-query
FROM traf a 
INNER JOIN mycolumn b
ON a.ref_id = b.ref_id
-- added this
LEFT JOIN (SELECT AVG(dd.spood) as median_val1,AVG(dd.factor) as median_val2
FROM (
SELECT d.spood, d.factor, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM traf d, (SELECT @rownum:=0) r
WHERE d.spood is NOT NULL 
ORDER BY d.spood 
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) )) AS AVG_VIEW
ON 1=1 -- use proper conditions and accordingly use the correct columns in SELECT of this sub-query
-- till here
where value_3 > 100
GROUP BY 1,2

注意：您需要根据自己的要求稍微更改一下这个查询。