sql中的条件判断、循环、统计

1、统计呼叫记录表，根据时间、单位查询统计呼叫次数，传递数据为多个单位的id

先统计出需要查询单位的次数

传递参数：

{
    "startTimeDate": "2023-03-01",
    "endTimeDate": "2023-03-13",
    "startTimePoint": "00:00:00",
    "endTimePoint": "23:09:25",
    "deviceCallType": "",
    "deptIds": [
        "61f76078f7404d65b85ef838f0158588",
        "efce27434e154162ad0a05e8f5880f1d",
        "eede1f32a19f42f08bef184b2ce735e1",
        "34aee31756ce40678b62d06cf85bf2ae",
        "dd689cfe0d6b4806a85a30212c89ea64",
        "d88d008298014f26a782603b9719347d",
        "6dd65d1bd4644504bfbd1956c2ce45cd",
        "de97f3494ee644ab9b8d424632bd0731"
    ]
}

 

mybatis查询语句

select count(1) as counts,deptId from rtc_call_log              
where 
DATE_FORMAT(time_callercallout,'%Y-%m-%d') <![CDATA[>= ]]> #{startTimeDate}               
AND DATE_FORMAT(time_callercallout,'%Y-%m-%d') <![CDATA[<= ]]> #{endTimeDate}               
AND DATE_FORMAT(time_callercallout,'%H:%i:%s') <![CDATA[>= ]]> #{startTimePoint}               
AND DATE_FORMAT(time_callercallout,'%H:%i:%s') <![CDATA[<= ]]> #{endTimePoint}              
<if test=\deviceCallType != null and deviceCallType != '' \> and deviceCallType = #{deviceCallType} </if>              
and dealTime is not null and dealTime <![CDATA[<> ]]> ''               
and deptId in 
<foreach collection='deptIds' open='(' item='item' separator=',' close=')'>#{item}</foreach>
group by deptId

sql：

SELECT
		count(1) AS counts,
		deptId
	FROM
		rtc_call_log
	WHERE
		DATE_FORMAT(
			time_callercallout,
			'%Y-%m-%d'
		) >= "2023-03-01"
	AND DATE_FORMAT(
		time_callercallout,
		'%Y-%m-%d'
	) <= "2023-03-13"
	AND DATE_FORMAT(
		time_callercallout,
		'%H:%i:%s'
	) >= "00:00:00"
	AND DATE_FORMAT(
		time_callercallout,
		'%H:%i:%s'
	) <= "23:09:25"
	AND dealTime IS NOT NULL
	AND dealTime <> ''
	AND deptId IN (
		"61f76078f7404d65b85ef838f0158588",
		"efce27434e154162ad0a05e8f5880f1d",
		"eede1f32a19f42f08bef184b2ce735e1",
		"34aee31756ce40678b62d06cf85bf2ae",
		"dd689cfe0d6b4806a85a30212c89ea64",
		"d88d008298014f26a782603b9719347d",
		"6dd65d1bd4644504bfbd1956c2ce45cd",
		"de97f3494ee644ab9b8d424632bd0731"
	)
	GROUP BY
		deptId

　　

 

 

 2、查询结果联查单位表，关联单位名称并统计

@Select(" <script>select  case when b.counts is null then '0' else b.counts end as num,a.deptName  as 'name' ,a.deptId from org_dept a " +             " LEFT JOIN  " +             " (select count(1) as counts,deptId from rtc_call_log " +             " where DATE_FORMAT(time_callercallout,'%Y-%m-%d') <![CDATA[>= ]]> #{startTimeDate} " +             " AND DATE_FORMAT(time_callercallout,'%Y-%m-%d') <![CDATA[<= ]]> #{endTimeDate} " +             " AND DATE_FORMAT(time_callercallout,'%H:%i:%s') <![CDATA[>= ]]> #{startTimePoint} " +             " AND DATE_FORMAT(time_callercallout,'%H:%i:%s') <![CDATA[<= ]]> #{endTimePoint}" +             " <if test=\"deviceCallType != null and deviceCallType != '' \"> and deviceCallType = #{deviceCallType} </if>" +             " and dealTime is not null and dealTime <![CDATA[<> ]]> '' " +             " and deptId in <foreach collection='deptIds' open='(' item='item' separator=',' close=')'>#{item}</foreach> " +             " group by deptId) as b " +             " on a.deptId = b.deptId " +             " where  a.deptId in <foreach collection='deptIds' open='(' item='item' separator=',' close=')'>#{item}</foreach> " +             " order by a.createTime desc " +             "</script> ") 

SELECT
    CASE
WHEN b.counts IS NULL THEN
    '0'
ELSE
    b.counts
END AS num,
 a.deptName AS 'name',
 a.deptId
FROM
    org_dept a
LEFT JOIN (
    SELECT
        count(1) AS counts,
        deptId
    FROM
        rtc_call_log
    WHERE
        DATE_FORMAT(
            time_callercallout,
            '%Y-%m-%d'
        ) >= "2023-03-01"
    AND DATE_FORMAT(
        time_callercallout,
        '%Y-%m-%d'
    ) <= "2023-03-13"
    AND DATE_FORMAT(
        time_callercallout,
        '%H:%i:%s'
    ) >= "00:00:00"
    AND DATE_FORMAT(
        time_callercallout,
        '%H:%i:%s'
    ) <= "23:09:25"
    AND dealTime IS NOT NULL
    AND dealTime <> ''
    AND deptId IN (
        "61f76078f7404d65b85ef838f0158588",
        "efce27434e154162ad0a05e8f5880f1d",
        "eede1f32a19f42f08bef184b2ce735e1",
        "34aee31756ce40678b62d06cf85bf2ae",
        "dd689cfe0d6b4806a85a30212c89ea64",
        "d88d008298014f26a782603b9719347d",
        "6dd65d1bd4644504bfbd1956c2ce45cd",
        "de97f3494ee644ab9b8d424632bd0731"
    )
    GROUP BY
        deptId
) AS b ON a.deptId = b.deptId
WHERE
    a.deptId IN (
        "61f76078f7404d65b85ef838f0158588",
        "efce27434e154162ad0a05e8f5880f1d",
        "eede1f32a19f42f08bef184b2ce735e1",
        "34aee31756ce40678b62d06cf85bf2ae",
        "dd689cfe0d6b4806a85a30212c89ea64",
        "d88d008298014f26a782603b9719347d",
        "6dd65d1bd4644504bfbd1956c2ce45cd",
        "de97f3494ee644ab9b8d424632bd0731"
    )
ORDER BY
    a.createTime DESC

 

 3、【1】sql循环写法

deptId IN (
        "61f76078f7404d65b85ef838f0158588",
        "efce27434e154162ad0a05e8f5880f1d",
        "eede1f32a19f42f08bef184b2ce735e1",
        "34aee31756ce40678b62d06cf85bf2ae",
        "dd689cfe0d6b4806a85a30212c89ea64",
        "d88d008298014f26a782603b9719347d",
        "6dd65d1bd4644504bfbd1956c2ce45cd",
        "de97f3494ee644ab9b8d424632bd0731"
    )

【2】sql条件判断

SELECT
    CASE
WHEN b.counts IS NULL THEN
    '0'
ELSE
    b.counts
END AS num,