MySQL格式化结果基于表名和多个表数据

Question

I am coming into this project and have a little MySQL background to do basic SELECTs and INSERTs and whatnot. But, this is making me beat my head against the wall.

I have a typical user information table in MySQL:

USERS
+-------+----------+---------+-----+
|user_id|first_name|last_name|email|
+-------+----------+---------+-----+
 1       tim        jones     [e-mail]
 2       sarah      peteres   [e-mail]
 3       larry      doe       [e-mail]

Then I have multiple product tables:

PRODUCTS_ONE
+-------+-------+---------+----------+--------------+
|prod_id|user_id|prod_name|prod_width|prod_ship_date|
+-------+-------+---------+----------+--------------+
 1       1       bowl      nine       1-1-16
 2       1       fork      one        1-2-16
 3       2       plate     eleven     1-3-16

PRODUCTS_TWO
+-------+-------+----------+--------+--------------+
|prod_id|user_id|prod_state|prod_job|prod_ship_date|
+-------+-------+----------+--------+--------------+
 1       3       maine      min      1-1-16
 2       2       texas      max      1-2-16
 3       1       ohio       min      1-1-16

I have 15 total PRODUCT tables that all have prod_id, users_id, and prod_ship_date. The other fields might all be different based on what product table they are in. But, all the different PRODUCT tables have those three common fields.

What I am trying to accomplish is to get a list of USER info and PRODUCT info for products that match a certain ship date.

I want to find all the users and what product table and product id they are getting on a certain date.

So, if I searched on a ship date of 1-1-16, I would get something like:

+----------------+-----------+-------------+-----------+
|users.first_name|users.email|product_table|products_id|
+----------------+-----------+-------------+-----------+
 tim              [e-mail] one,two       1,3
 larry            [e-mail] one           3

If I searched on a ship date of 1-2-16, I would get something like:

+----------------+-----------+-------------+-----------+
|users.first_name|users.email|product_table|products_id|
+----------------+-----------+-------------+-----------+
 tim              [e-mail] one           2
 sarah            [e-mail] two           2

I hope this all makes sense. Unfortunately, I cannot change the structure or layout of the various product tables due to legacy issues.

I just can't figure out the MySQL statement to use to get something like this.

The above results will be used for reporting purposes.

Answer 1

you could write a query like this:

select first_name,email,prod_id,group_concat(product_table) as product_table from (
select u.user_id ,first_name,email,prod_id, 'one' as product_table from users u join products_one p on u.user_id = p.user_id where prod_ship_date = '2016-01-01 00:00:00'
union
select u.user_id,first_name,email,prod_id, 'two' as product_table  from users u join products_two p on u.user_id = p.user_id where prod_ship_date = '2016-01-01 00:00:00'
) a

group by a.user_id

order by user_id,product_table

and so on.

With the second group concat and order by

select first_name,email,group_concat(prod_id),group_concat(product_table) as product_table from (
select u.user_id ,first_name,email,prod_id, 'one' as product_table from users u join products_one p on u.user_id = p.user_id where prod_ship_date = '2016-01-01 00:00:00'
union
select u.user_id,first_name,email,prod_id, 'two' as product_table  from users u join products_two p on u.user_id = p.user_id where prod_ship_date = '2016-01-01 00:00:00'
) a


group by a.user_id

order by user_id,product_table

Check out this sqlfiddle