行列转置，left join 可以实现不同行相应列的合并

Myth丶恋晨 2023-02-14 15:45 47阅读 0赞

行列转置（max --用文字合并空白），left join合并

SELECT  
"服务日期" ,  
"服务时间",  
"用户名",  
"企业名称",  
"跟踪人",  
"企业类型",  
"权限",  
"频道名称",  
"栏目名称",  
"品种名称",  
"新闻标题",  
"发布人",  
"发布时间",  
"总访问时间",  
"访问IP",  
COUNT("用户名")   
FROM  
(  
select   
event\_id as "事件id",  
day  as "服务日期",  
server\_time as "服务时间",  
user\_name as "用户名",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001001') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "企业名称",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001002') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "企业类型",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001004') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "跟踪人",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001003') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "权限",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001005') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "频道名称",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001006') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "栏目名称",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001007') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "品种名称",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001008') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "新闻标题",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001010') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "发布人",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001009') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "发布时间",   
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001011') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "总访问时间",  
max(  
  CASE  
    WHEN (event\_property\_code='102101000100001012') THEN  
  event\_property\_value  
  ELSE  
   NULL  
  end  
) as "访问IP"  
from (SELECT  mm.\*,ss.\* FROM cms.daily\_new\_clientrpt\_master mm LEFT JOIN cms.daily\_new\_clientrpt\_slave ss on mm.event\_id= ss.event\_id  WHERE pro\_code='102'  ) T  GROUP BY "事件id","服务日期","服务时间","用户名" ORDER BY "事件id","服务日期" )  
GROUP BY "新闻标题","发布人","用户名","企业名称","跟踪人","企业类型","权限","频道名称","栏目名称","品种名称","发布时间","总访问时间","访问IP","服务时间","服务日期" ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200603163817432.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200603163711469.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3k2NjY2NjZ5,size_16,color_FFFFFF,t_70