连表查询的方案（excel透视后记数项去重）
excel版透视后记数项去重
1.将表用户浏览资讯（资讯标题以及作者）的表增加用户标签字段（VLOOKUP）
2.进行数据透视，注意关键步骤（勾选：将此数据添加到数据模型）
3.可筛选到记数项
mysql版
2、今天首先讲mysql篇
安装mysql
使用dbeaver链接mysql
安装dbeaver时，注意要赋予dbeaver用户执行权限
1、建立两张数据库表（感谢我们公司的数据库工程师）
表一：用户浏览资讯（资讯标题以及作者）的表
表二：用户公司标签的表（分为6大类）
2、将数据导入数据库中
注意导入的数据库要cvs格式且表头要和数据库的表头英文名要对上，才可以导入、
是否数据库已识别excel中的表头
4、写sql关联查询
-- 文章uv
select
a.articletitle ,a.author ,
sum(CASE trim(u.mark) when '总部领导' then 1 else 0 end) as '总部领导',
sum(case trim(u.mark) when '机构领导' then 1 else 0 end) as '机构领导',
sum(case trim(u.mark) when '总部内勤' then 1 else 0 end) as '总部内勤',
sum(case trim(u.mark) when '机构员工' then 1 else 0 end) as '机构员工',
sum(case trim(u.mark) when '机构外勤' then 1 else 0 end) as '机构外勤',
sum(case trim(u.mark) when '九安员工' then 1 else 0 end) as '九安员工',
sum(case when u.mark is null then 1 else 0 end) as '其他' ,
count(*)
from (select distinct author, articletitle,username from articledata) a left join
userinfo u
on TRIM(a.username) = TRIM(u.name)
-- where a.username = '甲天下1234'
-- where a.author = ' 甲天下1234'
group by a.articletitle ,a.author
order by sum(CASE trim(u.mark) when '总部领导' then 1 else 0 end) DESC ;
-- 文章pv
select
a.articletitle ,a.author ,
sum(CASE trim(u.mark) when '总部领导' then 1 else 0 end) as '总部领导',
sum(case trim(u.mark) when '机构领导' then 1 else 0 end) as '机构领导',
sum(case trim(u.mark) when '总部内勤' then 1 else 0 end) as '总部内勤',
sum(case trim(u.mark) when '机构员工' then 1 else 0 end) as '机构员工',
sum(case trim(u.mark) when '机构外勤' then 1 else 0 end) as '机构外勤',
sum(case trim(u.mark) when '九安员工' then 1 else 0 end) as '九安员工',
sum(case when u.mark is null then 1 else 0 end) as '其他' ,
count(*)
from articledata a left join
userinfo u
on TRIM(a.username) = TRIM(u.name)
group by a.articletitle ,a.author
order by count(*) DESC ;
