I have a database MySQL 8.0 with 12 tables (one of each month) with information about clients. All tables have a Client ID and the amount of money that they save. For example, for two months:
------------------
January
------------------
ClienID | Amount
------------------
qwer23 | 23
------------------
December
------------------
ClienID | Amount
------------------
qwer23 | 15
And I want to get a table with the ClientID and the Amount of each month. Like this
------------------ ---------------- ----------
ClienID | January| | December |
------------------ ---------------- ----------
qwer23 | 23 | | 15 |
I search some options, but I'm not sure how to use JOIN or GROUP BY.
CodePudding user response：
You have one table for each of the twelve months, and a client can have up to one row in each table. To get a result row per client with all their monthly amounts, you'd full outer join all tables on the client ID. MySQL, however, still doesn't support full outer joins.
As the tables have a client ID, I assume there is also a client table these IDs are linking to. So, just select the clients from the clients table and outer join the months:
select
clientid,
january.amount as january,
february.amount as february,
...
december.amount as december
from client
left outer join january using (clientid)
left outer join february using (clientid)
...
left outer join december using (clientid);
If you don't have a client table (which seems unlikely), you can create one on-the-fly, by replacing
from client
by
from
(
select clientid from january union
select clientid from february union
...
select clientid from december
) client
CodePudding user response：
I have a database MySQL 8.0 with 12 tables (one of each month)
I think this is not a good database design, you shouldn't store months in different tables.
For the given data one way is using union for all the months, try:
select ClienID,
max(January) as January,
max(Febrary) as Febrary,
max(December) as December
from (
select ClienID,amount as 'January',null as 'Febrary', null as 'December'
from January
union
select ClienID,null as 'January',null as 'Febrary',amount as 'December'
from December
) as months_tbl
group by ClienID;
This is simplified for 3 months only, you need to add for each month a union clause, and the above query takes in considerate one value for each different ClienID.
The complete query is a follows:
select ClienID,
max(January) as January,
max(Febrary) as Febrary,
max(March) as March,
max(April) as April,
max(May) as May,
max(June) as June,
max(July) as July,
max(August) as August,
max(September) as September,
max(October) as October,
max(November) as November,
max(December) as December
from (
select ClienID,amount as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from January
union
select ClienID,null as 'January',amount as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from February
union
select ClienID,null as 'January',null as 'February', amount as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from March
union
select ClienID,null as 'January',null as 'February', null as 'March', amount as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from April
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', amount as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from May
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', amount as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from June
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', amount as 'July', null as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from July
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', amount as 'August', null as 'September', null as 'October', null as 'November', null as 'December'
from August
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', amount as 'September', null as 'October', null as 'November', null as 'December'
from September
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', amount as 'October', null as 'November', null as 'December'
from October
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', amount as 'November', null as 'December'
from November
union
select ClienID,null as 'January',null as 'February', null as 'March', null as 'April', null as 'May', null as 'June', null as 'July', null as 'August', null as 'September', null as 'October', null as 'November', amount as 'December'
from December
) as months_tbl
group by ClienID;