mysql 5.7group by查询非_MySQL 5.7 | GROUP BY |非聚合列錯誤

I upgraded our mysql db from 5.6 to 5.7 and am in the process of fixing some queries which are throwing some errors. One of the queries I am working involves a GROUP BY with a COALESCE.

我將我們的mysql數據庫從5.6升級到5.7，並且正在修復一些拋出一些錯誤的查詢。我正在處理的一個查詢涉及帶有COALESCE的GROUP BY。

Here is the query (abstracted) that works:

這是有效的查詢(抽象)：

SELECT

MAX(a.id),

a.entered,

count(*) AS teh_count

FROM

a

INNER JOIN

b ON b.id = a.link_to_b_id

INNER JOIN

c ON c.link_to_b_id = b.id

WHERE

b.revision_id > 0

AND

c.terminated_at = '0000-00-00 00:00:00'

AND

a.created_at > date_sub(NOW(), INTERVAL 8 HOUR)

GROUP BY

a.entered

ORDER BY

teh_count DESC

LIMIT

6;

But I need to COALESCE a.entered with c.override, so I tried the following:

但我需要使用c.override進行COALESCE，所以我嘗試了以下方法：

SELECT

MAX(a.id),

a.entered,

COALESCE(c.override, a.entered) AS appearance,

count(*) AS teh_count

FROM

a

INNER JOIN

b ON b.id = a.link_to_b_id

INNER JOIN

c ON c.link_link_to_b_id = b.id

WHERE

b.revision_id > 0

AND

c.terminated_at = '0000-00-00 00:00:00'

AND

a.created_at > date_sub(NOW(), INTERVAL 8 HOUR)

GROUP BY

a.entered

ORDER BY

teh_count DESC

LIMIT

6;

But MySQL 5.7 now throws the following error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'st_core.tuc.code_appearance_override' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

但MySQL 5.7現在拋出以下錯誤：SELECT列表的表達式＃2不在GROUP BY子句中，並且包含非聚合列'st_core.tuc.code_appearance_override'，它在功能上不依賴於GROUP BY子句中的列;這與sql_mode = only_full_group_by不兼容

I assume I can can change the sql_mode, but I'd prefer not too. What the error is telling me makes sense, in that the COALESCE column is not aggregated, so as a test I wrapped it with MAX and it works, however it seems kind of hacky to me.

我想我可以改變sql_mode，但我也不願意。錯誤告訴我的是有道理的，因為COALESCE列沒有聚合，所以作為測試我用MAX包裝它並且它可以工作，但是對我來說它似乎有些不好看。

Is there a more elegant solution?

有更優雅的解決方案嗎？

2 个解决方案

#1

1

You should also include a.entered in your group by clause and that's what the error saying. Though not sure why you are grouping by an different column a.code_entered?

您還應該在group by子句中包含a.entered，這就是錯誤所說的內容。雖然不確定為什么要按不同的列a.code_entered進行分組？

Your query should look like

您的查詢應該是這樣的

SELECT

MAX(a.id),

a.entered,

COALESCE(c.override, a.entered) AS appearance,

count(*) AS teh_count

FROM

a

INNER JOIN

b ON b.id = a.link_to_b_id

INNER JOIN

c ON c.link_link_to_b_id = b.id

WHERE

b.revision_id > 0

AND

c.terminated_at = '0000-00-00 00:00:00'

AND

a.created_at > date_sub(NOW(), INTERVAL 8 HOUR)

GROUP BY

a.entered,

COALESCE(c.override, a.entered)

ORDER BY

teh_count DESC

LIMIT

6;

#2

2

I think you intend something like this:

我想你打算這樣的事情：

SELECT MAX(a.id),

COALESCE(c.override, a.entered) AS appearance,

count(*) AS the_count

FROM a INNER JOIN

b

ON b.id = a.link_to_b_id INNER JOIN

c

ON c.link_link_to_b_id = b.id

WHERE b.revision_id > 0 AND

c.terminated_at = '0000-00-00 00:00:00' AND

a.created_at > date_sub(NOW(), INTERVAL 8 HOUR)

GROUP BY appearance

ORDER BY the_count DESC

LIMIT 6;

This removes a.entered from the SELECT list so there is only one column for grouping. That column can be referenced by table alias in the GROUP BY.

這將從SELECT列表中刪除a.entered，因此只有一列用於分組。該列可以由GROUP BY中的表別名引用。