Related order statistics for mysql and related order statistics for mysql

Related order statistics for mysql and related order statistics for mysql

1. the requirements are as follows:

Recipient shipping address Order Number user ID

Zhang San, Beijing 1001 1

Zhang Si Shanghai 1002 2

Zhang Wu, Beijing 1003 3

ZHANG Liu, Guangzhou 1004 4

Zhang San Shenzhen 1005 5

Zhang Qi, Shanghai 1006, 6

Now there is such a demand, each line is assumed to be an order record, the recipient and the receiving address field any one is consistent with other orders, that is, considered to be associated orders
If there are more than three associated orders, the corresponding user ID of this order will be recorded. The final requirement is the list of user IDs.
For example, ** the two orders are associated with an order number 1001 and an order number 1005 is 'zhangsan '.

MySQL is required

2. The implementation is as follows:

Create table order_table (rec_p varchar (20), rec_addr varchar (100), order_no varchar (20), user_id varchar (20); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('zhang san', 'beijing', '123', '1'); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('zhang si ', 'shanghai', '123', '2'); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('zhangwu', 'beijing', '123 ', '1'); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('item 6', 'guangzhou ', '123', '3 '); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('zhang san', 'shenzhen ', '123', '1'); insert into order_table (rec_p, rec_addr, order_no, user_id) values ('zhang 7', 'shanghai', '123', '4'); select. * 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. rec_p = a1.rec _ p and. rec_addr = a2.rec _ addr and (pnum + addrnum)> 3