27,579
社区成员
发帖
与我相关
我的任务
分享一求一条SQL语句
A表
id name
3 张三
5 王五
7 陈七
B表
id cname chren
3 张铁儿 儿子
3 张小花 女儿
3 张小文 女儿
7 陈文壮 儿子
一求一条SQL语句，要求结果如下，麻烦大家
张三 张铁儿 张小花 张小文
王五 null null null
陈七 陈文壮 null null
id name
3 张三
5 王五
7 陈七
B表
id cname chren
3 张铁儿 儿子
3 张小花 女儿
3 张小文 女儿
7 陈文壮 儿子
一求一条SQL语句，要求结果如下，麻烦大家
张三 张铁儿 张小花 张小文
王五 null null null
陈七 陈文壮 null null
...全文
请发表友善的回复…
发表回复
liangCK 2009-08-25
- 打赏
- 举报
--> 生成测试数据: #tb1
CREATE TABLE #tb1(id INT,name VARCHAR(4))
INSERT INTO #tb1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
--> 生成测试数据: #tb2
CREATE TABLE #tb2 (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO #tb2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 5,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子' UNION ALL
SELECT 5,'王心','配偶'
--SQL查询如下:
DECLARE @sql VARCHAR(8000);
SET @sql = '';
SELECT
@sql = @sql + ',MAX(CASE WHEN flag = '+RTRIM(number+1)
+' THEN B.cname ELSE '''' END) AS ['
+ CASE WHEN number = 0 THEN '配偶' ELSE 'chren' + RTRIM(number) END
+ ']'
FROM master.dbo.spt_values
WHERE type = 'p' AND number <= (SELECT TOP 1 COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);
----------
SELECT flag=CAST(NULL AS INT),* INTO #tmp FROM #tb2
ORDER BY id,CASE WHEN chren = '配偶' THEN 0 ELSE 1 END; --这里ORDER BY 一下.
DECLARE @flag INT,@id INT;
UPDATE #tmp SET
@flag = CASE WHEN id = @id THEN @flag + 1
ELSE CASE WHEN chren = '配偶' THEN 1 ELSE 2 END
END,
@id = id,
flag = @flag;
EXEC('SELECT A.id,A.name'+@sql+' FROM #tb1 AS A LEFT JOIN #tmp AS B
ON A.id = B.id GROUP BY A.id,A.name ORDER BY A.id');
DROP TABLE #tb1,#tb2;
DROP TABLE #tmp;
/*
id name 配偶 chren1 chren2
----------- ---- ------ ------ ------
3 张三 张铁儿 张小花
5 王五 王心 张小文
7 陈七 陈文壮
(3 行受影响)
*/
zhlym 2009-08-25
- 打赏
- 举报
最后的问题已解决，再次感谢liangck及大家的指点
zhlym 2009-08-25
- 打赏
- 举报
感谢，liangck 还发现一个问题，因B表不完全都有A表对应的配偶存在，现在要在结果中chren1这列当没有配偶时留空，接着chren2之后的列再显子女，麻烦再指点一下
liangCK 2009-08-25
- 打赏
- 举报
--> 生成测试数据: #tb1
CREATE TABLE #tb1(id INT,name VARCHAR(4))
INSERT INTO #tb1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
--> 生成测试数据: #tb2
CREATE TABLE #tb2 (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO #tb2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 5,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子' UNION ALL
SELECT 5,'王心','配偶'
--SQL查询如下:
DECLARE @sql VARCHAR(8000);
SET @sql = '';
SELECT
@sql = @sql + ',MAX(CASE WHEN flag = '+RTRIM(number+1)
+' THEN B.cname ELSE '''' END) AS [chren'+RTRIM(number+1)+']'
FROM master.dbo.spt_values
WHERE type = 'p' AND number < (SELECT TOP 1 COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);
----------
SELECT flag=CAST(NULL AS INT),* INTO #tmp FROM #tb2
ORDER BY id,CASE WHEN chren = '配偶' THEN 0 ELSE 1 END; --这里ORDER BY 一下.
DECLARE @flag INT,@id INT;
UPDATE #tmp SET
@flag = CASE WHEN id = @id THEN @flag + 1 ELSE 1 END,
@id = id,
flag = @flag;
EXEC('SELECT A.id,A.name'+@sql+' FROM #tb1 AS A LEFT JOIN #tmp AS B
ON A.id = B.id GROUP BY A.id,A.name ORDER BY A.id');
DROP TABLE #tb1,#tb2;
DROP TABLE #tmp;黄_瓜 2009-08-25
- 打赏
- 举报
[Quote=引用 13 楼 zhlym 的回复:]
嗯，每后一组调通了，用了临时表，总觉得有点缺陷，不知不用临时表行不行？
[/Quote]
不行
嗯，每后一组调通了，用了临时表，总觉得有点缺陷，不知不用临时表行不行？
[/Quote]
不行
zhlym 2009-08-25
- 打赏
- 举报
嗯，每后一组调通了，用了临时表，总觉得有点缺陷，不知不用临时表行不行？
liangCK 2009-08-25
- 打赏
- 举报
9f改改..
将DECLARE @sql VARCHAR(MAX);
改为 DECLARE @sql VARCHAR(8000);
将
WHERE type = 'p' AND number < (SELECT TOP(1) COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);
改为
WHERE type = 'p' AND number < (SELECT TOP 1 COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);liangCK 2009-08-25
- 打赏
- 举报
[Quote=引用 10 楼 zhlym 的回复:]
liangCK 你都是在SQL2005下调试的吧？偶在SQL2000下调不通。。。
[/Quote]
9f
liangCK 你都是在SQL2005下调试的吧？偶在SQL2000下调不通。。。
[/Quote]
9f
zhlym 2009-08-25
- 打赏
- 举报
liangCK 你都是在SQL2005下调试的吧？偶在SQL2000下调不通。。。
liangCK 2009-08-25
- 打赏
- 举报
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 18:37:59
-------------------------------------
--> 生成测试数据: #tb1
CREATE TABLE #tb1(id INT,name VARCHAR(4))
INSERT INTO #tb1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
--> 生成测试数据: #tb2
CREATE TABLE #tb2 (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO #tb2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 3,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子'
--SQL查询如下:
DECLARE @sql VARCHAR(MAX);
SET @sql = '';
SELECT
@sql = @sql + ',MAX(CASE WHEN flag = '+RTRIM(number+1)
+' THEN B.cname ELSE '''' END) AS [chren'+RTRIM(number+1)+']'
FROM master.dbo.spt_values
WHERE type = 'p' AND number < (SELECT TOP(1) COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);
----------
SELECT flag=CAST(NULL AS INT),* INTO #tmp FROM #tb2 ORDER BY id;
DECLARE @flag INT,@id INT;
UPDATE #tmp SET
@flag = CASE WHEN id = @id THEN @flag + 1 ELSE 1 END,
@id = id,
flag = @flag;
EXEC('SELECT A.id,A.name'+@sql+' FROM #tb1 AS A LEFT JOIN #tmp AS B
ON A.id = B.id GROUP BY A.id,A.name ORDER BY A.id');
DROP TABLE #tb1,#tb2;
DROP TABLE #tmp;
/*
id name chren1 chren2 chren3
----------- ---- ------ ------ ------
3 张三 张铁儿 张小花 张小文
5 王五
7 陈七 陈文壮
(3 行受影响)
*/
feixianxxx 2009-08-25
- 打赏
- 举报
上面是2000的做法 并且假设一个人儿女最多三个。。。
liangCK 2009-08-25
- 打赏
- 举报
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 18:37:59
-------------------------------------
--> 生成测试数据: #tb1
CREATE TABLE #tb1(id INT,name VARCHAR(4))
INSERT INTO #tb1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
--> 生成测试数据: #tb2
CREATE TABLE #tb2 (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO #tb2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 3,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子'
--SQL查询如下:
DECLARE @sql VARCHAR(MAX);
SET @sql = '';
SELECT
@sql = @sql + ',MAX(CASE WHEN rowid = '+RTRIM(number+1)
+' THEN B.cname ELSE '''' END) AS [chren'+RTRIM(number+1)+']'
FROM master.dbo.spt_values
WHERE type = 'p' AND number < (SELECT TOP(1) COUNT(*) FROM #tb2
GROUP BY id ORDER BY COUNT(*) DESC);
EXEC('SELECT A.id,A.name'+@sql+' FROM #tb1 AS A LEFT JOIN
(SELECT rowid=ROW_NUMBER() OVER(PARTITION BY id ORDER BY id),id,cname,chren
FROM #tb2) AS B
ON A.id = B.id GROUP BY A.id,A.name ORDER BY A.id');
DROP TABLE #tb1,#tb2;
/*
id name chren1 chren2 chren3
----------- ---- ------ ------ ------
3 张三 张铁儿 张小花 张小文
5 王五
7 陈七 陈文壮
(3 行受影响)
*/feixianxxx 2009-08-25
- 打赏
- 举报
create TABLE ka1 (id INT,name VARCHAR(4))
INSERT INTO ka1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
create TABLE ka2 (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO ka2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 3,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子'
select IDa=(select COUNT(*) from ka2 where id=k.id and k.cname>=cname ),*
into # from ka2 k
--select * from #
select a.name,
ernv1=MAX(case when ida=1 then cname else '' end),
ernv2=MAX(case when ida=2 then cname else '' end),
ernv3=MAX(case when ida=3 then cname else '' end)
from ka1 a left join # b on a.id=b.id
group by a.name
/*
name ernv1 ernv2 ernv3
---- ------ ------ ------
陈七 陈文壮
王五
张三 张铁儿 张小花 张小文
*/zhlym 2009-08-25
- 打赏
- 举报
就是子女数是动态的，还有sql2000没有ROW_NUMBER()这一用法
feixianxxx 2009-08-25
- 打赏
- 举报
万一一个人有4个子女怎么办
zhlym 2009-08-25
- 打赏
- 举报
[Quote=引用 1 楼 liangck 的回复:]
SQL codeSELECT
A.id,
A.name,MAX(CASEWHEN B.rowid=1THEN B.nameELSE''END)AS chren1,MAX(CASEWHEN B.rowid=2THEN B.nameELSE''END)AS chren2,MAX(CASEWHEN B.rowid=3THEN B.nameELSE''END )AS chren3FROM ¡
[/Quote]
子女数是动态的，这样B.rowid = 1...?会不会有问题呢？
SQL codeSELECT
A.id,
A.name,MAX(CASEWHEN B.rowid=1THEN B.nameELSE''END)AS chren1,MAX(CASEWHEN B.rowid=2THEN B.nameELSE''END)AS chren2,MAX(CASEWHEN B.rowid=3THEN B.nameELSE''END )AS chren3FROM ¡
[/Quote]
子女数是动态的，这样B.rowid = 1...?会不会有问题呢？
liangCK 2009-08-25
- 打赏
- 举报
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-25 18:37:59
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (id INT,name VARCHAR(4))
INSERT INTO @tb1
SELECT 3,'张三' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 7,'陈七'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (id INT,cname VARCHAR(6),chren VARCHAR(4))
INSERT INTO @tb2
SELECT 3,'张铁儿','儿子' UNION ALL
SELECT 3,'张小花','女儿' UNION ALL
SELECT 3,'张小文','女儿' UNION ALL
SELECT 7,'陈文壮','儿子'
--SQL查询如下:
SELECT
A.id,
A.name,
MAX(CASE WHEN B.rowid = 1 THEN B.cname ELSE '' END) AS chren1,
MAX(CASE WHEN B.rowid = 2 THEN B.cname ELSE '' END) AS chren2,
MAX(CASE WHEN B.rowid = 3 THEN B.cname ELSE '' END ) AS chren3
FROM @tb1 AS A
LEFT JOIN (SELECT rowid=ROW_NUMBER() OVER(PARTITION BY id ORDER BY id),
id,cname,chren FROM @tb2) AS B
ON A.id = B.id
GROUP BY A.id,A.name;
/*
id name chren1 chren2 chren3
----------- ---- ------ ------ ------
7 陈七 陈文壮
5 王五
3 张三 张铁儿 张小花 张小文
(3 行受影响)
*/liangCK 2009-08-25
- 打赏
- 举报
SELECT
A.id,
A.name,
MAX(CASE WHEN B.rowid = 1 THEN B.name ELSE '' END) AS chren1,
MAX(CASE WHEN B.rowid = 2 THEN B.name ELSE '' END) AS chren2,
MAX(CASE WHEN B.rowid = 3 THEN B.name ELSE '' END ) AS chren3
FROM tb1 AS A
LEFT JOIN (SELECT rowid=ROW_NUMBER() OVER(PARTITION BY id ORDER BY id),
id,cname,chren FROM tb2) AS B
ON A.id = B.id
GROUP BY A.id,A.name;