create table P(pid int, pname varchar(10)) insert P select 1, '书' union all select 2, '报' union all select 3, '衣服' union all select 4, '裤子' go create table O(oid int, pid int, uid int) insert O select 1, 1, 1 union all select 2, 1, 2 union all select 3, 2, 1 union all select 4, 3, 1 go create table U(uid int, uname varchar(10)) insert U select 1, '张' union all select 2, '王'--用戶1 select *, isorder=case when exists(select 1 from O where pid=tmp.pid and uid=1) then 'true' else 'false' end from P tmp --result pid pname isorder ----------- ---------- ------- 1 书 true 2 报 true 3 衣服 true 4 裤子 false(4 row(s) affected)--用戶2 select *, isorder=case when exists(select 1 from O where pid=tmp.pid and uid=2) then 'true' else 'false' end from P tmp --result pid pname isorder ----------- ---------- ------- 1 书 true 2 报 false 3 衣服 false 4 裤子 false(4 row(s) affected)
create table P(pid int, pname varchar(10))
insert P select 1, '书'
union all select 2, '报'
union all select 3, '衣服'
union all select 4, '裤子'
go
create table O(oid int, pid int, uid int)
insert O select 1, 1, 1
union all select 2, 1, 2
union all select 3, 2, 1
union all select 4, 3, 1
go
create table U(uid int, uname varchar(10))
insert U select 1, '张'
union all select 2, '王'--用戶1
select *,
isorder=case when exists(select 1 from O where pid=tmp.pid and uid=1) then 'true' else 'false' end
from P tmp
--result
pid pname isorder
----------- ---------- -------
1 书 true
2 报 true
3 衣服 true
4 裤子 false(4 row(s) affected)--用戶2
select *,
isorder=case when exists(select 1 from O where pid=tmp.pid and uid=2) then 'true' else 'false' end
from P tmp
--result
pid pname isorder
----------- ---------- -------
1 书 true
2 报 false
3 衣服 false
4 裤子 false(4 row(s) affected)
有点没有看明白