MySql 查询

1.分割字符串返回表对象

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',',',help_topic_id+1),',',-1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH('0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23')-LENGTH(REPLACE('0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',',',''))+1

2.查询分组拼接

SELECT
    `fin_account`.`ID` AS `ID`,
    `fin_account`.`AccountNo` AS `收款帐号`,
    `fin_account`.`Bank` AS `开户行`,
    `fin_account`.`AccountName` AS `账户名`,
    ( SELECT GROUP_CONCAT( b.`Name` ) FROM `fin_boss` AS b 
        INNER JOIN fin_boss_account ba on b.ID=ba.BossID 
        WHERE ba.AccountID = `fin_account`.ID ) AS `老板` ,
    `fin_account`.`Remark` AS `备注`,(
    CASE
            WHEN ( `fin_account`.`Enable` = 1 ) THEN
            '启用' ELSE '禁用' 
        END 
        ) AS `是否启用` 
    FROM
            `fin_account`
        

 3.SQL 分组后取条件值 最大/最小 的数据行

#按BossID, ProductID, DeptID 分组后，取时间最大的 数据行

SELECT
    ROW_NUMBER() OVER ( ORDER BY id ASC ) AS rowno,
    AAA.* 
FROM
    (
    SELECT
        *,
        ROW_NUMBER() OVER ( PARTITION BY BossID, ProductID, DeptID ORDER BY PriceTime DESC ) AS GroupNum 
    FROM
        fin_boss_price 
    WHERE
        PriceTime >= '2022-07-01 16:46:02' 
        AND PriceTime < '2024-07-04 16:46:02' 
    ) AS AAA 
WHERE
    AAA.GroupNum =1

 4.mysql 根据表注释和字段导出视图

 

SELECT 
    COLUMN_NAME 列名,
    COLUMN_TYPE 数据类型,
    DATA_TYPE 字段类型,
    CHARACTER_MAXIMUM_LENGTH 长度,
    IS_NULLABLE 是否为空,
    COLUMN_DEFAULT 默认值,
    COLUMN_COMMENT 备注,
    CONCAT('`t1`.`',COLUMN_NAME,'` AS ',COLUMN_COMMENT,',') 视图字段
FROM
    information_schema. COLUMNS
WHERE
    TABLE_NAME = 'crm_product'
AND TABLE_SCHEMA = 'tzjcrm';

 

5