把两个表的主键ID 组合到另一个表
但新表必须是 每个AreaId 都有所有的CategoryID
例:表A (AreaId 是主键)
AreaId name
1 广州
2 东莞
3 佛山
..... ....
表B (CategoryID 是主键)
CategoryID name
301 电子
302 服装
... ....
15000 功夫
表C (id是主键)
AreaId CategoryID
1 301
1 302
1 15000
... ....
2 301
2 302
2 15000
... ...
3 301
.... ....
但新表必须是 每个AreaId 都有所有的CategoryID
例:表A (AreaId 是主键)
AreaId name
1 广州
2 东莞
3 佛山
..... ....
表B (CategoryID 是主键)
CategoryID name
301 电子
302 服装
... ....
15000 功夫
表C (id是主键)
AreaId CategoryID
1 301
1 302
1 15000
... ....
2 301
2 302
2 15000
... ...
3 301
.... ....
SELECT A.AREAID,B.CATEGORYID
FROM A,B
SELECT A.AREAID,B.CATEGORYID
FROM A,B
WHERE NOT EXISTS (SELECT 1 FROM C WHERE C.AREAID=A.AREAID AND C.CATEGORYID=B.CATEGORYID)
select * from (select AreaId from A)a cross join (select CategoryID from B)b
except select * from C
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-29 10:47:39
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([AreaId] int,[name] varchar(4))
insert [A]
select 1,'广州' union all
select 2,'东莞' union all
select 3,'佛山'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([CategoryID] int,[name] varchar(4))
insert [B]
select 301,'电子' union all
select 302,'服装' union all
select 15000,'功夫'
--------------开始查询--------------------------
select * from (select distinct AreaId from a)a cross join (select distinct CategoryID from b)b
----------------结果----------------------------
/*
(3 行受影响)(3 行受影响)
AreaId CategoryID
----------- -----------
1 301
1 302
1 15000
2 301
2 302
2 15000
3 301
3 302
3 15000(9 行受影响)
*/
C
select
*
from
(select distinct AreaId from A)a
cross join
(select distinct CategoryID from B)b
where
not exists(SELECT 1 FROM C WHERE C.AREAID=A.AREAID AND C.CATEGORYID=B.CATEGORYID)