我有一个部门和员工的联合查询，想实现各个部门的员工序号都是从1才是，如果是部门就不显示。我这样只能查所有人的序号，并不能按部门开始。向大家请教下要怎么查
这是我给员工表上加的序号
select row_number() over (order by a.orgid,a.name) as seq,
------解决思路----------------------
--部门类似这样
select '' as seq,a.id as deptid,a.name as deptmc,'' as managermc
from sys_organization as a
where a.ifbusinessdept='1'
--人员
union
select CAST(row_number() over (PARTITION BY a.orgid order by a.orgid,a.name)AS VARCHAR) as seq,a.orgid as deptid,'' as deptmc,a.name as managermc
from sys_user as a
where a.usertype=5
------解决思路----------------------
UNION 的多个结果之间字段类型、长度都要一致。
select Convert(varchar(11),'') as seq,
a.id as deptid,
a.name as deptmc,
Convert(varchar(20),'') as managermc -- 类型要和 sys_user.name 一致
from sys_organization as a
where a.ifbusinessdept='1'
UNION ALL -- 加了 ALL 就不需要数据库做去重复了，虽然结果一样但是效率不同。
select Convert(varchar(11),
row_number() over (PARTITION BY a.orgid order by a.orgid,a.name)
) as seq,
a.orgid as deptid,
Convert(varchar(30),''_ as deptmc, -- 类型要和 sys_organization.name 一致
a.name as managermc
from sys_user as a
where a.usertype=5