SQL语句---子查询、行列互换、if语句

select A.date_range,`USER`,A.USER_ID,A.PROJECT,`GROUP`,
sum(IF(A.staus='pushed to',A.ct, 0)) as `pushed to`,
sum(IF(A.staus='pushed new',A.ct,0)) as `pushed new`,
sum(IF(A.staus='created',A.ct,0)) as `created`,
sum(IF(A.staus='deleted',A.ct,0)) as `deleted`,
sum(IF(A.staus='joined',A.ct,0)) as `joined`,sum(A.ct) as total
from (
	select e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='pushed to','pushed to','false') as staus,count(*)  ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='pushed to' 
	group by project_id,author_id
	union
	select  e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='pushed new','pushed new','false') as staus,count(*) ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='pushed new'
	group by project_id,author_id
	union
	select  e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='created','created','false') as staus,count(*) ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='created'
	group by project_id,author_id
	union
	select  e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='deleted','deleted','false') as staus,count(*) ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='deleted'
	group by project_id,author_id
	union
	select  e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='left','left','false') as staus,count(*) ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='left'
	group by project_id,author_id
	union
	select  e.date_range,e.author_name `USER`,e.author_username `USER_ID`,p.name `PROJECT`,p.namespace_name `GROUP`,
	if(action_name='joined','joined','false') as staus,count(*) ct
	from events_info e ,projects_info_02 p
	where e.project_id=p.id and action_name='joined'
	group by project_id,author_id
	order by `GROUP`
    ) A
group by A.USER_ID,A.PROJECT  
order by A.GROUP;
在这里插入代码片

其中子查询得到的虚拟表格如下所示
在对得到的虚拟表进行行列互换得到的结果如图所示：