我需要撰寫一個 MySQL 查詢，該查詢單獨回傳每列模式的出現次數，而無需明確指定列名。
假設資料為：
| apples | bananas | oranges |
| 4 | 4 | 3 |
| 2 | 2 | 1 |
| 4 | 3 | 5 |
| 3 | 3 | 5 |
| 4 | 1 | 5 |
我正在尋找的結果是：
| mode | count |
| 4 | 3 |
| 3 | 2 |
| 5 | 3 |
要獲取單個列（蘋果）的模式：
SELECT apples AS mode, COUNT(*) AS Count
FROM tablename
GROUP BY apples
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename GROUP BY apples);
要回傳列名，我可以執行以下操作：
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename'
低效且使用顯式列名，我可以使用 UNION 實作結果：
SELECT apples AS mode, COUNT(*) AS Count
FROM tablename
GROUP BY apples
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename GROUP BY apples)
UNION
SELECT bananas AS mode, COUNT(*) AS Count
FROM tablename
GROUP BY bananas
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename GROUP BY bananas)
UNION
SELECT oranges AS mode, COUNT(*) AS Count
FROM tablename
GROUP BY oranges
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename GROUP BY oranges)
我一直在嘗試一系列不同的查詢，將兩者與 GROUP BY 和子查詢結合起來，但它們一團糟，而且我沒有取得太大進展。我還沒有生成一個查詢來有效地顯示每列的模式，并顯式指定列名（更不用說使用第二個查詢回傳的列名了）。
例如（感覺我什至沒有走在正確的軌道上）：
SELECT COUNT(*) AS count
FROM tablename
GROUP BY (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename')
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename
GROUP BY (
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename'))
謝謝！！
uj5u.com熱心網友回復：
在單個查詢中使用純 SQL 似乎無法獲得預期結果，但它可能會構建一個如下所示的動態結果：
DROP TABLE IF EXISTS archives.sample_table;
CREATE TABLE archives.sample_table (
apples INT,
bananas INT,
oranges INT
);
INSERT INTO sample_table VALUES
( 4 , 4 , 3 ),
( 2 , 2 , 1 ),
( 4 , 3 , 5 ),
( 3 , 3 , 5 ),
( 4 , 1 , 5 )
;
SET SESSION group_concat_max_len = 9999999;
SET @table_name = 'sample_table';
SET @table_schema = 'archives';
WITH RECURSIVE column_info AS (
SELECT ORDINAL_POSITION column_index, COLUMN_NAME column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name and TABLE_SCHEMA = @table_schema
),
query_text AS (
SELECT i.column_index, CONCAT('SELECT column_name, column_value, counts FROM ( SELECT ''', i.column_name, ''' column_name, `', i.column_name, '` column_value, COUNT(*) counts, RANK() OVER(ORDER BY COUNT(*) DESC) rk FROM ', @table_name, ' GROUP BY `', i.column_name, '` ) r WHERE rk = 1') single_query
FROM column_info i
WHERE i.column_index = 1
UNION ALL
SELECT i.column_index, CONCAT('SELECT column_name, column_value, counts FROM ( SELECT ''', i.column_name, ''' column_name, `', i.column_name, '` column_value, COUNT(*) counts, RANK() OVER(ORDER BY COUNT(*) DESC) rk FROM ', @table_name, ' GROUP BY `', i.column_name, '` ) r WHERE rk = 1') single_query
FROM query_text prev
JOIN column_info i ON prev.column_index 1 = i.column_index
)
SELECT GROUP_CONCAT(single_query SEPARATOR ' UNION ALL ') INTO @stat_query
FROM query_text
;
PREPARE stmt FROM @stat_query
;
EXECUTE stmt
;
DEALLOCATE PREPARE stmt
;
樣本輸出
mysql> DROP TABLE IF EXISTS archives.sample_table;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE archives.sample_table ( apples INT, bananas INT, oranges INT);
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO sample_table VALUES ( 4 , 4 , 3 ),( 2 , 2 , 1 ),( 4 , 3 , 5 ),( 3 , 3 , 5 ),( 4 , 1 , 5 );
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SET SESSION group_concat_max_len = 9999999;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @table_name = 'sample_table';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @table_schema = 'archives';
Query OK, 0 rows affected (0.00 sec)
mysql> WITH RECURSIVE column_info AS ( SELECT ORDINAL_POSITION column_index, COLUMN_NAME column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name and TABLE_SCHEMA = @table_schema),query_text AS ( SELECT i.column_index, CONCAT('SELECT column_name, column_value, counts FROM ( SELECT ''', i.column_name, ''' column_name, `', i.column_name, '` column_value, COUNT(*) counts, RANK() OVER(ORDER BY COUNT(*) DESC) rk FROM ', @table_name, ' GROUP BY `', i.column_name, '` ) r WHERE rk = 1') single_query FROM column_info i WHERE i.column_index = 1 UNION ALL SELECT i.column_index, CONCAT('SELECT column_name, column_value, counts FROM ( SELECT ''', i.column_name, ''' column_name, `', i.column_name, '` column_value, COUNT(*) counts, RANK() OVER(ORDER BY COUNT(*) DESC) rk FROM ', @table_name, ' GROUP BY `', i.column_name, '` ) r WHERE rk = 1') single_query FROM query_text prev JOIN column_info i ON prev.column_index 1 = i.column_index) SELECT GROUP_CONCAT(single_query SEPARATOR ' UNION ALL ') INTO @stat_query FROM query_text;
Query OK, 1 row affected (0.00 sec)
mysql> PREPARE stmt FROM @stat_query;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
------------- -------------- --------
| column_name | column_value | counts |
------------- -------------- --------
| apples | 4 | 3 |
| bananas | 3 | 2 |
| oranges | 5 | 3 |
------------- -------------- --------
3 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
uj5u.com熱心網友回復：
根據@bill-karwin 和@shadow，任何在查詢中動態設定列的嘗試都是不可能的，所以我：
- 創建了一個生成查詢文本以使用 UNION 生成模式的程序 - 它從 INFORMATION_SCHEMA 中獲取列，然后遍歷列以創建查詢：
CREATE DEFINER=`root`@`localhost` PROCEDURE `column_modes`(OUT strsql TEXT)
BEGIN
SET @cols = (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tablename');
SET @ct = (SELECT COUNT(*) FROM tablename);
SET @n = 0;
SET @sql = '';
WHILE @n < @cols DO
PREPARE stmt FROM "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename' LIMIT ?, 1 INTO @col";
EXECUTE stmt USING @n;
DEALLOCATE PREPARE stmt;
SET @sbq = CONCAT('SELECT ',
@col,
' AS mode, COUNT(*)/',
@ct,
' AS count FROM tablename GROUP BY ',
@col,
' HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tablename GROUP BY ',
@col,
')');
SET @sql = CONCAT(@sql,
"SELECT '",
@col,
"' as 'column', GROUP_CONCAT(mode SEPARATOR ',') as mode, GROUP_CONCAT(count SEPARATOR ',') as count FROM (",
@sbq,
') temp');
IF @n != (@cols - 1) THEN
SET @sql = CONCAT(@sql, ' UNION ');
END IF;
SET @n = @n 1;
END WHILE;
-- SELECT @sql;
SET strsql = @sql;
END
- 使用 PREPARE 執行查詢：
CALL column_modes(@thisvar);
SELECT @thisvar;
PREPARE stmt FROM @thisvar;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
也非常感謝@ProGu 提供了第二個解決方案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/380030.html