declare @BUKA_CODE as varchar(10)
set @BUKA_CODE = '...'select * from jinji_mst where SYAIN_NO in (select SUPERIOR_NO from group_cd where BUKA_CODE = @BUKA_CODE) or SYAIN_NO in (select SYAIN_NO from group_cd where BUKA_CODE = @BUKA_CODE) order by TOKYU
set @BUKA_CODE = '...'select * from jinji_mst where SYAIN_NO in (select SUPERIOR_NO from group_cd where BUKA_CODE = @BUKA_CODE) or SYAIN_NO in (select SYAIN_NO from group_cd where BUKA_CODE = @BUKA_CODE) order by TOKYU
select distinct jinji_mst.SYAIN_NO, SYAIN_NAME, BUKA_CODE1, TOKYU
from jinji_mst
innser join group_cd on group_cd.SYAIN_NO = jinji_mst.SYAIN_NO
and jinji_mst.BUKA_CODE='BUKA_CODE'
order by group_cd.BUKA_CODE asc,group_cd.GROUP_CD asc,jinji_mst.TOKYU desc,jinji_mst.SYAIN_NO asc
这句话怎么解释?
innser 改成 inner
distinct jinji_mst.SYAIN_NO, SYAIN_NAME, BUKA_CODE1, TOKYU
from
jinji_mst
join group_cd on
group_cd.SYAIN_NO = jinji_mst.SYAIN_NO
and
jinji_mst.BUKA_CODE='BUKA_CODE'
order by
group_cd.BUKA_CODE asc,group_cd.GROUP_CD asc,
jinji_mst.TOKYU desc,jinji_mst.SYAIN_NO asc
SYAIN_NO SYAIN_NAME BUKA_CODE1 TOKYU
0930 张 211 3
1055 王 211 3
1172 李 211 2
0155 赵 211 6
0168 孙 211 6
0537 孙 211 6
0539 孙 211 6
0538 孙 211 6
group_cd
GROUP_CD SYAIN_NO BUKA_CODE SUPERIOR_NO
C1 0155 211 0538
C2 0168 211 0539
C3 1055 211 0537
C1 1172 211 0538
想出的结果
211 C1 0538 孙 6 0155
211 C1 0538 孙 2 1172
211 C2 0539 孙 6 0168
211 C3 0537 孙 3 1055
a.BUKA_CODE,
a.GROUP_CD,
a.SUPERIOR_NO,
b.SYAIN_NAME,
b.TOKYU,
a.SYAIN_NO
from
group_cd a
inner join
jinji_mst b
on
a.BUKA_CODE=b.BUKA_CODE1 adn a.SYAIN_NO=b.SYAIN_NO
where
a.BUKA_CODE=@BUKA_CODE
if object_id('[jinji_mst]') is not null drop table [jinji_mst]
go
create table [jinji_mst]([SYAIN_NO] varchar(4),[SYAIN_NAME] varchar(2),[BUKA_CODE1] int,[TOKYU] int)
insert [jinji_mst]
select '0930','张',211,3 union all
select '1055','王',211,3 union all
select '1172','李',211,2 union all
select '0155','赵',211,6 union all
select '0168','孙',211,6 union all
select '0537','孙',211,6 union all
select '0539','孙',211,6 union all
select '0538','孙',211,6
if object_id('[group_cd]') is not null drop table [group_cd]
go
create table [group_cd]([GROUP_CD] varchar(2),[SYAIN_NO] varchar(4),[BUKA_CODE] int,[SUPERIOR_NO] varchar(4))
insert [group_cd]
select 'C1','0155',211,'0538' union all
select 'C2','0168',211,'0539' union all
select 'C3','1055',211,'0537' union all
select 'C1','1172',211,'0538'
declare @BUKA_CODE int
set @BUKA_CODE=211
select
a.BUKA_CODE,
a.GROUP_CD,
a.SUPERIOR_NO,
b.SYAIN_NAME,
b.TOKYU,
a.SYAIN_NO
from
group_cd a
inner join
jinji_mst b
on
a.BUKA_CODE=b.BUKA_CODE1 and a.SYAIN_NO=b.SYAIN_NO
where
a.BUKA_CODE=@BUKA_CODE
order by
a.BUKA_CODE,
a.GROUP_CD,
b.TOKYU desc,
a.SYAIN_NO
--测试结果:
/*
BUKA_CODE GROUP_CD SUPERIOR_NO SYAIN_NAME TOKYU SYAIN_NO
----------- -------- ----------- ---------- ----------- --------
211 C1 0538 赵 6 0155
211 C1 0538 李 2 1172
211 C2 0539 孙 6 0168
211 C3 0537 王 3 1055(4 行受影响)*/
declare @BUKA_CODE int
set @BUKA_CODE=211
select
a.BUKA_CODE,
a.GROUP_CD,
a.SUPERIOR_NO,
b.SYAIN_NAME,
c.TOKYU,
a.SYAIN_NO
from
group_cd a
inner join
jinji_mst b
on
a.BUKA_CODE=b.BUKA_CODE1 and a.SUPERIOR_NO=b.SYAIN_NO
inner join
jinji_mst c
on
a.BUKA_CODE=c.BUKA_CODE1 and a.SYAIN_NO=c.SYAIN_NO
where
a.BUKA_CODE=@BUKA_CODE
order by
a.BUKA_CODE,
a.GROUP_CD,
b.TOKYU desc,
a.SYAIN_NO
--测试结果:
/*
BUKA_CODE GROUP_CD SUPERIOR_NO SYAIN_NAME TOKYU SYAIN_NO
----------- -------- ----------- ---------- ----------- --------
211 C1 0538 孙 6 0155
211 C1 0538 孙 2 1172
211 C2 0539 孙 6 0168
211 C3 0537 孙 3 1055(4 行受影响)*/