强哥新周报SQL

因为数据口径的更改,所以.强哥的SQL 比较好用.不会出麻烦. 总共有四个

日常记录下,好好看.

-- 2019年4月核销新客
SELECT
    yzm2.consignee_phone AS `会员手机号码`,
    yzm2.confirm_time AS `线上首购接单时间`,
    yzm2.consign_time AS `线上首购核销时间`,
    hcm.id AS 华创会员ID,
  hcm.create_date AS 华创会员创建时间,
    hcm.fisrt_shopping_time AS 华创会员首购时间
FROM
    (
    SELECT
        tn.id,
        tn.consignee_phone,
        tn.confirm_time,
        MIN( tn.order_time ) AS order_time,
        tn.consign_time 
    FROM
        mabao51.trade_onl AS tn 
    WHERE
        tn.consignee_phone IS NOT NULL
      AND tn.org_id NOT IN (4000, 4004)    
    GROUP BY
        tn.consignee_phone 
    ) AS yzm1,
    (
    SELECT
        tn.id,
        tn.consignee_phone,
        tn.confirm_time,
        MIN( tn.order_time ) AS order_time,
        tn.consign_time 
    FROM
        mabao51.trade_onl AS tn 
    WHERE
        tn.consign_time IS NOT NULL 
        AND tn.consignee_phone IS NOT NULL 
      AND tn.org_id > 0 
      AND tn.org_id NOT IN (4000, 4004)    
    GROUP BY
        tn.consignee_phone 
    ) AS yzm2,
    arm_changsha.ms_member_def AS hcm
WHERE
  hcm.mobile = yzm1.consignee_phone
  AND yzm1.consignee_phone= yzm2.consignee_phone    
    AND hcm.create_date >= yzm1.order_time 
    AND yzm2.consign_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"

四月门店全部新客

-- 2019年4月41家门店全部新客
/*取出所有顾客的最早销售单的时间,然后判断是否在四月.是这个逻辑,首购判断.新客.去除一些沉默客户*/


SELECT
    * 
FROM
    (
    SELECT
        gs.member_id,
        gs.salesdepart_id,
        MIN( gs.sales_date ) sales_date 
    FROM
        arm_changsha.goods_sales AS gs 
    GROUP BY
        gs.member_id 
    ) AS gs2 
    LEFT JOIN arm_changsha.ms_member_def AS m ON m.id = gs2.member_id
  LEFT JOIN (
    SELECT
        tn.org_id_erp
    FROM
        mabao51.trade_onl AS tn 
    WHERE
      tn.org_id > 0 
      AND tn.org_id NOT IN (4000, 4004)    
        AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
    GROUP BY
        tn.org_id_erp
    ) AS yzo ON yzo.org_id_erp = gs2.salesdepart_id
WHERE
    gs2.sales_date BETWEEN "2019-04-01 00:00:00" 
    AND "2019-05-01 00:00:00"
    AND m.create_date > "2019-01-01 00:00:00" 
    AND yzo.org_id_erp IS NOT NULL
    

 

四月全部购买人数

-- 2019年4月41家门店全部购买人

SELECT
    *
FROM
    (
    SELECT
        gs.member_id,
        gs.salesdepart_id,
        gs.sales_date
    FROM
        arm_changsha.goods_sales AS gs 
    ) AS gs2 
  LEFT JOIN (
    SELECT
        tn.org_id_erp
    FROM
        mabao51.trade_onl AS tn 
    WHERE
      tn.org_id NOT IN (4000, 4004)    
        AND tn.org_id > 0
        AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
    GROUP BY
        tn.org_id_erp
    ) AS yzo ON yzo.org_id_erp = gs2.salesdepart_id
WHERE
    gs2.sales_date BETWEEN "2019-04-01 00:00:00" 
    AND "2019-05-01 00:00:00"
    AND yzo.org_id_erp IS NOT NULL
GROUP BY
  gs2.member_id
    

四月全部销售额

-- 2019年4月41家门店销售总额

SELECT
    ROUND(
        (
            SUM( gsd.deal_price * gsd.sales_number ) - IFNULL( SUM( gsp.gift_balance ), 0 ) 
        ) / 10000,
        2 
    ) AS amount 
FROM
    (
    SELECT
        id,
      salesdepart_id
    FROM
        goods_sales 
    WHERE
--         salesdepart_id = "11"
--         AND 
        sales_date BETWEEN "2019-04-01 00:00:00" 
        AND "2019-04-30 23:59:59" 
    ) AS gs
    LEFT JOIN goods_sales_detail AS gsd ON gsd.sales_id = gs.id
    LEFT JOIN goods_sales_pay_detail gsp ON CONCAT( gsp.sales_id, '_', gsp.goods_id ) = CONCAT( gs.id, '_', gsd.goods_id )
    LEFT JOIN  (
        SELECT
            tn.org_id_erp 
        FROM
            mabao51.trade_onl AS tn 
        WHERE
          tn.org_id NOT IN ( 4000, 4004 ) 
          AND tn.org_id > 0
          AND tn.order_time BETWEEN "2019-04-01 00:00:00" AND "2019-05-01 00:00:00"
        GROUP BY
            tn.org_id_erp 
        ) AS tn2 ON tn2.org_id_erp = gs.salesdepart_id
WHERE
  tn2.org_id_erp IS NOT NULL

 

转载于:https://www.cnblogs.com/sakura3/p/10899555.html