mysql函数使用记录

1、DATE_FORMAT

格式datetime eg:

DATE_FORMAT(T.BOOKING_ENTRY_TIME_BEGIN,'%Y-%m-%d %H:%i') 

DATE_FORMAT(T.APPLY_TIME,'%Y-%m-%d %H:%i:%s') 

2、CONCAT_WS

拼接函数 eg:

CONCAT_WS ('~',DATE_FORMAT(T.BOOKING_ENTRY_TIME_BEGIN,'%Y-%m-%d %H:%i'),DATE_FORMAT(T.BOOKING_ENTRY_TIME_END,'%H:%i')) AS bookingEntryTime

3、CASE  WHEN 

搜索函数 eg:

( CASE 
    WHEN GROUP_CONCAT(DISTINCT P.`entry_flag`) LIKE '%,%'
  THEN '部分入场'
    ELSE 
  GROUP_CONCAT(DISTINCT P.`entry_flag`)
    END 
) AS entryStatusName,

4、GROUP_CONCAT

有GROUP BY场景，合并多行

GROUP_CONCAT(DISTINCT P.`dangerous_flag`)  AS dangerousFlag

**HAVING后用于查询条件 **

AND GROUP_CONCAT(DISTINCT P.`entry_flag`) LIKE '0,1'
OR GROUP_CONCAT(DISTINCT P.`entry_flag`) LIKE '1,0'

5、CONCAT_WS

**合并多列 **

CONCAT_WS( '',
    DL.`LICENSE`,
    YB.`LICENSE`,
    DZ.`LICENSE`,
    NL.`LICENSE`,
    GC.`LICENSE`,
    DH.`LICENSE`,
    SX.`LICENSE`,
    DT.`LICENSE`,
    LS.`LICENSE`) AS LICENSE

6、DISTINCT 

去重

select DISTINCT name from ***_TABLE

7、EXISTS

用在条件

OR (
    UM.`del_flag` = 1
    AND  (
        SELECT  EXISTS  (
            SELECT  bum.id   FROM  `booking_user_manage` AS bum   WHERE bum.`user_id` = T.USER_ID  AND bum.`del_flag` = 0) < 1         )
 )

用在查询

SELECT 
  T.ID,
  T.RISK_MEASURES_TYPE,
  T.RISK_NAME,
  (SELECT 
    EXISTS 
    (SELECT 
      W.ID 
    FROM
      IWM_WORK_RISK_MEASURES AS W 
    WHERE W.WORK_ID = '0b117f8b0aae5e1e1866b9cd508acd29' 
      AND W.RISK_MEASURES_ID = T.`ID` 
      AND T.`RISK_MEASURES_TYPE` = W.RISK_MEASURES_TYPE)) AS selectEd 
FROM
  IWM_DATA_RISK_MEASURES T 
WHERE 1 = 1 
  AND T.`RISK_MEASURES_TYPE` = 1 

8、使用@用户变量

 <select id="queryRootOrgNoByUserOrgNo" resultType="string">
	SELECT T2.`ORG_NO`
	FROM (
		SELECT @r AS _id,
		(
			SELECT @r := `PARENT_NO` 
			FROM `SYS_ORG` WHERE `ORG_NO` = _id) AS two_v_two,

			@l := @l + 1 AS one_v_one
			FROM (SELECT @r := #{value}, @l := 0) vars,
			`SYS_ORG` h 
			WHERE @r IS NOT NULL
		) T1 
		LEFT JOIN `SYS_ORG` T2 ON T1._id = T2.`ORG_NO` 
	WHERE T2.`PARENT_NO` = '0'
</select>

<select id="queryAllNodeByRootOrgNo" resultType="string">
	SELECT `ORG_NO`
	FROM (
		SELECT
		 T1.`ORG_NO`,IF ( FIND_IN_SET( T1.`PARENT_NO`, @pids ) > 0,
		 @pids := CONCAT( @pids, ',', T1.`ORG_NO` ), 0 ) AS ischild 
		 FROM (
			SELECT `ORG_NO`, `PARENT_NO`
			FROM `SYS_ORG` t ORDER BY `PARENT_NO`, `ORG_NO`
			) T1,
		( SELECT @pids := #{value} ) T2
		) T3
	WHERE ischild NOT LIKE '0'  OR `ORG_NO` = #{value}
</select>