mysql - How to implement multi-table association query?-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

mysql - How to implement multi-table association query?

巴扎黑 2017-07-04 13:44:12

1030

Table A LogID UserId Date 00001 0001 05-01 00002 0002 05-02 00003 0003 05-02 00004 0004 05-02 00005 0003 05-03 00006 0001 05-03 00007 0002 05-03 Table B UserId Status 0001 Active 0002 Active 0003 Active 0004 Inactive Table C UserId Province 0001 Yunnan 0002 Fujian 0003 Fujian 0004 Beijing

The above are three tables in the database, related through UserID. Table A is a user login information table with LogID as the primary key; Table B stores user active status, and Table C stores user geographical location information. Now I want to get the cumulative sum of the number of other states based on the date grouping in table A. The expected return result is:

Date Active Inactive Yunnan Fujian Beijing 05-01 1 0 1 0 0 05-02 2 1 0 2 1 05-03 3 0 1 2 0

Can it be implemented with a SQL statement?

巴扎黑

reply all (1)

给我你的怀抱2017-07-04 13:46:12 1 floor

The business logic of this table is very loose, so I won’t write it for you in a loose way. Just treat your ABC table relationship as many-to-one:

select a.date, sum(case when b.status='Active' then 1 else 0 end) 'Active', sum(case when b.status='Inactive' then 1 else 0 end) 'Inactive', sum(case when c.province ='Yunnan' then 1 else 0 end) 'Yunnan', sum(case when c.province ='Fujian' then 1 else 0 end) 'Fujian', sum(case when c.province ='Beijing' then 1 else 0 end) 'Beijing' from a left join b on a.userid=b.user_id join c on a.user_id=c.user_id group by a.date order by a.date;