SQL中exists的用法

[wusenet041]

表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语句 但是不知道怎么写 高手帮帮忙啊

[yygyogfny]

select * from 表A A where jsrid = '查询用户的ID' and kf_code in (select kf_code from 表B B where jsr_id = A.jsrid)

[百年树人]

select * 
from jyb
where 
  jsrid in(
            select 
              KF_CODE
            from 权限表
              where JSR_ID='admin'
           )

[dawugui]

--用变量.

declare @jsrid as varchar(10)
set @jsrid = 'admin'

select b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsrid

set @jsrid = '011051'

select b.* from a , b where a.jsrid = b.jsr_id and a.jsrid = @jsrid

[百年树人]

[Quote=引用 2 楼 josy 的回复:]
SQL codeselect *
from jyb
where
jsrid in(
select
KF_CODE
from 权限表
where JSR_ID='admin'
)
[/Quote]
更正一个字段

select * 
from jyb
where 
  KF_code in(
            select 
              KF_CODE
            from 权限表
              where JSR_ID='admin'
           )

[肥龙上天]

这一个没有使用到code 啊
直接链表就OK了

[dawugui]

create table A(JSRID varchar(10))
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')
go

declare @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 

[肥龙上天]

select * from 表A a join 表B b on a.KF_CODE = b.KF_CODE where a.JSR_ID ='admin'

[yygyogfny]

表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)

[yygyogfny]

大虾们手好快哦~~~

[ermuzi]

大虾们手好快哦~~~

[百年树人]

---测试数据---
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 行）
**/

[jjjssh]

我是新手
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