要求查询如下:
1、请查询出所有部门的最新信息(deptcode,efectivedate,deptname),写出SQL语句
注:部门编码是部门的唯一标识,不会改变;部门名称信息应以最后生效的部门为准;
2、请查询出包含下级部门的并发生过更名的部门。
1、请查询出所有部门的最新信息(deptcode,efectivedate,deptname),写出SQL语句
注:部门编码是部门的唯一标识,不会改变;部门名称信息应以最后生效的部门为准;
2、请查询出包含下级部门的并发生过更名的部门。
select
*
from
tb t
where
not exists(select 1 from tb where deptcode=t.deptcode and deptname=t.deptname and efectivedate>t.efectivedate )
1. select * from dept t not exists(select 1 from dept where t.deptcode=deptcode and t.effectivedate<effectivedate)2. select * from dept t
where reason='更名'
and exists(select 1 from dept where charindex(t.deptcode,deptcode)>0 and t.deptcode<>deptcode)
select t.* from tb t where not exists (select 1 from tb where deptcode = t.deptcode and efectivedate > t.efectivedate)