SQL的自定义变量用法（适用于navicat对接的mysql）

标题SQL的自定义变量用法（适用于navicat对接的mysql）

** 注意SET @变量用法只支持原生mysql，因为navicat链接的mysql不支持回传服务器，如果一定要用，可以使用python+pymysql注入sql语句**

基本用法：

直接定义一列值

SELECT @x := 1, @y := 2

sql自定义变量

循环递增

此种用法可以在mysql中进行模拟排序，原理是：新定义一个变量列，强制链接到查询的表中，再查询时定义的变量不断让自己+1递增即可，这里+任何数都可以，可以根据实际情况做很多变化性操作。

SELECT
	@x := @x+1,
	amount
FROM 2019order
INNER JOIN (SELECT @x := 0) as tb1 ON 1=1

MySQL自定义变量递增

统一传入参数

对于复杂的sql查询，可能需要在各种连表或子查询中传入相同参数，参数可能需要调节，此时可以通自定义变量一次传入，比如下面这次RFM模型数据查询，最后一次传入@date参数

SELECT
	R.user_id,
	R.Rencency,
	F.Frequency,
	M.Monetary 
FROM
	(
	SELECT
		user_id,
		DATEDIFF( R1.Rencency, @date ) AS Rencency 
	FROM
		(
		SELECT
			user_id,
			MAX( create_time ) AS Rencency 
		FROM
			`order` 
		WHERE
			`status` = 2 
			AND platform_id IN { platformid } 
			AND create_time BETWEEN DATE_SUB( @date, INTERVAL 365 DAY ) AND @date 
		GROUP BY
			user_id 
		ORDER BY
			Rencency DESC 
		) AS R1 
	) AS R
	LEFT JOIN (
	SELECT
		user_id,
		F1.Frequency 
	FROM
		(
		SELECT
			user_id,
			COUNT(
			DISTINCT LEFT ( create_time, 7 )) AS Frequency 
		FROM
			`order` 
		WHERE
			`status` = 2 
			AND platform_id IN { platformid } 
			AND create_time BETWEEN DATE_SUB( @date, INTERVAL 365 DAY ) AND @date 
		GROUP BY
			user_id 
		ORDER BY
			Frequency 
		) AS F1 
	) AS F ON R.user_id = F.user_id
	LEFT JOIN (
	SELECT
		user_id,
		M1.Monetary 
	FROM
		(
		SELECT
			user_id,
			SUM( original_price / 100 ) AS Monetary 
		FROM
			`order` 
		WHERE
			`status` = 2 
			AND platform_id IN { platformid } 
			AND create_time BETWEEN DATE_SUB( @date, INTERVAL 365 DAY ) AND @date 
		GROUP BY
			user_id 
		ORDER BY
			Monetary 
		) AS M1 
	) AS M ON R.user_id = M.user_id
	INNER JOIN ( SELECT @date := '{date}' ) AS date_x ON 1 =1