求教,有么有更高效的写法
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 awhere siteid = 1and not exists(select 1 from ProductSite where siteid <> 1and 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 allselect 2,2,1 union allselect 3,3,1 union allselect 4,1,2 union allselect 5,2,2select * from test awhere 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 allselect 2,2,1 union allselect 3,3,1 union allselect 4,1,2 union allselect 5,2,2--如果需要以siteId=1为标准select * from test awhere 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*/