-- ALTER TABLE  `大润发数据测试对比` ADD `消费日期` datetime DEFAULT NULLDELETEFROM    `大润发数据测试对比`;DROP TABLE base ;CREATE TEMPORARY TABLE base SELECT    a.salesdepart_id AS 门店ID,    b.`name` AS 会员姓名,    b.mobile AS 手机号码,    b.create_date AS 注册日期,    ROUND(a.real_pay / 10000, 2) AS 消费金额,        a.sales_date AS 消费日期 ,    COUNT(a.id) AS 消费次数FROM    arm_changsha.goods_sales AS aLEFT JOIN arm_changsha.ms_member_def AS b ON b.id = a.member_idWHERE    b.create_date >= '2019-01-17 00:00:00'AND b.create_date <= '2019-01-30 23:59:59'AND a.salesdepart_id = '1008'AND a.sales_type = '1'AND   ROUND(a.real_pay / 10000, 2) > 10GROUP BY    b.mobile;SELECT * FROM base;-- 选取临时表时,直接选取字段.不用写前缀那些东西. 这个最基础的都不知道.也是醉了.服了你.--  业务存在会员在创建的时候没有购买东西,而后在后续的时间内购买的东西 选取的应该是固定时间段内的会员购买量. 一月注册的二月再买东西不应该算在内.#写入会员基本信息INSERT INTO `大润发数据测试对比` (    门店ID,    会员姓名,    手机号码,    注册日期,    消费金额,        消费日期,    消费次数) SELECT    门店ID,     会员姓名,     手机号码,     注册日期,     消费金额,        消费日期 ,     消费次数FROM    base AS aWHERE    消费日期  >= '2019-01-17 00:00:00'AND  消费日期 <= '2019-01-30 23:59:59';/*随机一半人发券UPDATE `大润发数据测试对比` AS aINNER JOIN(SELECT *FROM`大润发2月第二周_copy`ORDER BYRAND(手机号码)LIMIT 0,49)bSET a.`是否发券` = '是'WHEREb.手机号码 = a.手机号码;*/#补充发券日期-- UPDATE `大润发数据测试对比` AS a-- SET a.`发券日期` = '2019-01-14'-- WHERE--     a.`是否发券` = '是';#写入T+7复购金额UPDATE `大润发数据测试对比` AS aSET a.`T+7消费金额` = (    SELECT        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额    FROM        arm_changsha.goods_sales AS b    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-01-31 00:00:00'    AND b.create_date <= '2019-02-13 23:59:59'    AND b.sales_type = '1'    GROUP BY        b.member_id);#写入T+7购买次数UPDATE `大润发数据测试对比` AS aSET a.`T+7消费次数` = (    SELECT        COUNT(DISTINCT b.sales_id)    FROM        arm_changsha.goods_sales_detail AS b    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-02-14 00:00:00'    AND b.create_date <= '2019-02-20 23:59:59'    AND d.sales_type = '1');#写入T+14复购金额UPDATE `大润发数据测试对比` AS aSET a.`T+14消费金额` = (    SELECT        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额    FROM        arm_changsha.goods_sales AS b    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-02-14 00:00:00'    AND b.create_date <= '2019-02-27 23:59:59'    AND b.sales_type = '1'    GROUP BY        b.member_id);#写入T+14购买次数UPDATE `大润发数据测试对比` AS aSET a.`T+14消费次数` = (    SELECT        COUNT(DISTINCT b.sales_id)    FROM        arm_changsha.goods_sales_detail AS b    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-02-14 00:00:00'    AND b.create_date <= '2019-02-27 23:59:59'    AND d.sales_type = '1');#写入T+30复购金额UPDATE `大润发数据测试对比` AS aSET a.`T+30消费金额` = (    SELECT        ROUND(SUM(b.real_pay) / 10000, 2) AS 累计消费金额    FROM        arm_changsha.goods_sales AS b    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-02-14 00:00:00'    AND b.create_date <= '2019-03-15 23:59:59'    AND b.sales_type = '1'    GROUP BY        b.member_id);#写入T+30购买次数UPDATE `大润发数据测试对比` AS aSET a.`T+30消费次数` = (    SELECT        COUNT(DISTINCT b.sales_id)    FROM        arm_changsha.goods_sales_detail AS b    LEFT JOIN arm_changsha.goods_sales AS d ON d.id = b.sales_id    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = d.member_id    WHERE        c.mobile = a.手机号码    AND b.create_date >= '2019-02-14 00:00:00'    AND b.create_date <= '2019-03-15 23:59:59'    AND d.sales_type = '1');

mysql 临时表添加.以及操作案例.

其实最后选取临时表时.直接写字段就好.不用那么麻烦. 说到底还是语法基础太弱.常识都不知道.