During MySQL activity, orders are over $600 and have not been _20161030 for more than 30 days before the event date.

The order is over $600 during the calculation activity and has not been placed for more than 30 days before the activity date

first get this demand, first need to clear the activity date interval 10.29-10.31, its secondary to the time of the day of the highest order amount of the day and the order amount, again need to judge the user before the 29th, before the end of the 29th number of days between the date and 10.29th is greater than 30 days interval

So it's a matter of converting to a date of two tables.

1. The first table takes all users before the end of the single date of 10.29th, the last single amount, this table is the main table behind the table left join it

2. The day when the second table takes the highest order amount during the activity (nesting is required, the amount of the 3 days is calculated first, group by user ID, the order date is the highest amount of the day by the user ID ascending amount descending and then the table group by user ID)

3. Then the second table left join the first table is conditional on the user ID equality

SELECTC. The city, c. user id,d. User name, D. user address, D. Contact number, c. Salesperson, c. Order date, c. Order Amount, C. identification, C. Last Order date asLast single date before number 29th, C. Previous amount asAmount before number 29th, E. The first few days asThe end of number 29th is the first day, c. Number of days between the last order of 29th asNumber of days before 29th for the end of single date distance 29th from (            SELECTA.*, B. Order date, B. Order Amount, Case  whenB. Sales Staff is NULL  ThenA. Last sales memberELSEB. Sales StaffEND  asSales Clerk, Case  whenB. Order Date is  not NULL  andB. Order Amount>= -  Then">= -" whenB. Order Date is  not NULL  andB. Order Amount< -  Then"< -"ELSE"Ten. No. 29th to 11.1th no order "END  asidentification, Timestampdiff ( Day, A. Last Order date, " .-Ten- in") asLast Order distance 29th number of days from(#取所有用户10. Last Order date before number 29thSELECTA2. City, A2. User id,a2. Order Date asLast order date, A2. Sales Clerk asLast sales member, A2. Amount asLast Amount from (            SELECTA1. City, A1. User id,date (A1. Order date) asOrder Date,SUMAmount asamount, A1. Sales Clerk from' A003_order ' asA1WHEREA1. City='Changchun'  andA1. Order Date<" .-Ten- in" andA1. Amount>0#取完各用户GROUP  byA1. User id,date (A1. Order date)ORDER  byA1. User id,date (A1. Order date)DESC        )  asA2GROUP  byA2. User ID) asa Left JOIN(#用户在10.29 to 11.01 the highest order amount of the daySELECTB2. User id,b2. Order date, B2. Salesperson, B2. Order Amount from (            SELECTB1. User Id,date (B1. Order date) asOrder Date, B1, sales clerk,SUM(B1. Amount) asOrder Amount from' A003_order ' asB1WHEREB1. City=Changchun andB1. Order Date>=" .-Ten- in" andB1. Order Date<" .- One- on" andB1. Amount>0             GROUP  byB1. User Id,date (B1. Order date)ORDER  byB1. User ID,SUM(B1. Amount)DESC         )  asB2GROUP  byB2. User ID) asB onA. User ID=B. User ID) asC Left JOIN' A001_resterant ' asD onD. User ID=c. User ID Left JOIN' Tb003_08 ' asE onE. User ID=C. User ID andE. Last Order Date=c. Date of last orderWHEREC. Number of days between the last order distance 29th>= -  andC. Identification=">= -"

During MySQL activity, orders are over $600 and have not been _20161030 for more than 30 days before the event date.