7°

左外链接

CREATE DEFINER=`root`@`%` PROCEDURE `static`(
	IN `yyyy` VARCHAR(12), 
	IN `mm` VARCHAR(8)
)
BEGIN
	DECLARE u_yyyy VARCHAR(8) DEFAULT '2020';
	DECLARE u_mm VARCHAR(8) DEFAULT '01';
	DECLARE u_mm_start VARCHAR(8) DEFAULT '01';
	DECLARE u_mm_end VARCHAR(8) DEFAULT '31';
	DECLARE d_s_time VARCHAR(16) DEFAULT '00:00:00';
	DECLARE d_e_time VARCHAR(16) DEFAULT '23:59:59';
	SELECT IFNULL(yyyy, '2020')
	INTO u_yyyy;
	SELECT IFNULL(mm, '01')
	INTO u_mm;
	SELECT DAY(LAST_DAY(CONCAT(u_yyyy, '-', u_mm, '-01')))
	INTO u_mm_end;
	SELECT five.user_id, five.user_name, five.counter, five.one_shen_shu, five.two_shen_shu
		, six.three_shen_shu
	FROM (
		SELECT three.user_id, three.user_name, three.counter, three.one_shen_shu, four.two_shen_shu
		FROM (
			SELECT one.user_id, one.user_name, one.counter, two.one_shen_shu
			FROM (
				SELECT l.user_id, u.user_name, COUNT(l.user_id) AS counter
				FROM l_lending_information l, l_admin_user u
				WHERE (l.user_id IS NOT NULL
					AND l.user_id = u.user_id
					AND UNIX_TIMESTAMP(l.get_time) 
					BETWEEN UNIX_TIMESTAMP(CONCAT(u_yyyy, '-', u_mm, '-', u_mm_start, ' ', d_s_time)) 
					AND UNIX_TIMESTAMP(CONCAT(u_yyyy, '-', u_mm, '-', u_mm_end, ' ', d_e_time)))
				GROUP BY user_id
			) one
				LEFT JOIN (
					SELECT user_id, COUNT(one_result) AS one_shen_shu
					FROM l_lending_information
					WHERE one_result = 1
						AND (UNIX_TIMESTAMP(one_time) > UNIX_TIMESTAMP(two_time)
							OR two_time IS NULL)
					GROUP BY user_id
				) two
				ON two.user_id = one.user_id
		) three
			LEFT JOIN (
				SELECT user_id, COUNT(two_result) AS two_shen_shu
				FROM l_lending_information
				WHERE two_result = 1
					AND (UNIX_TIMESTAMP(two_time) > UNIX_TIMESTAMP(three_time)
						OR three_time IS NULL)
				GROUP BY user_id
			) four
			ON four.user_id = three.user_id
	) five
		LEFT JOIN (
			SELECT user_id, COUNT(three_result) AS three_shen_shu
			FROM l_lending_information
			WHERE three_result = 1
				AND (UNIX_TIMESTAMP(two_time) > UNIX_TIMESTAMP(three_time)
					OR three_time IS NULL)
			GROUP BY user_id
		) six
		ON six.user_id = five.user_id;
END

 

本文由【赤月三号】发布于开源中国,原文链接:https://my.oschina.net/u/1773772/blog/3157784

全部评论: 0

    我有话说: