1, the requirements are as follows
Consignee delivery address order number user ID
Zhang San Beijing 1001 1
Zhang Four Shanghai 1002 2
Zhang Five Beijing 1003 3
Zhang six Guangzhou 1004 4
Zhang San Shenzhen 1005 5
Zhang Seven Shanghai 1006 6
There is now such a requirement that each line is assumed to be an order record, and any item in the consignee and the Receipt address field is consistent with the other order, which is considered to be an associated order
If the associated order is greater than 3, the user ID corresponding to the order is recorded. The last thing you need is a list of user IDs.
For example: * * Order number 1001 and order number 1005 of the consignee are ' Zhang San ', prove that the two orders are linked orders, and order number 1001 and order number 1003
Need to be implemented with MySQL
2. Realize the following:
CREATE TABLE order_table ( rec_p varchar), rec_addr varchar (+), order_no varchar, user_id varchar); INSERT into order_table (rec_p,rec_addr,order_no,user_id) VALUES (' Zhang San ', ' Beijing ', ' 1001 ', ' 1 '); INSERT into Order_table (rec_p,rec_addr,order_no,user_id) VALUES (' Zhang Si ', ' Shanghai ', ' 1002 ', ' 2 '); insert into order_table (REC_P,REC_ADDR, order_no,user_id) VALUES (' Zhang Five ', ' Beijing ', ' 1003 ', ' 1 '), insert into order_table (rec_p,rec_addr,order_no,user_id) VALUES (' Zhang six ', ' Guangzhou ', ' 1004 ', ' 3 '), insert into order_table (rec_p,rec_addr,order_no,user_id) VALUES (' Zhang San ', ' Shenzhen ', ' 1005 ', ' 1 '); insert Into order_table (rec_p,rec_addr,order_no,user_id) VALUES (' Zhang Seven ', ' Shanghai ', ' 1006 ', ' 4 '); Select a.* from Order_table A, ( Select Rec_p,count (*) Pnum from order_table Group by rec_p) A1, (Select Rec_addr,count (*) Addrnum from order_table Group by REC_ADDR) A2where a.rec_p=a1.rec_p and A.rec_addr=a2.rec_addr and (Pnum+addrnum) >3
MySQL implements associated order statistics