NAVICAT PREMIUM 初识

1 问题运行SQL取数语句出错？。
答案：因为运行SQL文件时没有这个表。新的数据库里面
解决方法：
　　获取需要建立的二维表创建语句
新建查询
输入语句，点击运行即可
解释注释语句：
主体为五个字段的主表。接下来都是添加的类似需要的计算字段。
重置为子表与主表进行关联取数。一个字段为一个表。
#控奶新客：T-14至T-7内购买大于等于1，且T-14至T-194（最远到7月）未购控奶

#T为1月13日;

DELETE

FROM

    `0114-0120控奶新客`;

#写入会员基本信息

INSERT INTO `0114-0120控奶新客` (

    门店ID,

    门店,

    会员姓名,

    手机号码,

    控奶首购时间

) SELECT

    *

FROM

    (

        SELECT

            a.salesdepart_id AS 门店ID,

            b.`name` AS 门店,

            c.`name` AS 会员姓名,

            c.mobile AS 手机号码,

            MIN(d.create_date) AS `控奶首购时间`

        FROM

            arm_changsha.goods_sales AS a

        LEFT JOIN arm_changsha.sys_office AS b ON b.id = a.salesdepart_id      # 表关联信息。逻辑表关联

        LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = a.member_id

        LEFT JOIN arm_changsha.goods_sales_detail AS d ON d.sales_id = a.id

        LEFT JOIN arm_changsha.goods_base AS e ON e.id = d.goods_id

        WHERE

            a.sales_type = ''

        AND d.barcode IN (

            SELECT

                条码

            FROM

                `tmp_通货`

            WHERE

                商品标签 = '非通货'

            AND 大类 LIKE '01%'

        )

        AND c.mobile NOT IN (

            SELECT

                mobile

            FROM

                `7~9月英克系统消费客户统计`

        )

        AND c.mobile IS NOT NULL

        GROUP BY

            c.mobile

    ) AS L

WHERE

    `控奶首购时间` >= '2018-12-31 00:00:00'

AND `控奶首购时间` <= '2019-01-06 23:59:59';

#写入首购购买数量

UPDATE `0114-0120控奶新客` AS a

SET a.`控奶首购数量` = (

    SELECT

        sum(b.sales_number) AS 购买数量

    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

    LEFT JOIN arm_changsha.goods_base AS e ON e.id = b.goods_id

    WHERE

        c.mobile = a.手机号码

    AND b.create_date = a.控奶首购时间

    AND e.kind_id LIKE '01%'

    AND d.sales_type = ''

    AND b.barcode IN (

        SELECT

            条码

        FROM

            `tmp_通货`

        WHERE

            商品标签 = '非通货'

        AND 大类 LIKE '01%'

    )

);

#写入T+7控货奶粉购买数量

UPDATE `0114-0120控奶新客` AS a

SET a.`T+7控奶购买数量` = (

    SELECT

        sum(b.sales_number) AS 购买数量

    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-01-21 00:00:00'

    AND b.create_date <= '2019-01-27 23:59:59'

    AND d.sales_type = ''

    AND b.barcode IN (

        SELECT

            条码

        FROM

            `tmp_通货`

        WHERE

            商品标签 = '非通货'

        AND 大类 LIKE '01%'

    )

);

#写入T+7复购金额

UPDATE `0114-0120控奶新客` AS a

SET 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-21 00:00:00'

    AND b.create_date <= '2019-01-27 23:59:59'

    AND b.sales_type = ''

    GROUP BY

        b.member_id

);

#写入T+7购买次数

UPDATE `0114-0120控奶新客` AS a

SET 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-01-21 00:00:00'

    AND b.create_date <= '2019-01-27 23:59:59'

    AND d.sales_type = ''

);

#写入近90日充值金额

UPDATE `0114-0120控奶新客` AS a

SET a.`近90日充值金额` = (

    SELECT

        ROUND(

            sum(b.use_recharge_balance) / 10000,

            2

        ) AS 充值金额

    FROM

        arm_changsha.ms_member_card_log AS b

    LEFT JOIN arm_changsha.ms_member_def AS c ON c.id = b.member_id

    LEFT JOIN arm_changsha.ms_acct_book_def AS d ON d.member_id = b.member_id

    WHERE

        c.mobile = a.手机号码

    AND d.acct_id IS NOT NULL

    AND b.oper_type IN ('', '', '')

    AND b.create_date >= '2018-10-16 00:00:00'

    AND b.create_date <= '2019-01-13 23:59:59'

);

#写入近90天控货奶粉购买数量

UPDATE `0114-0120控奶新客` AS a

SET a.`近90日控奶购买数量` = (

    SELECT

        sum(b.sales_number) AS 购买数量

    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 >= '2018-10-16 00:00:00'

    AND b.create_date <= '2019-01-13 23:59:59'

    AND d.sales_type = ''

    AND b.barcode IN (

        SELECT

            条码

        FROM

            `tmp_通货`

        WHERE

            商品标签 = '非通货'

        AND 大类 LIKE '01%'

    )

);

#判断是否运营门店

UPDATE `0114-0120控奶新客` AS a

SET a.`是否运营门店` = CASE

WHEN a.门店ID IN (

    '',

    '',

    '',

    ''

) THEN

    '是'

ELSE

    '否'

END;

#判断是否稳定

UPDATE `0114-0120控奶新客` AS a

SET a.`是否稳定` = CASE

WHEN a.近90日控奶购买数量 >= ''

OR a.近90日充值金额 >= '' THEN

    '是'

ELSE

    '否'

END;
表关联逻辑，对应键值。
巴特西

NAVICAT PREMIUM 初识

最新文章

热门文章
