一个快速的MySQL问题。Ff我有一个表,其中列为a,b,c,c,d,e,f,num作为一个例子。无论如何,我可以选择a,b,.,f,sum(num)组,a,b,.,f,而不需要在后面逐组添加该组。我想这样做的原因是,我将有一个数组的字段,我不想包含在组中,当它是空的时候,我想按所有字段分组。
本质上,如果我有一个包含(a,c,e)的数组,我想要生成这样的查询,选择a,b,c,d,e,f,sum( num )作为num组,按b,d,f,f选择a,b,c,d,e,f,sum(num)。
像这样的语法存在吗?
谢谢
发布于 2012-02-21 18:37:16
不幸的是,没有任何直接的SQL可以做到这一点。
但是,您可能希望使用information_schema.columns尝试一些动态SQL。
mysql> desc information_schema.columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.05 sec)假设您有一个表test.customers
mysql> desc test.customers;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| companyname | varchar(30) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| phone | char(12) | NO | | NULL | |
| email | varchar(100) | YES | | NULL | |
| contact | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)这些列存在于information_schema.columns中。您可以通过以下方法检索它们:
SELECT GROUP_CONCAT(column_name) column_list FROM information_schema.columns
WHERE table_schema='test' AND table_name='customers';它给了你这个:
mysql> SELECT GROUP_CONCAT(column_name) column_list FROM information_schema.columns
-> WHERE table_schema='test' AND table_name='customers';
+--------------------------------------------+
| column_list |
+--------------------------------------------+
| id,companyname,address,phone,email,contact |
+--------------------------------------------+
1 row in set (0.01 sec)假设你想得到的东西都不在里面(id,公司名称,电话,电子邮件)。
您可以这样查询:
SELECT GROUP_CONCAT(A.column_name) column_list
FROM
(
SELECT column_name
FROM information_schema.columns
WHERE table_schema='test'
AND table_name='customers'
) A
LEFT JOIN
(
SELECT 'id' column_name
UNION SELECT 'companyname'
UNION SELECT 'phone'
UNION SELECT 'email'
) exclusion
USING (column_name)
WHERE exclusion.column_name IS NULL;看看它给了你什么
mysql> SELECT GROUP_CONCAT(A.column_name) column_list
-> FROM
-> (
-> SELECT column_name
-> FROM information_schema.columns
-> WHERE table_schema='test'
-> AND table_name='customers'
-> ) A
-> LEFT JOIN
-> (
-> SELECT 'id' column_name
-> UNION SELECT 'companyname'
-> UNION SELECT 'phone'
-> UNION SELECT 'email'
-> ) exclusion
-> USING (column_name)
-> WHERE exclusion.column_name IS NULL;
+-----------------+
| column_list |
+-----------------+
| address,contact |
+-----------------+
1 row in set (0.02 sec)好的,您如何构建要查找的查询?
比方说
上执行逆操作。
您必须动态地构建查询。下面是:
步骤01)为a,c,e,num创建排除列表
SELECT 'a' column_name
UNION SELECT 'c'
UNION SELECT 'e'
UNION SELECT 'num';步骤2)创建对mydb.mytable使用排除列表的查询
SELECT GROUP_CONCAT(A.column_name) column_list
FROM
(
SELECT column_name
FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytable'
) A
LEFT JOIN
(
SELECT 'a' column_name
UNION SELECT 'c'
UNION SELECT 'e'
UNION SELECT 'num'
) exclusion
USING (column_name)
WHERE exclusion.column_name IS NULL;步骤03)将输出捕获到变量中(gbc按列分组)
SELECT GROUP_CONCAT(A.column_name)
INTO @gbc
FROM
(
SELECT column_name
FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytable'
) A
LEFT JOIN
(
SELECT 'a' column_name
UNION SELECT 'c'
UNION SELECT 'e'
UNION SELECT 'num'
) exclusion
USING (column_name)
WHERE exclusion.column_name IS NULL;步骤04)创建完整的查询
SET @s = CONCAT('SELECT a,b,c,d,e,f,sum(num) from mydb.mytable group by ',@gbc);步骤05)执行动态SQL
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 试试看!!
警告
如果您希望所有列按a,b,c,d,e,f分组,那么只需使用它作为排除列表。
SELECT GROUP_CONCAT(A.column_name)
INTO @gbc
FROM
(
SELECT column_name
FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytable'
) A
LEFT JOIN
(
SELECT 'a' column_name
) exclusion
USING (column_name)
WHERE exclusion.column_name IS NULL;https://stackoverflow.com/questions/9381683
复制相似问题