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