日期:2014-05-17 浏览次数:20688 次
if OBJECT_ID('tempdb..#tempA', 'u') is not null drop table #tempA;
go
create table #tempA( [会员号] varchar(100), [姓名] varchar(100));
insert #tempA
select '0001','张三' union all
select '0002','李四' union all
select '0003','王五'
if OBJECT_ID('tempdb..#tempB', 'u') is not null drop table #tempB;
go
create table #tempB( [会员号] varchar(100), [会员组号] varchar(100));
insert #tempB
select '0001','1001' union all
select '0004','1001' union all
select '0005','1001' union all
select '0006','1001' union all
select '0002','1002' union all
select '0007','1002' union all
select '0008','1002' union all
select '0003','1003' union all
select '0009','1003'
--SQL:
SELECT A.会员号, C.会员号
FROM #tempA a
INNER JOIN #tempB b
ON A.会员号 = B.会员号
INNER JOIN #tempB c
ON b.会员组号 = c.会员组号
ORDER BY B.会员组号
/*
会员号 会员号
0001 0001
0001 0004
0001 0005
0001 0006
0002 0002
0002 0007
0002 0008
0003 0003
0003 0009
*/