22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: [users] -- 业务员表
if object_id('[users]') is not null drop table [users]
go
create table [users] (id int,name varchar(1))
insert into [users]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
--> 测试数据: [USER_GRINT] 业务员授权表
if object_id('[USER_GRINT]') is not null drop table [USER_GRINT]
go
create table [USER_GRINT] (USERID int,GRINTiD int)
insert into [USER_GRINT]
select 1,2
--> 测试数据: [userYw] 业务员单据表
if object_id('[userYw]') is not null drop table [userYw]
go
create table [userYw] (userid int,ywid int)
insert into [userYw]
select 1,11 union all
select 1,22 union all
select 2,21 union all
select 2,23 union all
select 3,24
-- 查询身份 A
select c.*
from [userYw] C
JOIN
(
SELECT ID FROM USERS WHERE name='A'
UNION
SELECT USERID FROM [USER_GRINT],USERS WHERE GRINTiD=ID AND name='A'
) T
ON C.userid=T.ID
/*
userid ywid
----------- -----------
1 11
1 22
(2 行受影响)
*/
-- 查询身份 B
select c.*
from [userYw] C
JOIN
(
SELECT ID FROM USERS WHERE name='B'
UNION
SELECT USERID FROM [USER_GRINT],USERS WHERE GRINTiD=ID AND name='B'
) T
ON C.userid=T.ID
/*
userid ywid
----------- -----------
1 11
1 22
2 21
2 23
*/
-- 查询身份 C
select c.*
from [userYw] C
JOIN
(
SELECT ID FROM USERS WHERE name='C'
UNION
SELECT USERID FROM [USER_GRINT],USERS WHERE GRINTiD=ID AND name='C'
) T
ON C.userid=T.ID
/*
userid ywid
----------- -----------
3 24
(1 行受影响)
*/
单据id 授权
1 A
2 A
1 B
2 C
select * from table where 授权=‘A’