MySQL 中使用 any_value 子查詢時，WHERE IN 失效的原因是什么？

mysql 中使用 any_value 子查詢時，where in 失效的原因

在 mysql 中，使用 any_value 子查詢對分組后的數據進行篩選時，可能會導致 where in 失效。這是因為 any_value 函數返回的是一組行中的第一個非空值，而 where in 操作符期望的是一系列明確的值。

在這種情況下，當將 any_value 子查詢用作 where in 的子句時，查詢優化器可能會將其轉換為內部連接。內部連接將 test 表中的每一行與 any_value 子查詢返回的行進行匹配，從而導致返回整個 test 表。

解決方案：

為了解決這個問題，需要使用一個額外的 select 語句對 any_value 子查詢的結果進行封裝。這會強制查詢優化器將子查詢作為實際值表來處理，而不是內部連接。

select * from test where id in (     select id     from (         select any_value(id) as id         from test         group by type     ) as temp );

第二個問題：未指定目標表別名時的異常行為

當在 any_value 子查詢中未指定目標表別名時，mysql 不會報錯，并且會返回整個 test 表。這是因為 mysql 允許將子查詢的結果作為派生表處理。如果沒有指定別名，則 mysql 會根據子查詢的 group by 子句，自動生成一個派生表的名稱，格式為 group_by_table_。

在示例中，未指定的子查詢的派生表名稱為 group_by_table_1。因此，當使用錯誤的 select 語句時，實際執行的查詢如下：

SELECT * FROM test WHERE id IN (     SELECT id     FROM GROUP_BY_TABLE_1 );

由于 group_by_table_1 包含整個 test 表，因此查詢返回了整個 test 表。