sql 动态查询--查询条件为空时----该条件不作为查询条件

原创已于 2022-02-09 17:16:43 修改 · 2.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #database

于 2022-02-07 17:49:18 首次发布

该SQL查询用于统计特定部门（根据${bm}


```sql
SELECT
	account_name AS ryxm,
	machine_name AS '设备号',
	dept_name AS '部门',
	account_id AS '账号',
	user_type AS '身份类型',
	sum( CASE time_name WHEN '早餐' THEN cs ELSE 0 END ) '早餐_次数',
	sum( CASE time_name WHEN '早餐' THEN je ELSE 0 END ) '早餐_金额',
	sum( CASE time_name WHEN '午餐' THEN cs ELSE 0 END ) '午餐_次数',
	sum( CASE time_name WHEN '午餐' THEN je ELSE 0 END ) '午餐_金额',
	sum( CASE time_name WHEN '加班餐' THEN cs ELSE 0 END ) '加班餐_次数',
	sum( CASE time_name WHEN '加班餐' THEN je ELSE 0 END ) '加班餐_金额' 
FROM
	(
	SELECT
		account_name,
		machine_name,
		time_name,
		dept_name,
		account_id,
		user_type,
		count( * ) AS cs,
		sum( price ) AS je 
	FROM
		(
		SELECT
			* 
		FROM
			(
			SELECT
				a.account_name,
				a.time_name,
				b.machine_name,
				price,
				dept_name,
				account_id,
				user_type,
				a.create_time 
			FROM
				ykt_order a
				LEFT JOIN ykt_machine b ON a.machine_no = b.machine_no 
			WHERE
				a.type = '2' 
and (dept_name='${bm}' OR '${bm}'IS NULL OR  '${bm}'='') 
and (user_type='${sflx}' OR '${sflx}'IS NULL OR  '${sflx}'='') 

			) c 
		WHERE
			time_name IS NOT NULL 
AND create_time >= '${sj_bg}' 
	AND create_time <= '${sj_ed}' 
		) z 
	GROUP BY
		account_name,
		machine_name,
		time_name 
	) zz 
GROUP BY
	account_name,
	machine_name

在这里插入在这里插入图片描述
图片描述



SELECT * FROM table
where 1 = 1
and ((@A IS NULL) or (A=@A) or @A = '')
and ((@B IS NULL) or (B=@B) or @B= '')
END

@A是传入的参数