MySQL同时查询多行数据并以一行结果输出

在实际应用中，我们经常需要获取多行数据，但是希望以一行结果输出，这时候就需要用到MySQL的一些操作技巧。

阅读更多：MySQL 教程

GROUP_CONCAT函数

GROUP_CONCAT函数将一组值连接成一个字符串，这对于多行查询以一行结果输出非常方便。具体语法为：

GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

其中，DISTINCT可选，表示去重；expr为要连接的字段，可以是列名，表达式等；ORDER BY 可选，表示排序方式；SEPARATOR 可选，表示分隔符，默认为逗号。

下面是一个例子，首先建立一个测试表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向表中插入一些数据：

INSERT INTO `test` (`name`) VALUES ('a'),('b'),('c'),('d'),('e'),('f');

如果想要将表中所有name字段值连接成一个字符串输出，可以这样写：

SELECT GROUP_CONCAT(name) as names FROM test;

输出结果为：

names
a,b,c,d,e,f

可以看到，所有name值已经被连接成了一个字符串。

如果想要以“-”分隔符连接所有name值，可以这样写：

SELECT GROUP_CONCAT(name SEPARATOR '-') as names FROM test;

输出结果为：

names
a-b-c-d-e-f

SQL内联查询

内联查询是指在一个SQL语句中嵌套另一个SQL语句，通常用于查询多个表或者多个子查询的数据。对于想要将多行数据以一行结果输出的情况，内联查询也非常有用。

下面是一个例子，首先建立两个测试表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `goods` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user` (`name`) VALUES ('张三'),('李四'),('王五');
INSERT INTO `order` (`user_id`, `goods`) VALUES (1,'商品1'),(1,'商品2'),(2,'商品3'),(3,'商品4'),(3,'商品5');

其中，user表记录了用户信息，order表记录了订单信息，user_id表示订单对应的用户。

如果想要查询每个用户对应的订单列表，可以这样写：

SELECT u.id, u.name, o.goods FROM user u INNER JOIN order o ON u.id = o.user_id;

输出结果为：

id	name	goods
1	张三	商品1
1	张三	商品2
2	李四	商品3
3	王五	商品4
3	王五	商品5

这里使用了INNER JOIN语句，将user表和order表按照user_id字段进行关联查询，并将结果输出。

如果想要将每个用户对应的订单列表以一行结果输出，可以在SELECT语句中使用GROUP_CONCAT函数：

SELECT u.id, u.name, GROUP_CONCAT(o.goods SEPARATOR '|') as goods FROM user u INNER JOIN order o ON u.id = o.user_id GROUP BY u.id;

输出结果为：

id	name	goods
1	张三	商品1\|商品2
2	李四	商品3
3	王五	商品4\|商品5

可以看到，每个用户对应的订单列表已经以“|”分隔符连接在了一起。

用户自定义变量

用户自定义变量是指以@开头的变量，可以在一个SQL语句中定义，然后在另一个SQL语句中引用。对于想要查询多行数据以一行结果输出的情况，用户自定义变量也非常有用。

下面是一个例子，首先建立一个测试表：

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

向表中插入一些数据：

INSERT INTO `score` (`student`, `subject`, `score`) VALUES ('小明','语文',90),('小明','数学',85),('小明','英语',92),('小红','语文',88),('小红','数学',91),('小红','英语',87);

如果想要查询每个学生的总分并以一行结果输出，可以这样写：

SELECT
  student,
  @sum:=SUM(score) AS sum
FROM
  score, (SELECT @sum:=0) AS tmp
GROUP BY student;

输出结果为：

student	sum
小明	267
小红	266

这里使用了用户自定义变量@sum，首先在一个子查询中进行初始化（这里初始化为0），然后在SELECT语句中对每个学生的总分进行累加，并将结果输出。

注意，为了防止变量重名，在定义变量时最好使用特定的前缀，如@sum、@total等。

小结

MySQL提供了许多操作技巧，能够方便地将多行数据以一行结果输出，对于一些特定的应用场景非常有用。其中，GROUP_CONCAT函数能够将一组值连接成一个字符串；SQL内联查询能够在一个SQL语句中嵌套另一个SQL语句，通常用于查询多个表或者多个子查询的数据；用户自定义变量能够方便地在一个SQL语句中定义变量，并在另一个SQL语句中引用。