| |

VerySource

 Forgot password?
 Register
Search
View: 888|Reply: 5

A headache

[Copy link]

1

Threads

13

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

Post time: 2020-1-6 23:40:01
| Show all posts |Read mode
QXXH QXMC
1 Fill in the post
2 Post approval
3 Fill in the receipt
4 receipt approval

Privilege-Person Correspondence Table
XH QXXH YHMC
1 2 three
2 3 photos
3 1 Li Si
4 3 Li Si
5 4 Li Si
I want to generate a query like this: list what permissions all users have, and list all permissions at the same time.
The query results are as follows:
XH QXXH QXMC YHMC has this permission
1 1 Fill in the post Zhang San false
2 2 Posting approval Zhang San true
3 3 Fill in the receipt
4 4 Received Approval Zhang San false
5 1 Fill in the post Li Si true
6 2 Post approval Li Si false
7 3 Fill in the receipt Li Si true
8 4 Received approval Li Si true
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-1-7 08:18:02
| Show all posts
create table A (QXXH int, QXMC nvarchar (10))
insert A select 1, 'Fill in a post'
union all select 2, 'Post approval'
union all select 3, 'Fill in the receipt'
union all select 4, 'Receive Approval'

create table B (XH int, QXXH int, YHMC nvarchar (10))
insert B select 1, 2, '张三'
union all select 2, 3, '张三'
union all select 3, 1, '李四'
union all select 4, 3, '李四'
union all select 5, 4, '李四'

select tmp. *, do you have this permission = case when B.XH is null then 'False' else 'True' end from
(
select * from
(select distinct YHMC from B) as B
cross join A
tmp
left join B on tmp.QXXH = B.QXXH and tmp.YHMC = B.YHMC

--result
Does YHMC QXXH QXMC have this permission
---------- ----------- ---------- ------
Li Si 1 Post True
Li Si 2 Posting approval False
Li Si 3 Received True
Li Si 4 Receive receipt approval True
Zhang San 1 Post False
Zhang San 2 Posting approval True
Zhang San 3 Receipt Fill in True
Zhang San 4 Incoming Approval False

(8 row (s) affected)
Reply

Use magic Report

0

Threads

48

Posts

30.00

Credits

Newbie

Rank: 1

Credits
30.00

 China

Post time: 2020-1-7 08:33:01
| Show all posts
select a.xh, b.qxxh, b.qxmc, a.yhmc, qxf = case when b.qxxh is null then 0 else 1 end
from xt_qxry a full join table1 on a.qxxh = b.qxxh
Reply

Use magic Report

0

Threads

17

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

Post time: 2020-1-7 11:03:01
| Show all posts
create table tmp1 (QXXH int, QXMC nvarchar (20))
go

create table tmp2 (XH int, QXXH int, YHMC nvarchar (20))

go

select c. *, do you have permission = case when d.xh is null then 'false' else 'true' end
 from
(
 select * from tmp1 a cross join (select distinct yhmc from tmp2) b
) c

left join tmp2 d on c.qxxh = d.qxxh and c.yhmc = d.yhmc
Reply

Use magic Report

1

Threads

13

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

 Author| Post time: 2020-1-7 11:27:01
| Show all posts
Well, thank youfanfan1980
Reply

Use magic Report

0

Threads

8

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

Post time: 2020-1-7 12:27:01
| Show all posts
SELECT B.YHMC, A.QXXH, A.QXMC, 'TRUE' AS QX
FROM T_QX A, T_QX2 B
WHERE B.YHMC = '张三' AND A.QXXH = B.QXXH
UNION
SELECT distinct B.YHMC, A.QXXH, A.QXMC, 'FALSE' AS QX
FROM T_QX A, T_QX2 B where B.YHMC = '张三' AND
 a.QXXH NOT IN
(SELECT QXXH FROM T_QX2 WHERE YHMC = '张三')
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list