很有意思的SQL 查询

Question

一张表t1(Place, Name)，   
南京，张一
南京，张二
南京，张三
南京，张四
北京，小一
北京，小二
北京，小三另一张表t2(Place, Name)，   
南京，王一
南京，王二
南京，王三
北京，大一
北京，大二
北京，大三
北京，大四
要求查询的结果是
南京 张一 王一
南京 张二 王二
南京 张三 王三
南京 张四 （空）
北京 小一 大一
北京 小二 大二
北京 小三 大三
北京 （空） 大四要说明的，并不是一定要 张一 和王一 搭配，而是某个地点 比如南京的纪录，两两组配，只要出现一次，下次就不要出现了。
蛮有难度的，请各位
指点。

Answer 1

Create database Testdb
go
use Testdb
goCREATE TABLE [dbo].[testTable1](
[Pkid] [int] IDENTITY(1,1) NOT NULL,
[tPlace] [nvarchar](255) NULL,
[tName] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestTable1] PRIMARY KEY CLUSTERED 
(
[Pkid] ASC
) ON [PRIMARY]
) ON [PRIMARY]CREATE TABLE [dbo].[testTable2](
[Pkid] [int] IDENTITY(1,1) NOT NULL,
[tPlace] [nvarchar](255) NULL,
[tName] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestTable2] PRIMARY KEY CLUSTERED 
(
[Pkid] ASC
) ON [PRIMARY]
) ON [PRIMARY]SET Identity_Insert dbo.testTable1 ON
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
1,
'南京','张一')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
2,
'南京','张二')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
3,
'南京','张三')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
4,
'南京','张四')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
5,
'北京','小一')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
6,
'北京','小二')
GO
INSERT INTO dbo.testTable1(
Pkid,tPlace,tName)
VALUES (
7,
'北京','小三')
GO
Set Identity_Insert dbo.testTable1 OFFSET Identity_Insert dbo.testTable2 ON
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
1,
'南京','王一')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
2,
'南京','王二')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
3,
'南京','王三')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
4,
'北京','大一')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
5,
'北京','大二')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
6,
'北京','大三')
GO
INSERT INTO dbo.testTable2(
Pkid,tPlace,tName)
VALUES (
7,
'北京','大四')
GO
Set Identity_Insert dbo.testTable2 OFF--使用TableVars
Declare @TableVar Table 
(PKID int IDENTITY(1,1)  Primary Key ,tPlace nvarchar(50),tName1 nvarchar(50),tName2 nvarchar(50),tFrom int )
Declare @TableVar2 Table 
(PKID int IDENTITY(1,1)  Primary Key ,tPlace nvarchar(50),tName1 nvarchar(50),tName2 nvarchar(50),tID int)
--产生数据源表变量
insert into @TableVar(tPlace,tName1,tFrom) (select tPlace,tName,1 from testTable1 union all select tPlace,tName,2 from testTable2)
--select * from @TableVar
Declare @Flag int
Declare @tempID int
Declare @tempID2 int
Declare @CountID int
Declare @tempPlace nvarchar(50)
Declare @tempName nvarchar(50)
Declare @tempName2 nvarchar(50)
set @CountID=(select count(PKID) from @TableVar)
while(@CountID>0)
Begin
set @Flag=(select Top 1 tFrom from @TableVar)
set @tempPlace=(select Top 1 tPlace from @TableVar where tFrom=@Flag)
set @tempID=(select Top 1 PKID from @TableVar where tFrom=@Flag and tPlace=@tempPlace)
set @tempID2=(select Top 1 PKID from @TableVar where tFrom<>@Flag and tPlace=@tempPlace) set @tempName=(select Top 1 tName1 from @TableVar where tFrom=@Flag and tPlace=@tempPlace)
set @tempName2=(select Top 1 tName1 from @TableVar where tFrom<>@Flag and tPlace=@tempPlace)
insert into @TableVar2 (tPlace,tID) select Top 1 tPlace,PKID from @TableVar where (tPlace=@tempPlace) 
--print @tempName
--print @tempName2
if(@Flag=1)
update @TableVar2 set tName1=@tempName ,tName2=@tempName2 where tID=@tempID
else if(@Flag=2)
update @TableVar2 set tName2=@tempName ,tName1=@tempName2 where tID=@tempID
else
continue
delete from @TableVar where (PKID=@tempID or PKID=@tempID2) and Tplace=@tempPlace
    set @CountID=(select count(PKID) from @TableVar)
END
Select * from   @TableVar2   
结果：
1 南京 张一 王一 1
2 南京 张二 王二 2
3 南京 张三 王三 3
4 南京 张四 NULL 4
5 北京 小一 大一 5
6 北京 小二 大二 6
7 北京 小三 大三 7
8 北京 NULL 大四 14--use Master 
--go
--drop database Testdb

Answer 2

select distinct t1.place,t1.name,t2.name from t1
inner join t2 on t1.place=t2.place

Answer 3

SQL2005中可以不借助临时表,用row_number实现借助临时表所identity的特性.
CREATE TABLE t1 (Place VARCHAR(10),   Name VARCHAR(10))       
INSERT t1 SELECT '南京','张一' 
UNION ALL SELECT '南京','张二' 
UNION ALL SELECT '南京','张三' 
UNION ALL SELECT '南京','张四' 
UNION ALL SELECT '北京','小一' 
UNION ALL SELECT '北京','小二' 
UNION ALL SELECT '北京','小三' 
UNION ALL SELECT '武汉','大四' 
CREATE TABLE t2 (Place VARCHAR(10),   Name VARCHAR(10))       
INSERT t2 SELECT '南京','王一' 
UNION ALL SELECT '南京','王二' 
UNION ALL SELECT '南京','王x' 
UNION ALL SELECT '南京','王y' 
UNION ALL SELECT '南京','王z' 
UNION ALL SELECT '南京','王a' 
UNION ALL SELECT '北京','大一' 
UNION ALL SELECT '北京','大二' 
UNION ALL SELECT '北京','大三' 
UNION ALL SELECT '北京','大四' 
UNION ALL SELECT '西安','大四' 
SELECT IDENTITY(int) ID ,* INTO #1 FROM t1 ORDER BY Place
SELECT IDENTITY(int) ID ,* INTO #2 FROM t2 ORDER BY Place
SELECT ISNULL(a.Place,b.Place) Place,
a.Name,
b.Name
FROM
(SELECT Place,Name,
(SELECT COUNT(*) FROM #1 WHERE Place=x.Place AND ID<=x.ID
) idx 
FROM #1 x
) a
FULL JOIN
(SELECT Place,Name,
(SELECT COUNT(*) FROM #2 WHERE Place=x.Place AND ID<=x.ID
) idx 
FROM #2 x
) b
ON a.Place=b.Place AND b.idx=ISNULL(a.idx,b.idx)DROP TABLE t1,t2,#1,#2
/*
Place NameFromT1 NameFromT2
北京 小一 大一
北京 小二 大二
北京 小三 大三
北京 NULL 大四
南京 张一 王z
南京 张二 王a
南京 张三 王一
南京 张四 王二
南京 NULL 王x
南京 NULL 王y
西安 NULL 大四
武汉 大四 NULL
*/

Answer 4

没试过四楼的方法，不过我并没使用临时表#temp，而是使用了表变量 Table
是为了提高效率。四楼的方法应该也可以改成表变量法。
而表变量在sql2000就支持了！　

Answer 5

数据量小,表变量跟临时表没有区别.
数据量大,临时表是会比表变量慢,但几十万的数据放在表变量里你觉得合理吗?
有一个原则, 效率高的,不一定是最好的. 看你是要效率,还是要减服务器压力. 多的就不说了.另外,用表变量还是临时表,都是为了一个identity列.sql2005中可以用row_number

Answer 6

getnetrmb wrote:
---------
这不是行列转换代码吗
-------------------------很负责任的告诉你,不是.

Answer 7

同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!同样的结构为什么要分两张表!而且没设置ID..这样查询会很麻烦!!