table1 table2
id name id t1_id km cj
1 张三 1 1 语文 85
1 李四 2 1 数学 90
3 2 语文 90
4 2 数学 95
怎么写结果如下的SQL 求大神指点!!
id name km1 cj1 km2 cj2
1 张三 语文 85 数学 90
2 李四 语文 85 数学 90
id name id t1_id km cj
1 张三 1 1 语文 85
1 李四 2 1 数学 90
3 2 语文 90
4 2 数学 95
怎么写结果如下的SQL 求大神指点!!
id name km1 cj1 km2 cj2
1 张三 语文 85 数学 90
2 李四 语文 85 数学 90
(
id INT PRIMARY KEY IDENTITY(1,1),
[name] VARCHAR(50)
)CREATE TABLE table2
(
id INT PRIMARY KEY IDENTITY(1,1),
t1_id INT,
km VARCHAR(50),
cj int
)
INSERT INTO table1
SELECT '张三'
UNION
SELECT '李四'INSERT INTO table2
SELECT 1,'语文',85
UNION
SELECT 1,'数学',90
UNION
SELECT 2,'语文',90
UNION
SELECT 2,'数学',95SELECT * FROM table1
SELECT * FROM table2
DROP TABLE table2SELECT ROW_NUMBER() OVER (ORDER BY a.id) AS numid,a.id,a.[name],b.km,b.cj INTO #Table3 FROM table1 a
CROSS JOIN table2 b WHERE a.id=b.t1_id
SELECT a.id,a.name,a.km AS km1 ,a.cj AS cj1,b.km AS km2,b.cj AS cj2 FROM #Table3 a
LEFT JOIN #Table3 b ON a.id=b.id AND a.numid<>b.numid
WHERE a.numid IN (
SELECT MAX(numid) FROM #Table3 GROUP BY id
)DROP TABLE #Table3
http://www.cnblogs.com/worfdream/articles/2409162.html