sql 行转列 列转行 总结

 

 

 

 

 

 

一、CONCAT（）函数

concat() 函数用于将多个字符串连接成一个字符串。

1、语法及使用特点：

CONCAT(str1,str2,…) 
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。

 

2、使用示例：

 

 

使用函数concat_ws().

语法为： concat_ws(separator,str1,str2,......)

concat_ws() 代表concat with separator, 是 concat()的特殊形式，第一个参数是分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

如SELECT CONCAT_WS('_',id,name) AS con_ws FROM info LIMIT 1;返回结果为

+----------+
| con_ws   |
+----------+
| 1_BioCyc |
+----------+
SELECT CONCAT_WS(',','First name',NULL,'Last Name');返回结果为

| CONCAT_WS(',','First name',NULL,'Last Name') |

三、group_concat()函数

前言：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。

例5：查询该数据并根据性别分组：

select sex, id, username from my_test group by sex;

 

根据性别分组之后，想知道每个分组里面都有哪些数据，那怎么办啊？ ——使用group_concat()

例6：查询数据分组并获取每个组别中详细的数据：

 

3、使用SUBSTRING_INDEX和CROSS JOIN将列里面的的数字都拆分出来，把一行变成一列。

方法一（网上查询的方法）：

建配置表：

CREATE TABLE digits (digit INT(1));
    INSERT INTO digits
    VALUES
        (0),
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9);
    CREATE TABLE sequence (seq INT(3));
    INSERT INTO sequence (
        SELECT
            D1.digit + D2.digit * 10
        FROM
            digits D1
        CROSS JOIN digits D2
    );

配置表sequence的结果为0-99的一列数字：

SQL：

SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(JoinEventIds, ',', seq),
        ',' ,- 1
    ) JoinEventIds
FROM
    sequence
CROSS JOIN user
WHERE
    seq BETWEEN 1
AND (
    SELECT
        1 + LENGTH(JoinEventIds) - LENGTH(
            REPLACE (JoinEventIds, ',', '')
        )
)

方法二（自己不想建表，图省事）：将sequence替换为SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user  LIMIT 0,100) ，user为表名，这张表需要大于100条。

SELECT  SUBSTRING_INDEX(
            SUBSTRING_INDEX(JoinEventIds, ',', seq),
            ',' ,- 1
        ) JoinEventIds
    FROM
        (SELECT @rownum:=@rownum+1 AS seq FROM (SELECT @rownum:=0) r, user  LIMIT 0,100) b
    CROSS JOIN user
    WHERE
        seq BETWEEN 1
    AND (
        SELECT
            1 + LENGTH(JoinEventIds) - LENGTH(REPLACE(JoinEventIds, ',', '')))

两种方法结果均为：

 

转载于:https://my.oschina.net/u/2425942/blog/3077610