首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >用语句倒置一个组是可行的吗?

问用语句倒置一个组是可行的吗?
EN

Stack Overflow用户
提问于 2012-02-21 16:56:44
回答 1查看 853关注 0票数 1

一个快速的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)。

像这样的语法存在吗?

谢谢

EN

回答 1

Stack Overflow用户

发布于 2012-02-21 18:37:16

不幸的是,没有任何直接的SQL可以做到这一点。

但是,您可能希望使用information_schema.columns尝试一些动态SQL。

代码语言:javascript
复制
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

代码语言:javascript
复制
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中。您可以通过以下方法检索它们:

代码语言:javascript
复制
SELECT GROUP_CONCAT(column_name) column_list FROM information_schema.columns
WHERE table_schema='test' AND table_name='customers';

它给了你这个:

代码语言:javascript
复制
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,公司名称,电话,电子邮件)。

您可以这样查询:

代码语言:javascript
复制
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;

看看它给了你什么

代码语言:javascript
复制
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)

好的,您如何构建要查找的查询?

比方说

  • 您的表在mydb数据库
  • 中称为mytable,它的列为a、b、c、d、e、f、num
  • ,您希望在a,c,e,num

上执行逆操作。

您必须动态地构建查询。下面是:

步骤01)为a,c,e,num创建排除列表

代码语言:javascript
复制
SELECT 'a' column_name
UNION SELECT 'c'
UNION SELECT 'e'
UNION SELECT 'num';

步骤2)创建对mydb.mytable使用排除列表的查询

代码语言:javascript
复制
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按列分组)

代码语言:javascript
复制
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)创建完整的查询

代码语言:javascript
复制
SET @s = CONCAT('SELECT a,b,c,d,e,f,sum(num) from mydb.mytable group by ',@gbc);

步骤05)执行动态SQL

代码语言:javascript
复制
PREPARE stmt FROM @s; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 

试试看!!

警告

如果您希望所有列按a,b,c,d,e,f分组,那么只需使用它作为排除列表。

代码语言:javascript
复制
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;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/9381683

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档