java中组合类不需要多表联查,如何将多个表的结果与不同的列组合在一起？

一时失言乱红尘 2022-08-29 15:46 122阅读 0赞

![Image 1][]

I have several tables with different numbers and types of columns, and a single column in common.

\+--------+---------+------------+-------------+

\+--------+---------+------------+-------------+

\+--------+-------------+----------------+

| person | moustacheID | moustacheStyle |

\+--------+-------------+----------------+

I want to fetch all the results that match a given value of the shared column. I can do it using multiple select statements like this:

SELECT \* FROM beards WHERE person = "bob"

and

SELECT \* FROM moustaches WHERE person = "bob"

But this requires multiple mysql API calls, which seems inefficient. I was hoping I could use UNION ALL to get all the results in a single API call, but UNION requires that the tables have the same number and similar type of columns. I could write a SELECT statement that would manually pad the results from each table by adding columns with NULL values, but that would quickly get unmanageable for a few more tables with a few more columns.

I'm looking for a result set roughly like this:

\+--------+---------+------------+-------------+-------------+----------------+

\+--------+---------+------------+-------------+-------------+----------------+

| bob | 1 | rasputin | 1 | | |

\+--------+---------+------------+-------------+-------------+----------------+

| bob | 2 | samson | 12 | | |

\+--------+---------+------------+-------------+-------------+----------------+

| bob | | | | 1 | fu manchu |

\+--------+---------+------------+-------------+-------------+----------------+

Is there a way to achieve this that's fast and maintainable? Or am I better off running a separate query for each table?

Clarification:

I'm not looking for a cartesian product. I don't want a row for every combination of beard-and-moustache, I want a row for every beard and a row for every moustache.

So if there are 3 matching beards and 2 matching moustaches I should get 5 rows, not 6.

解决方案

this should be working fine:

SELECT \* FROM \`beards\` b LEFT OUTER JOIN \`mustaches\` ON (0) WHERE person = "bob"

UNION ALL

SELECT \* FROM \`beards\` b RIGHT OUTER JOIN \`mustaches\` ON (0) WHERE person = "bob"

you don't have to handle the columns by yourself. the left and right outer join do this job.

unfortunately mysql doesn't have a full join. that's why you have to do it this way with a union

SELECT \* FROM \`customer\` b LEFT OUTER JOIN \`charges\` ON (0) LEFT OUTER JOIN \`day\` ON (0)

UNION

SELECT \* FROM \`customer\` b RIGHT OUTER JOIN \`charges\` ON (0) LEFT OUTER JOIN \`day\` ON (0)

UNION