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?
给我你的怀抱2017-07-04 13:46:12
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;