MySQL 單筆訂單滿6個及以上產品且金額>=300贈送優惠券_20161103

標籤：strong   rom   銷售   res   count   weight   having   code   使用者id   

主動式內容： 單筆訂單滿6個及以上產品（帽子、浴巾除外），金額滿贈300元，即贈送300-10元（除帽子、浴巾外）優惠券一張。
需求資料：滿足條件的使用者ID
啟用時間：11.2-11.5（4天）
活動規則：
①使用無憂券ID908-913的商戶即便滿足條件也不參與此次活動
② 參與秒殺、滿贈的活動的商戶可以參與此次活動
③ 11.2日、3日資料4日下午發放；11、4日、5日訂單7日發放

 

1.我們可以給他一個最細的明細，每個訂單每個產品下單的情況

SELECT a1.城市,a1.使用者ID,b.使用者名稱稱,b.使用者地址,b.聯絡電話,DATE(訂單日期) AS 訂單日期,a1.訂單ID,a1.銷售人員,a1.產品ID,a1.產品名稱,SUM(a1.金額) AS 金額FROM `a003_order` AS a1LEFT JOIN `a001_resterant` AS b ON a1.使用者ID=b.使用者IDWHERE a1.城市=‘濟南‘ AND a1.訂單日期>="2016-11-02" AND a1.訂單日期<"2016-11-06" AND a1.金額>0 AND (a1.產品名稱 NOT LIKE "%帽子%" OR a1.產品名稱 NOT LIKe"%浴巾%" )GROUP BY a1.訂單ID,a1.產品ID

2、給他一個訂單匯總，其中訂單後在跟隨使用優惠券的情況

SELECT a2.城市,a2.使用者ID,a2.使用者名稱稱,a2.使用者地址,a2.聯絡電話,a2.訂單日期,a2.訂單ID,a2.銷售人員,COUNT(a2.產品ID) AS 產品數,SUM(a2.金額) AS 訂單額,    CASE WHEN COUNT(a2.產品ID)>=6 AND SUM(a2.金額)>=300 THEN "產品數>=6&單筆訂單大於300" ELSE NULL  END AS 標識,a4.優惠券ID,a4.優惠券名稱,a4.優惠券金額    FROM (        SELECT a1.城市,a1.使用者ID,b.使用者名稱稱,b.使用者地址,b.聯絡電話,DATE(訂單日期) AS 訂單日期,a1.訂單ID,a1.銷售人員,a1.產品ID,a1.產品名稱,SUM(a1.金額) AS 金額        FROM `a003_order` AS a1        LEFT JOIN `a001_resterant` AS b ON a1.使用者ID=b.使用者ID        WHERE a1.城市=‘濟南‘ AND a1.訂單日期>="2016-11-02" AND a1.訂單日期<"2016-11-06" AND a1.金額>0 AND (a1.產品名稱 NOT LIKE "%帽子%" OR a1.產品名稱 NOT LIKE        "%浴巾%" )        GROUP BY a1.訂單ID,a1.產品ID    ) AS a2    LEFT JOIN  (#未使用優惠券ID 908到913的使用者訂單        SELECT a3.使用者ID,a3.訂單號,a3.優惠券ID,a3.優惠券名稱,SUM(優惠券金額) AS 優惠券金額        FROM `a016_order_customercoupon_xref` AS a3          WHERE a3.城市="濟南" AND a3.使用時間>="2016-11-02" AND a3.使用時間<"2016-11-06" AND (a3.優惠券ID<908 OR a3.優惠券ID>913)        GROUP BY a3.訂單號    ) AS a4 ON a4.訂單號=a2.訂單ID    GROUP BY a2.訂單ID

3、最後對達標的餐館再進行匯總group by    having 條件式篩選單筆訂單合格

SELECT a.城市,a.使用者ID,a.使用者名稱稱,a.使用者地址,a.聯絡電話,c.銷售人員,SUM(a.訂單額) AS 4天內訂單額,COUNT(a.訂單ID) AS 4天內訂單數,CASE WHEN SUM(a.訂單額)>=300 THEN "10元（除帽子、浴巾外）優惠券一張" ELSE NULL END AS 贈送優惠券FROM (    SELECT a2.城市,a2.使用者ID,a2.使用者名稱稱,a2.使用者地址,a2.聯絡電話,a2.訂單日期,a2.訂單ID,a2.銷售人員,COUNT(a2.產品ID) AS 產品數,SUM(a2.金額) AS 訂單額,    CASE WHEN COUNT(a2.產品ID)>=6 AND SUM(a2.金額)>=300 THEN "產品數>=6&單筆訂單大於300" ELSE NULL  END AS 標識,a4.優惠券ID,a4.優惠券名稱,a4.優惠券金額    FROM (        SELECT a1.城市,a1.使用者ID,b.使用者名稱稱,b.使用者地址,b.聯絡電話,DATE(訂單日期) AS 訂單日期,a1.訂單ID,a1.銷售人員,a1.產品ID,a1.產品名稱,SUM(a1.金額) AS 金額        FROM `a003_order` AS a1        LEFT JOIN `a001_resterant` AS b ON a1.使用者ID=b.使用者ID        WHERE a1.城市=‘濟南‘ AND a1.訂單日期>="2016-11-02" AND a1.訂單日期<"2016-11-06" AND a1.金額>0 AND (a1.產品名稱 NOT LIKE "%帽子%" OR a1.產品名稱 NOT LIKE        "%浴巾%" )        GROUP BY a1.訂單ID,a1.產品ID    ) AS a2    LEFT JOIN  (#未使用優惠券ID 908到913的使用者訂單        SELECT a3.使用者ID,a3.訂單號,a3.優惠券ID,a3.優惠券名稱,SUM(優惠券金額) AS 優惠券金額        FROM `a016_order_customercoupon_xref` AS a3          WHERE a3.城市="濟南" AND a3.使用時間>="2016-11-02" AND a3.使用時間<"2016-11-06" AND (a3.優惠券ID<908 OR a3.優惠券ID>913)        GROUP BY a3.訂單號    ) AS a4 ON a4.訂單號=a2.訂單ID    GROUP BY a2.訂單ID    HAVING COUNT(a2.產品ID)>=6 AND SUM(a2.金額)>=300) AS aLEFT JOIN `a003b_order_item` AS c ON c.使用者ID=a.使用者IDGROUP BY a.使用者IDORDER BY SUM(a.訂單額) DESC 

這樣資料對起來也比較方便 可以很方便的看一個使用者是否是滿足條件達標

MySQL 單筆訂單滿6個及以上產品且金額>=300贈送優惠券_20161103