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 )
这是我写的,查是查的出来,但是略显冗杂.
求高手指点.
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 )
这是我写的,查是查的出来,但是略显冗杂.
求高手指点.
where siteid = 1
and not exists
(select 1 from ProductSite
where siteid <> 1
and productid = a.productid
)
--> 测试数据:[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,2select * 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
*/==你是要这个结果吗?
--> 测试数据:[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
*/