求教,有么有更高效率的写法

求教,有么有更高效的写法
ProductSite 表结构是这样的
id(标识列) productId(商品的ID) siteId(商店的ID)
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2

以siteId 为 1 的为标准 ,我想查出其他商店没有商店中的商品的商品ID
select * from
(select * from ProductSite where ProductSite.siteid = 1) as tb1
where not exists
(select fwqid from
(select productid from ProductSite where ProductSite.siteid = 2) as tb2
where tb1.productid = tb2.productid )
这是我写的,查是查的出来,但是略显冗杂.
求高手指点.

------解决方案--------------------

SQL code

select * from  ProductSite a
where siteid = 1
and not exists
(select 1 from ProductSite 
where siteid <> 1
and productid = a.productid 
)

------解决方案--------------------

SQL code


--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[id] int,
[productId] int,
[siteId] int
)
insert [test]
select 1,1,1 union all
select 2,2,1 union all
select 3,3,1 union all
select 4,1,2 union all
select 5,2,2

select * from test a
where not exists(select 1 from test b 
where a.siteId<>b.siteId and a.productId=b.productId)
/*
id    productId    siteId
---------------------------------
3    3    1
*/

==你是要这个结果吗？

------解决方案--------------------

SQL code


--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[id] int,
[productId] int,
[siteId] int
)
insert [test]
select 1,1,1 union all
select 2,2,1 union all
select 3,3,1 union all
select 4,1,2 union all
select 5,2,2

--如果需要以siteId=1为标准

select * from test a
where not exists(select 1 from test b 
where a.productId=b.productId and a.siteId<>b.siteId)
and a.siteId=1

/*
id    productId    siteId
---------------------------------
3    3    1
*/

求教,有么有更高效率的写法

相关推荐