MySQL Single order over 6 products and the amount of >=300 gift coupon _20161103

Activities: A single order over 6 products (excluding hats, bath towels), the amount of 300 yuan, that is, 300-10 yuan (except hats, towels) coupons.
Requirement data: User ID that satisfies the criteria
Activity time: 11.2-11.5 (4 days)
Activity rules:
① use of worry-free voucher id908-913 not participate in the event even if the conditions are met
② participating in the second-kill, full-gift of the event merchants can participate in this event
③11.2 Day, 3rd data 4th afternoon release, 11, 4th, 5th order 7th issued

1. We can give him one of the finest details of each order for each product placed on the case

SELECTA1. City, A1. User id,b. User name, B. user address, B. Contact number, date (order date) asOrder date, A1. Order ID,A1. Sales clerk, A1. Product id,a1. Product Name,SUM(A1. Amount) asAmount from' A003_order ' asA1 Left JOIN' A001_resterant ' asB onA1. User ID=B. User IDWHEREA1. City='Jinan'  andA1. Order Date>=" .- One- Geneva" andA1. Order Date<" .- One- ." andA1. Amount>0  and(A1. Product Name not  like"%Hat%"ORA1. Product Name not  like"%Bath towel%" )GROUP  byA1. Order ID,A1. Product ID

2, give him an order summary, which after the order in following the use of coupons in the case

SELECTA2. City, A2. User id,a2. User name, A2. User address, A2. Contact phone, A2. Order date, A2. Order ID,A2. Sales clerk,COUNT(A2. Product ID) asNumber of products,SUM(A2. Amount) asOrder Amount, Case  when COUNT(A2. Product ID)>=6  and SUM(A2. Amount)>= -  Then"Number of products>=6&Single order greater than 300 "ELSE NULL  END  asidentification, A4. Coupon id,a4. Coupon name, A4. Coupon Amount from (        SELECTA1. City, A1. User id,b. User name, B. user address, B. Contact number, date (order date) asOrder date, A1. Order ID,A1. Sales clerk, A1. Product id,a1. Product Name,SUM(A1. Amount) asAmount from' A003_order ' asA1 Left JOIN' A001_resterant ' asB onA1. User ID=B. User IDWHEREA1. City='Jinan'  andA1. Order Date>=" .- One- Geneva" andA1. Order Date<" .- One- ." andA1. Amount>0  and(A1. Product Name not  like"%Hat%"ORA1. Product Name not  like        "%Bath towel%" )        GROUP  byA1. Order ID,A1. Product ID) asA2 Left JOIN(#未使用优惠券ID 908 to 913 of user ordersSELECTA3. User Id,a3. Order number, A3. Coupon id,a3. Coupon name,SUM(coupon amount) asCoupon Amount from' A016_order_customercoupon_xref ' asA3WHEREA3. City=Jinan andA3. Use time>=" .- One- Geneva" andA3. Use time<" .- One- ." and(A3. Coupon ID<908 ORA3. Coupon ID>913)        GROUP  byA3. Order number) asA4 onA4. Order number=A2. Order IDGROUP  byA2. Order ID

3, the final to the standard restaurant to be aggregated group by have conditions to filter a single order to meet the conditions

SELECTA. The city, a. User id,a. User name, a. User address, A. Contact number, C. sales clerk,SUM(A. Order amount) asOrder Amount within 4 days,COUNT(A. Order ID) asnumber of orders within 4 days, Case  when SUM(A. Order amount)>= -  Then"10 yuan (excluding hats, towels) Coupon one"ELSE NULL END  asComplimentary Coupon from (    SELECTA2. City, A2. User id,a2. User name, A2. User address, A2. Contact phone, A2. Order date, A2. Order ID,A2. Sales clerk,COUNT(A2. Product ID) asNumber of products,SUM(A2. Amount) asOrder Amount, Case  when COUNT(A2. Product ID)>=6  and SUM(A2. Amount)>= -  Then"Number of products>=6&Single order greater than 300 "ELSE NULL  END  asidentification, A4. Coupon id,a4. Coupon name, A4. Coupon Amount from (        SELECTA1. City, A1. User id,b. User name, B. user address, B. Contact number, date (order date) asOrder date, A1. Order ID,A1. Sales clerk, A1. Product id,a1. Product Name,SUM(A1. Amount) asAmount from' A003_order ' asA1 Left JOIN' A001_resterant ' asB onA1. User ID=B. User IDWHEREA1. City='Jinan'  andA1. Order Date>=" .- One- Geneva" andA1. Order Date<" .- One- ." andA1. Amount>0  and(A1. Product Name not  like"%Hat%"ORA1. Product Name not  like        "%Bath towel%" )        GROUP  byA1. Order ID,A1. Product ID) asA2 Left JOIN(#未使用优惠券ID 908 to 913 of user ordersSELECTA3. User Id,a3. Order number, A3. Coupon id,a3. Coupon name,SUM(coupon amount) asCoupon Amount from' A016_order_customercoupon_xref ' asA3WHEREA3. City=Jinan andA3. Use time>=" .- One- Geneva" andA3. Use time<" .- One- ." and(A3. Coupon ID<908 ORA3. Coupon ID>913)        GROUP  byA3. Order number) asA4 onA4. Order number=A2. Order IDGROUP  byA2. Order ID having COUNT(A2. Product ID)>=6  and SUM(A2. Amount)>= -)  asa Left JOIN' A003b_order_item ' asC onC. User ID=A. User IDGROUP  byA. User IDORDER  by SUM(A. Order amount)DESC 

In this way the data is also more convenient to see whether a user is satisfied with the criteria of compliance

MySQL Single order over 6 products and the amount of >=300 gift coupon _20161103