hr的sql

客户朋友圈统计

select m.moment_id as '任务id',
m.create_time_time as '创建时间',
m.creator as '创建人',
m.text_content as '文本内容',
m.create_type as '创建来源 0企业 1个人',
m.visible_type as '可见范围 0部分可见 1公开',
m.ucount as '员工总数1',
-- (n.ucount +f.ucount) as '员工总数', 
n.ucount as '未发表人数',
f.ucount as '已发表人数',
cc.ccount as '评论数',
cl.clount as '点赞数'  from  (
select m.moment_id,m.create_time_time,
m.creator,m.create_type,m.visible_type,m.text_content,
count(t.userid) as  ucount
from wecome_moment_list  m 
left join wecome_moment_task t on m.moment_id=t.moment_id 
where 1=1
-- m.create_type='0' 
-- and  m.link_title='富安娜艺术家' 
-- and m.create_time_time >='2023-09-21 00:00:00'
-- and m.creator = 34236 
-- and m.moment_id='mom0z2ajBwAAG0mNArdX_fWGiT4ZkdWImw'
GROUP BY m.moment_id,m.create_time_time,
m.creator,m.create_type,m.visible_type,m.text_content  
order by m.create_time_time desc) m

left join 
(
select m.moment_id,count(t.userid) as  ucount
from wecome_moment_list  m  
left join wecome_moment_task t on m.moment_id=t.moment_id
where  t.publish_status ='0'
GROUP BY m.moment_id ) as n  on n.moment_id = m.moment_id

left join 
(
select m.moment_id,count(t.userid) as  ucount
from wecome_moment_list  m  
left join wecome_moment_task t on m.moment_id=t.moment_id
where t.publish_status ='1'
GROUP BY m.moment_id ) as f on f.moment_id = m.moment_id

left join 
(SELECT moment_id,count(id) as ccount  from  wecome_moment_comments  where comment_type ='comment'
GROUP BY  moment_id ) cc on cc.moment_id = m.moment_id
left join 
(SELECT moment_id,count(id) as clount from  wecome_moment_comments  where comment_type ='like'
GROUP BY  moment_id) cl on cl.moment_id = m.moment_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49