mysql如何实现多行查询结果合并成一行,mysql如何实现多行查询结果合并成一行网站简介信息
创新互联-专业网站定制、快速模板网站建设、高性价比聊城网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式聊城网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖聊城地区。费用合理售后完善，十载实体公司更值得信赖。
利用函数：group_concat(),实现一个ID对应多个名称时，原本为多行数据，把名称合并成一行。
其完整语法：
GROUP_CONCAT(expr)
该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
mysql SELECT student_name,
- GROUP_CONCAT(test_score)
- FROM student
- GROUP BY student_name;
Or:
mysql SELECT student_name,
- GROUP_CONCAT(DISTINCT test_score
- ORDER BY test_score DESC SEPARATOR ' ')
- FROM student
- GROUP BY student_name;
在MySQL中,你可以获取表达式组合的连接值。你可以使用DISTINCT删去重复值。假若你希望多结果值进行排序，则应该使用 ORDER BY子句。若要按相反顺序排列，将 DESC (递减) 关键词添加到你要用ORDER BY 子句进行排序的列名称中。默认顺序为升序；可使用ASC将其明确指定。 SEPARATOR 后面跟随应该被插入结果的值中间的字符串值。默认为逗号 (‘,')。通过指定SEPARATOR '' ，你可以删除所有分隔符。
使用group_concat_max_len系统变量，你可以设置允许的最大长度。 程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
SET [SESSION | GLOBAL] group_concat_max_len = val;
MySQL中 concat 函数
使用方法：
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
注意：
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
SELECT CONCAT(CAST(int_col AS CHAR), char_col)
MySQL的concat函数可以连接一个或者多个字符串,如
mysql select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
mysql select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)
MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
mysql select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)
MySQL中 concat_ws 函数
使用方法：
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
如连接后以逗号分隔
mysql select concat_ws(',','11','22','33');
+-------------------------------+
| concat_ws(',','11','22','33') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set (0.00 sec)
和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
mysql select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)
MySQL中 group_concat 函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
基本查询
mysql select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，分号分隔
mysql select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)
以id分组，把去冗余的name字段的值打印在一行，
逗号分隔
mysql select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)
以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
mysql select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)
repeat()函数
用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数
mysql select repeat('ab',2);
+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+
1 row in set (0.00 sec)
又如
mysql select repeat('a',2);
+---------------+
| repeat('a',2) |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)
mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,'',str)
mysql 向表中某字段前加字符串
update table_name set field=CONCAT('str',field)
1、本地数据库连接
Driver={MySQL};Server=localhost;Option=16834;Database=myDataBase;
2、远程数据连接
Driver={MySQL};Server=myServerAddress;Option=131072;Stmt=;Database=myDataBase; User=myUsername;Password=myPassword;
The port 3306 is the default MySql port.
The value is ignored if Unix socket is used.
Use this to connect to a server in a replicated server configuration without concern on which server to use.
Use SSL if the server supports it, but allow connection in all cases
This option is available from Connector/NET version 6.2.1
Always use SSL. Deny connection if server does not support SSL.
This option is available from Connector/NET version 6.2.1
This option is available from Connector/NET version 6.2.1
This option is available from Connector/NET version 6.2.1
This option is available from Connector/NET version 6.2.1
This option is available from Connector/NET version 5.2.2
Returns a MySqlDateTime object for invalid values and a System.DateTime object for valid values
Returns System.DateTime.MinValue valued System.DateTime object for invalid values and a System.DateTime object for valid values.
The use of auto enlist transactionscope (default behaviour) could cause trouble in medium trust environments.
Default behaviour is that parameters for stored routines (stored procedures) are checked against the server
Some permissions and value casting related errors reported fixed when using this connection option.
The default behaviour is to read tables mysql.proc/INFORMATION_SCHEMA.ROUTINES and try to map provided command parameter values to the called procedures parameters and type cast values accordingly.
This can be troublesome if permissions to the (aforementioned) sproc info tables are insufficient.
The driver will not automatically map the parameters so you must manually set parameter types and you must also make sure to add the parameters to the command object in the exact order as appeared in the procedure definition.
This option is available from Connector/NET version 5.0.4
Specifying DefaultTableCacheAge is optional, default value is 60 seconds.
This option is available from Connector/NET version 6.4
This option is available from Connector/NET version 5.2.6
From version 6.2 idle connections are removed from the pool, freeing resources on the client (sockets) and the server (sockets and threads). Do not manually keep (global) connections and open close. Keep connection object creation and disposal as tight as possible, this might be counterintuitive but pooling mechanisms will take care of caching well and your code will be cleaner.
This is the default behaviour.
Default values are 0 and 100.
Makes an additional round trip to the server when obtaining a connection from the pool and connection state will be reset.
This is useful in load balancing scenarios when available servers change you don't want 100 constant connections in the pool pointing to only one server.
Specified in seconds, the amount of time after connection object creation the connection will be destroyed. Destruction will only happen when connections are returned to pool.
A connection might be long lived in the pool, however the connections server settings are updated (SHOW VARIABLES command) each time returned to the pool. This makes the client use of the connection object up to date with the correct server settings. However this causes a round trip and to optimize pooling performance this behaviour can be turned off.
This option is available from Connector/NET version 6.3
This option is available from Connector/NET version 6.4.4
The Windows Native Authentication Plugin must be installed for this to work.
Number of seconds between each keep-alive package send.
This option is available from Connector/NET version 6.1.1
The default is 25, meaning that stored procedure meta data (such as input/output data types etc) for the latest 25 called procedures will be cached in client memory.
This option is available from Connector/NET version 5.0.2
This enables Visual Studio wizards that bracket symbols with [] to work with Connector/Net. This option incurs a performance hit, so should only be used if necessary.
This option is available from Connector/NET version 6.3.1
Use this one to specify a default command timeout for the connection. Please note that the property in the connection string does not supercede the individual command timeout property on an individual command object.
This option is available from Connector/NET version 5.1.4.
Use this one to specify the length in seconds to wait for a server connection before terminating the attempt and receive an error.
Use this one to instruct the provider to ignore any command prepare statements and prevent corruption issues with server side prepared statements.
The option was added in Connector/NET version 5.0.3 and Connector/NET version 1.0.9.
Use this one to specify which network protocol to use for the connection.
"socket" is the default value used if the key isn't specified. Value "tcp" is an equivalent for "socket".
Use "pipe" to use a named pipes connection, "unix" for a Unix socket connection and "memory" to use MySQL shared memory.
It's possible to explicit set the shared memory object name used for communication.
It's possible to explicit set the pipe name used for communication, if not set, 'mysql' is the default value.
It is the port value of -1 that tells the driver to use named pipes network protocol. This is available on Windows only. The value is ignored if Unix socket is used.
It's possible to explicit set the shared memory object name used for communication.
Use this one to specify which character set to use to encode queries sent to the server.
Note! Use lower case value utf8 and not upper case UTF8 as this will fail.
Note that resultsets still are returned in the character set of the data returned.