MySQL求投资人在第二次投资所在的那个月的所有投资金额

    工作中遇到了个问题：要用MySQL求投资人在第二次投资所在的那个月的所有投资金额（以下简称二投金额）。

举例说明：fff4d7d1752f4950b088c5b550a82ac9的二投金额，即为2016-02月份所有投资金额，也就是1500元。如下图所示：

那么怎么写SQL呐？先说一下求一个人的二投金额的思路。首先取出这人的最小投资月份，即2015-12，然后求大于2015-12的所有月份，即2016-02和2016-03，然后对2016-02和2016-03取最小，即求出来了第二次投资所在的那个月—2016-02，然后将其作为条件，对该月所有的投资金额求和。详细SQL如下：

SELECT
	SUM(INVESTAMOUNT) AS FIRST_MONTH_INVEST_MONEY,
	INVESTAMOUNT AS FIRST_INVEST_MONEY,
	INVESTOR AS INVSTOR
FROM
	DW_ODS.ODS_HZCF_T_INVEST
WHERE
	DATE_FORMAT(INVESTTIME, '%Y%m') = (
		SELECT
			MIN(
				DATE_FORMAT(INVESTTIME, '%Y%m')
			)
		FROM
			DW_ODS.ODS_HZCF_T_INVEST
		WHERE
			DATE_FORMAT(INVESTTIME, '%Y%m') > (
				SELECT
					MIN(
						DATE_FORMAT(INVESTTIME, '%Y%m')
					)
				FROM
					DW_ODS.ODS_HZCF_T_INVEST
				WHERE
					FLAG = '30007001002'
				AND INVESTOR = 'fff4d7d1752f4950b088c5b550a82ac9'
			)
		AND FLAG = '30007001002'
		AND INVESTOR = 'fff4d7d1752f4950b088c5b550a82ac9' #fff4d7d1752f4950b088c5b550a82ac9)
	)
AND FLAG = '30007001002'
AND INVESTOR = 'fff4d7d1752f4950b088c5b550a82ac9'; 

。下面看一下求所有用户的二投金额。先按求一个人的二投金额的方式试试。SQL如下：

SELECT
	SUM(INVESTAMOUNT) AS FIRST_MONTH_INVEST_MONEY,
	INVESTAMOUNT AS FIRST_INVEST_MONEY,
	INVESTOR AS INVSTOR,
	FLAG AS FLG
FROM
	DW_ODS.ODS_HZCF_T_INVEST
WHERE
	(
		INVESTOR,
		DATE_FORMAT(INVESTTIME, '%Y%m'),
		FLAG
	) = (
		SELECT
			INVESTOR AS MININVEST,
			MIN(
				DATE_FORMAT(INVESTTIME, '%Y%m')
			) AS MINTIME,
			FLAG AS MINFLG
		FROM
			DW_ODS.ODS_HZCF_T_INVEST
		WHERE
			DATE_FORMAT(INVESTTIME, '%Y%m') > (
				SELECT
					INVESTOR AS MININVESTOR,
					MIN(
						DATE_FORMAT(INVESTTIME, '%Y%m')
					) AS MINTIME,
					FLAG AS MINFLAG
				FROM
					DW_ODS.ODS_HZCF_T_INVEST
				GROUP BY
					MININVESTOR
				HAVING
					MINFLAG = '30007001002'
			)
		GROUP BY
			MININVEST
		HAVING
			MINFLG = '30007001002'
	)
GROUP BY
	INVSTOR
HAVING
	FLG = '30007001002';

显然，这样写会出很多问题。那么该怎么写呐？下面讲两种方法来实现上述需求。

1、子查询+临时表

SQL如下：

DROP TABLE TEM_1;

#求所有人首次投资的月份
CREATE TEMPORARY TABLE TEM_1 SELECT
	INVESTOR AS MININVESTOR,
	MIN(
		DATE_FORMAT(INVESTTIME, '%Y%m')
	) AS MINTIME,
	FLAG AS MINFLAG
FROM
	DW_ODS.ODS_HZCF_T_INVEST
GROUP BY
	MININVESTOR
HAVING
	MINFLAG = '30007001002';

DROP TABLE TEM_2;

#求所有人二投所在的月份
CREATE TEMPORARY TABLE TEM_2 SELECT
	INVESTOR AS MININVESTOR,
	MIN(
		DATE_FORMAT(INVESTTIME, '%Y%m')
	) AS MINTIME,
	FLAG AS MINFLAG
FROM
	DW_ODS.ODS_HZCF_T_INVEST AS B
WHERE
	DATE_FORMAT(INVESTTIME, '%Y%m') > (
		SELECT
			A.MINTIME AS MINTIM
		FROM
			(
				SELECT
					MININVESTOR AS MININVEST,
					MINTIME
				FROM
					TEM_1
			) AS A
		WHERE
			A.MININVEST = B.INVESTOR
	)
GROUP BY
	INVESTOR
HAVING
	FLAG = '30007001002';

#求所有人的二投
SELECT
	INVESTOR,
	SUM(INVESTAMOUNT)
FROM
	DW_ODS.ODS_HZCF_T_INVEST AS C
JOIN TEM_2 AS B ON C.INVESTOR = B.MININVESTOR
AND DATE_FORMAT(C.INVESTTIME, '%Y%m') = B.MINTIME
AND C.FLAG = '30007001002'
GROUP BY
	C.INVESTOR;

2、嵌套查询+临时表

SQL如下：

DROP TABLE TEM_3;

#查询出所有人二投所在的月份，放入临时表-TEM_3
CREATE TEMPORARY TABLE TEM_3 SELECT
	INVEST AS INVET,
	MIN(
		DATE_FORMAT(INVSTTIM, '%Y%m')
	) AS MININVETIM,
	FLG
FROM
	(
		SELECT
			A.INVESTTIME AS INVSTTIM,
			A.INVESTOR AS INVEST,
			A.FLAG AS FLG
		FROM
			DW_ODS.ODS_HZCF_T_INVEST AS A,
			(
				#查询出所有人首次投资所在的月份
				SELECT
					INVESTOR,
					DATE_FORMAT(MIN(INVESTTIME), '%Y%m') AS MINMONTH,
					FLAG
				FROM
					DW_ODS.ODS_HZCF_T_INVEST
				GROUP BY
					INVESTOR
				HAVING
					FLAG = '30007001002'
			) AS B
		WHERE
			A.INVESTOR = B.INVESTOR
		AND DATE_FORMAT(A.INVESTTIME, '%Y%m') > B.MINMONTH
	) AS C
GROUP BY
	INVEST
HAVING FLG = '30007001002';


#用TEM_3中的投资时间作为连接D表和E表的一个条件，算出所有人的二投
SELECT
	INVESTOR,
	SUM(INVESTAMOUNT)
FROM
	DW_ODS.ODS_HZCF_T_INVEST AS D
JOIN TEM_3 AS E ON D.INVESTOR = E.INVET
AND DATE_FORMAT(D.INVESTTIME, '%Y%m') = E.MININVETIM
AND D.FLAG = '30007001002'
GROUP BY
	INVESTOR;

    结语：经测试，第一种方法的效率更高些。表里共有8000多条数据，第一种方法耗时7秒多，第二种方法耗时9秒多。因此，推荐使用第一种方法，即用临时表。