表A table :jyb
XNO(购买人的ID) IDKEY(交易号) (KF_code)购买地点ID JSRID(服务人员ID)
00001 001 0001 admin
00002 002 0002 011051
00001 003 0001 admin
00002 004 0001 011021
00002 005 0002 011031
00001 006 0002 011031
........
........表B 权限表
JSR_ID KF_CODE
admin 0001
admin 0002
011051 0002
011021 0001
011031 0002
我想写个SQL语句 当查询的人员是admin的时候 就能查询出0001仓库和0002仓库交易的所有记录
查询011051的时候 就只能查询出0002仓库的记录 好像是用exists语句 但是不知道怎么写 高手帮帮忙啊
XNO(购买人的ID) IDKEY(交易号) (KF_code)购买地点ID JSRID(服务人员ID)
00001 001 0001 admin
00002 002 0002 011051
00001 003 0001 admin
00002 004 0001 011021
00002 005 0002 011031
00001 006 0002 011031
........
........表B 权限表
JSR_ID KF_CODE
admin 0001
admin 0002
011051 0002
011021 0001
011031 0002
我想写个SQL语句 当查询的人员是admin的时候 就能查询出0001仓库和0002仓库交易的所有记录
查询011051的时候 就只能查询出0002仓库的记录 好像是用exists语句 但是不知道怎么写 高手帮帮忙啊
from jyb
where
jsrid in(
select
KF_CODE
from 权限表
where JSR_ID='admin'
)
set @jsrid = 'admin'select b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsridset @jsrid = '011051'select b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsrid
select *
from jyb
where
KF_code in(
select
KF_CODE
from 权限表
where JSR_ID='admin'
)
这一个没有使用到code 啊
直接链表就OK了
insert into a values('admin')
insert into a values('011051')
insert into a values('admin')
insert into a values('011021')
insert into a values('011031')
insert into a values('011031')
create table B(JSR_ID varchar(10),KF_CODE varchar(10))
insert into b values('admin' , '0001')
insert into b values('admin' , '0002')
insert into b values('011051', '0002')
insert into b values('011021', '0001')
insert into b values('011031', '0002')
godeclare @jsrid as varchar(10)
set @jsrid = 'admin'select distinct b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsrid
/*
JSR_ID KF_CODE
---------- ----------
admin 0001
admin 0002(所影响的行数为 2 行)
*/set @jsrid = '011051'
select distinct b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsrid
/*
JSR_ID KF_CODE
---------- ----------
011051 0002(所影响的行数为 1 行)
*/drop table a , b
表A table :jyb
XNO(购买人的ID) IDKEY(交易号) (KF_code)购买地点ID JSRID(服务人员ID)
00001 001 0001 admin
00002 002 0002 011051
00001 003 0001 admin
00002 004 0001 011021
00002 005 0002 011031
00001 006 0002 011031
........
........ 表B 权限表
JSR_ID KF_CODE
admin 0001
admin 0002
011051 0002
011021 0001
011031 0002
create table ta
(XNO varchar(10),
IDKEY varchar(10),
KF_code varchar(10),
JSRID varchar(10)
)
insert into ta
select '00001', '001', '0001', 'admin'
union all
select '00002', '002', '0002', '011051'
union all
select '00001', '003', '0001', 'admin'
union all
select '00002', '004', '0001', '011021'
union all
select '00002', '005', '0002', '011031'
union all
select '00001', '006', '0002', '011031'create table tb
(
JSR_ID varchar(10),
KF_CODE varchar(10)
)
insert into tb
select 'admin', '0001'
union all
select 'admin', '0002'
union all
select '011051', '0002'
union all
select '011021', '0001'
union all
select '011031', '0002' select * from ta A where jsrid = '011051' and kf_code in (select kf_code from tb B where jsr_id = A.jsrid)
==============================
XNO IDKEY KF_code JSRID
---------- ---------- ---------- ----------
00002 002 0002 011051(1 row(s) affected)==============================select * from ta A where jsrid = 'admin' and kf_code in (select kf_code from tb B where jsr_id = A.jsrid)
==============================
XNO IDKEY KF_code JSRID
---------- ---------- ---------- ----------
00001 001 0001 admin
00001 003 0001 admin(2 row(s) affected)
if object_id('[jyb]') is not null drop table [jyb]
go
create table [jyb]([XNO] varchar(5),[IDKEY] varchar(3),[KF_code] varchar(4),[JSRID] varchar(6))
insert [jyb]
select '00001','001','0001','admin' union all
select '00002','002','0002','011051' union all
select '00001','003','0001','admin' union all
select '00002','004','0001','011021' union all
select '00002','005','0002','011031' union all
select '00001','006','0002','011031'
if object_id('[权限表]') is not null drop table [权限表]
go
create table [权限表]([JSR_ID] varchar(6),[KF_CODE] varchar(4))
insert [权限表]
select 'admin','0001' union all
select 'admin','0002' union all
select '011051','0002' union all
select '011021','0001' union all
select '011031','0002'
---查询---
select *
from jyb
where
KF_code in(
select
KF_CODE
from 权限表
where JSR_ID='admin'
)select *
from jyb
where
KF_code in(
select
KF_CODE
from 权限表
where JSR_ID='011051'
)---结果---
/**
XNO IDKEY KF_code JSRID
----- ----- ------- ------
00001 001 0001 admin
00002 002 0002 011051
00001 003 0001 admin
00002 004 0001 011021
00002 005 0002 011031
00001 006 0002 011031(所影响的行数为 6 行)
**/
/**
XNO IDKEY KF_code JSRID
----- ----- ------- ------
00002 002 0002 011051
00002 005 0002 011031
00001 006 0002 011031(所影响的行数为 3 行)
**/
select jyb.* from jyb
where kf_id in(0001,0002) and exits(select * from 权限表 where jsr_id='admin' and kf_id in(0001,0002))select jyb.* from jyb
where kf_id in(0002) and exits(select * from 权限表 where jsr_id='011051' and kf_id in(0002))
因为权限表 有可能存在:
admin 0003
admin 0004
011051 0005
011051 0006