-
-
niyaoqunalia 初学数据Lv2
发表于2019-5-14 10:25
悬赏10
已解决
版本号企业版 V4.7.1R133-build20180904153258-re964897
最近看过此主题的会员
23个回答
-
niyaoqunalia 初学数据Lv2
这是可以显示名称的sql
select a.B2,a.B1,a.A2,a.mc_A21,b.B2,b.A2,b.mc_A21,c.B2,c.B1
from (
select count(distinct a.USER_ID) as B2, LEFT(b.code,2) as B1,a.DEPT_ID as A2,max(c.DEPT_NAME) as mc_A21
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
group by a.DEPT_ID, LEFT(b.code,2)) a
inner join (
select * from ( select count(distinct a.USER_ID) as B2,a.DEPT_ID as A2,max(c.DEPT_NAME) as mc_A21
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
group by a.DEPT_ID ) as temp_ limit 1000) b
on (a.A2=b.A2 or (a.A2 is null and b.A2 is null))
inner join (
select * from ( select count(distinct a.USER_ID) as B2, LEFT(b.code,2) as B1
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
group by LEFT(b.code,2) ) as temp_ limit 50) c
on (a.B1=c.B1 or (a.B1 is null and c.B1 is null))
这是不能显示名称的SQL
select a.B2,a.B1,a.A2,a.mc_A21,b.B2,b.A2,b.mc_A21,c.B2,c.B1
from (
select count(distinct a.USER_ID) as B2, LEFT(b.code,2) as B1,a.DEPT_ID as A2,max(c.DEPT_NAME) as mc_A21
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
where (CONVERT(a.DEPT_ID, CHAR) like '32%' or CONVERT(a.DEPT_ID, CHAR) like '33%' or CONVERT(a.DEPT_ID, CHAR) like '34%')
group by a.DEPT_ID, LEFT(b.code,2)) a
inner join (
select * from ( select count(distinct a.USER_ID) as B2,a.DEPT_ID as A2,max(c.DEPT_NAME) as mc_A21
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
where (CONVERT(a.DEPT_ID, CHAR) like '32%' or CONVERT(a.DEPT_ID, CHAR) like '33%' or CONVERT(a.DEPT_ID, CHAR) like '34%')
group by a.DEPT_ID ) as temp_ limit 1000) b
on (a.A2=b.A2 or (a.A2 is null and b.A2 is null))
inner join (
select * from ( select count(distinct a.USER_ID) as B2, LEFT(b.code,2) as B1
from hb_etyy_v_userinfo a
left join DIM_TITLE b
on (a.TITLE_LEVEL=b.tit_name)
left join DEPARTMENT c
on (a.DEPT_ID=c.DEPT_ID)
where (CONVERT(a.DEPT_ID, CHAR) like '32%' or CONVERT(a.DEPT_ID, CHAR) like '33%' or CONVERT(a.DEPT_ID, CHAR) like '34%')
group by LEFT(b.code,2) ) as temp_ limit 50) c
on (a.B1=c.B1 or (a.B1 is null and c.B1 is null))
niyaoqunalia 发表于 2019-5-14 17:10
这是可以显示名称的sql
select a.B2,a.B1,a.A2,a.mc_A21,b.B2,b.A2,b.mc_A21,c.B2,c.B1
from (
不能显示的这块貌似是多个个过滤条件出来，根据自己做的表样排查下 是不是哪里的过滤条件写的有误
