一、CONCAT()函数 CONCAT()函数用于将多个字符串连接成一个字符串。 使用数据表Info作为示例,其中SELECT id,name FROM info LIMIT 1;的返回结果 为 +----+--------+ | id | name | +----+--------+ | 1 | BioCyc | +----+--------+ 1、语法及使用特点
create table sales (
`year` int not null,
`country` varchar(20) not null,
`product` varchar(32) not null,
`profit` int
);
insert into sales values(2004, "china", "tnt1", 2001);
insert into sales values(2004, "china", "tnt2", 2002);
insert into sales values(2004, "china", "tnt3", 2003);
insert into sales values(2005, "china", "tnt1", 2004);
insert into sales values(2005, "china", "tnt2", 2005);
insert into sales values(2005, "china", "tnt3", 2006);
insert into sales values(2005, "china", "tnt1", 2007);
insert into sales values(2005, "china", "tnt2", 2008);
insert into sales values(2005, "china", "tnt3", 2009);
insert into sales values(2006, "china", "tnt1", 2010);
insert into sales values(2006, "china", "tnt2", 2011);
insert into sales values(2006, "china", "tnt3", 2012);
select year, country, product, sum(profit) from sales group by year, country, product;
+------+---------+---------+-------------+
| year | country | product | sum(profit) |
+------+---------+---------+-------------+
| 2004 | china | tnt1 | 2001 |
| 2004 | china | tnt2 | 2002 |
| 2004 | china | tnt3 | 2003 |
| 2005 | china | tnt1 | 4011 |
| 2005 | china | tnt2 | 4013 |
| 2005 | china | tnt3 | 4015 |
| 2006 | china | tnt1 | 2010 |
| 2006 | china | tnt2 | 2011 |
| 2006 | china | tnt3 | 2012 |
+------+---------+---------+-------------+
9 rows in set (0.00 sec)
select year, country, product, sum(profit) from sales group by year, country, product with rollup;
+------+---------+---------+-------------+
| year | country | product | sum(profit) |
+------+---------+---------+-------------+
| 2004 | china | tnt1 | 2001 |
| 2004 | china | tnt2 | 2002 |
| 2004 | china | tnt3 | 2003 |
| 2004 | china | NULL | 6006 |
| 2004 | NULL | NULL | 6006 |
| 2005 | china | tnt1 | 4011 |
| 2005 | china | tnt2 | 4013 |
| 2005 | china | tnt3 | 4015 |
| 2005 | china | NULL | 12039 |
| 2005 | NULL | NULL | 12039 |
| 2006 | china | tnt1 | 2010 |
| 2006 | china | tnt2 | 2011 |
| 2006 | china | tnt3 | 2012 |
| 2006 | china | NULL | 6033 |
| 2006 | NULL | NULL | 6033 |
| NULL | NULL | NULL | 24078 |
+------+---------+---------+-------------+
16 rows in set (0.00 sec)
注意: Rollup 与 order by 相互排拆
select year, country, product, sum(profit) from sales group by year, country, product with rollup order by product;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY