MySQL multiple conditions with what table as the main condition table _20161111 Friday

Tag: The code filtering process contains the activity requirements record Lin and

There was a 2-day outage two days ago.

Data requirements:
1. Event Date 11.8-11.10 Order
2, a single order to buy a class B products to meet the 68 yuan, and 3 consecutive days to order the user ID

First, the first condition is simple, the second condition is the second condition, and the logical operation

And the calculation of each field dimension is not the same, single order purchase a, B meet 68 This situation contains a user one day under multiple orders this is a user multiple records of the situation I call many to one

The second condition is the user ID of the order for 3 consecutive days. This is a one-to-two record line a record is a user corresponding to the three days of the next single days total I call it one

The first method of rendering: I what you want to render is a table that contains one of the following fields

A user, placing a single number of days, three days per day of the amount (in line with the single order AB and >=68, so need a most fine detail an order specific to each product to go to the original details so it looks clearer this presentation process is to put a single order to meet the criteria of the user first filtered out and then to judge his next single days

A3 table having a single order to satisfy the condition of the user ID the following is the calculation of the next number of days because there may be multiple orders a day, you need to repeat the group by for the user and the order date.

Calculates the count (order date) number of days in descending order of number of days by the end of the group by user calculates the number of days that the user is eligible for a single condition and then judges the user to find out.

So the result of the table is a pair of one by one users a row of records

SELECTCity, user ID, user name,SUMAmount as3 Days Total Amount,COUNT(Order Date) asNumber of days to order,SUM(IF(Order Date='2016-11-08', AB Amount and,NULL)) as8th AB Amount and,SUM(IF(Order Date='2016-11-09', AB Amount and,NULL)) as9th AB Amount and,SUM(IF(Order Date='2016-11-10', AB Amount and,NULL)) as10th AB Amount and from (    SELECTCity, user ID, user name, order date,SUMAmount asAmountSUM(AB Amount and) asAB Amount and,SUM(a amount) asA amount,SUM(b amount) asB Amount from (        SELECTCity, user ID, user name, order date, order ID,SUMAmount asAmount, (SUM(IF(Product Category="A", Amount,0))+SUM(IF(Product Category="B", Amount,0))) asAB Amount and,SUM(IF(Product Category="A", Amount,NULL)) asA amount,SUM(IF(Product Category="B", Amount,NULL)) asB Amount from(            SELECTCity, user ID, user name, date (order date) asOrder date, ' Order ID ', A1 ' Product ID ', A1 ' product name ', ' Product category ', ' Amount ' from' A003_order ' asA1 Left JOIN' A002_sku ' asA2 onA1. Product ID=A2. Product IDWHERECity='Jinan'  andOrder Date>=" .- One- ," andOrder Date<" .- One- One" andAmount>0         )  asA3GROUP  byOrder ID having(SUM(IF(Product Category="A", Amount,0))+SUM(IF(Product Category="B", Amount,0)))>= the        ORDER  byUser ID) asaGROUP  byUser ID, order date) asbGROUP  byUser IDORDER  by COUNT(Order Date)DESC 

Second rendering: I first calculate all users in this 3 days summary, a user row records, the number of days to place the order, the total number of orders, 3 days a total, b total, AB Total and, this is the main table

Finally, to determine the users who are single orders to meet 68 of the user as a table from the left JOIN Main table as the user condition

Both tables are both one-to-two tables that are viewed from the user's perspective

SELECTA.*, B. Identification from(#以用户ID汇总 All users who have orders within 3 daysSELECTCity, User ID,COUNT(Order Date) asNumber of days to order,SUM(Number of orders) asNumber of orders,SUM(AB Amount and) asAB Amount and,SUM(a amount) asA amount,SUM(b amount) asB Amount from (        SELECTCity, User ID, order date,COUNT(Order ID) asNumber of orders,SUM(AB Amount and) asAB Amount and,SUM(a amount) asA amount,SUM(b amount) asB Amount from (            SELECTCity, User id,date (order date) asOrder date, Order ID,SUMAmount astotal order, (SUM(IF(A2. Product Categories="A", Amount,0))+SUM(IF(A2. Product Categories="B", Amount,0))) asab Amount and,SUM(IF(A2. Product Categories="A", Amount,NULL)) asa amount,SUM(IF(A2. Product Categories="B", Amount,NULL)) asB Amount from' A003_order ' asA1 Left JOIN' A002_ Products ' asA2 onA1. Product ID=A2. Product IDWHERECity='Jinan'  andOrder Date>=" .- One- ," andOrder Date<" .- One- One" andAmount>0  andUser ID=44825            GROUP  byOrder ID) asA3GROUP  byUser ID, order date) asA4GROUP  byUser ID) asa Left JOIN(#取出AB和单笔订单>=68 User Orders one-to- oneSELECTB3.*     from(#订单ID明细 a user that corresponds to multiple order IDs one-to-manySELECTCity, User id,date (order date) asOrder date, Order ID,SUMAmount astotal order, (SUM(IF(B2. Product Categories="A", Amount,0))+SUM(IF(B2. Product Categories="B", Amount,0))) asab Amount and,SUM(IF(B2. Product Categories="A", Amount,NULL)) asa amount,SUM(IF(B2. Product Categories="B", Amount,NULL)) asB amount, Case  when(SUM(IF(B2. Product Categories="A", Amount,0))+SUM(IF(B2. Product Categories="B", Amount,0)))>= the  Then"A single order within 3 days>= the"ELSE NULL END  asIdentification from' A003_order ' asB1 Left JOIN' A002_ Products ' asB2 onB1. Product ID=b2. Product IDWHERECity='Jinan'  andOrder Date>=" .- One- ," andOrder Date<" .- One- One" andAmount>0  andUser ID=44825        GROUP  byOrder IDORDER  by(SUM(IF(B2. Product Categories="A", Amount,0))+SUM(IF(B2. Product Categories="B", Amount,0)))DESC    )  asB3WHEREIdentity is  not NULL    GROUP  byUser ID) asB onA. User ID=B. User ID

MySQL multiple conditions with what table as the main condition table _20161111 Friday