USE tempdb GO if object_id('tb') is not null drop table [tb] go create table tb( pid varchar(20), pname varchar(20) ) insert into tb select 'a','产品a' union all select 'b','产品b' union all select 'c','产品c' if object_id('tb1') is not null drop table [tb1] go create table tb1 ( pid varchar(20), uid varchar(20) ) insert into tb1 select 'a','wang' union all select 'b','wang' insert into tb1 select 'a','lin'GO SELECT t1.*,ENABLED=CASE WHEN t2.Pid IS NULL THEN 0 ELSE 1 END FROM (SELECT a.*,b.UID from tb AS a,(SELECT DISTINCT Uid FROM tb1) AS b) AS t1 LEFT JOIN tb1 AS t2 ON t1.pid=t2.pid AND t1.Uid=t2.Uid
Select a.pid,a.pname,sum(case when uid='wang' then 1 else 0 end) coun from tb a left join tb1 b on a.pid=b.pid where b.uid is null or b.uid='wang' group by a.pid,a.pname
select a.*,b.uid,ENABLED=CASE WHEN b.Pid IS NULL THEN 0 ELSE 1 END from tb as a left join tb1 as b on a.pid=b.pid where b.uid='wang'
GO
if object_id('tb') is not null
drop table [tb]
go
create table tb(
pid varchar(20),
pname varchar(20)
)
insert into tb select 'a','产品a' union all select 'b','产品b' union all select 'c','产品c'
if object_id('tb1') is not null
drop table [tb1]
go
create table tb1 (
pid varchar(20),
uid varchar(20)
)
insert into tb1 select 'a','wang' union all select 'b','wang'
insert into tb1 select 'a','lin'GO
SELECT
t1.*,ENABLED=CASE WHEN t2.Pid IS NULL THEN 0 ELSE 1 END
FROM (SELECT a.*,b.UID from tb AS a,(SELECT DISTINCT Uid FROM tb1) AS b) AS t1
LEFT JOIN tb1 AS t2 ON t1.pid=t2.pid AND t1.Uid=t2.Uid
from tb a left join tb1 b on a.pid=b.pid where b.uid is null or b.uid='wang' group by a.pid,a.pname
select a.*,b.uid,ENABLED=CASE WHEN b.Pid IS NULL THEN 0 ELSE 1 END
from tb as a left join tb1 as b
on a.pid=b.pid
where b.uid='wang'