SQL语句常用集锦

1.根据查询字段结果不同，显示不同字段

        SELECT d.ID AS deptId, d.NAME AS deptName, d.PARENT_ID AS parentId,d.PARENT_IDS AS parentIdsStr,
        pare.NAME deptParentName,p.PRESENT_POSITION presentPosition,
        COUNT(IF(p.PRESENT_POSITION = 1, 1, NULL)) AS headProducePersonNum,
        COUNT(IF(p.PRESENT_POSITION = 2, 1, NULL)) AS producePersonNum,
        COUNT(p.id) AS presentPersonNum
        FROM department d
        LEFT JOIN department pare ON d.PARENT_ID = pare.id
        LEFT JOIN person p ON d.id = p.dept_id AND p.IS_PRESENT = 1 AND  p.PRESENT_POSITION = 2
        GROUP BY d.id,p.PRESENT_POSITION
        HAVING presentPersonNum <![CDATA[ > ]]>  0
        ORDER BY presentPersonNum DESC, d.id ASC;
//字段值大小比较，显示不同结果
SELECT
	tr.PERSON_CODE,
	tr.PERSON_NAME,
	tr.SWING_TIME,
	tr.enterNum,
	tr.exitNum,
CASE WHEN tr.enterNum > tr.exitNum THEN '1' 
ELSE '0'  
END AS isPresent
FROM
	(
SELECT
	PERSON_CODE,
	PERSON_NAME,
	SWING_TIME,
	OPEN_RESULT,
	ENTER_OR_EXIT,
	COUNT( IF ( ENTER_OR_EXIT = 1, 1, NULL ) ) AS enterNum,
	COUNT( IF ( ENTER_OR_EXIT = 2, 1, NULL ) ) AS exitNum 
FROM
	acs_swing_card_record_202504 
WHERE
	PERSON_CODE IS NOT NULL 
	AND OPEN_RESULT = 1 
	AND DATE_FORMAT( SWING_TIME, '%Y-%m-%d' ) = '2025-04-09' 
GROUP BY
	PERSON_CODE 
	) tr

2. ifnull 函数的使用

  select
        ds.type storeType,
        dr.type rtm,
        ifnull( dga.code ,dr.code) agentCode,
        ifnull(dga.agent_name_cn ,dr.name_cn) agentName,
        dr.code resellerCode,
        dr.name_cn resellerName,
        ds.code code
        FROM dg_store ds
        LEFT JOIN dg_store_type dst ON dst.id = ds.type
        LEFT JOIN dg_reseller dr ON dr.code = ds.reseller_code
        LEFT JOIN dg_general_agent dga ON dga.id = dr.agent_id

3. sum 合并列结果计算

SELECT SUM(quantity + price) AS total_sum