表1
id name1 name2
0 aaa bbb
1 ccc ddd
2 eee fff 表2
group id1 id2 id3
1400 0 1 2
1500 1 2
1600 0 要求输出表
表3
group names
1400 aaa bbb/ccc ddd/eee fff
1500 ccc ddd/eee fff
1600 aaa bbb
请教这样的视图应该怎样写?
id name1 name2
0 aaa bbb
1 ccc ddd
2 eee fff 表2
group id1 id2 id3
1400 0 1 2
1500 1 2
1600 0 要求输出表
表3
group names
1400 aaa bbb/ccc ddd/eee fff
1500 ccc ddd/eee fff
1600 aaa bbb
请教这样的视图应该怎样写?
DECLARE @表1 TABLE(
id int, name1 varchar(10), name2 varchar(10))
INSERT @表1
SELECT 0, 'aaa', 'bbb' UNION ALL
SELECT 1, 'ccc', 'ddd' UNION ALL
SELECT 2, 'eee', 'fff'DECLARE @表2 TABLE(
[group] int, id1 int, id2 int, id3 int)
INSERT @表2
SELECT 1400, 0, 1, 2 UNION ALL
SELECT 1500, 1, 2, NULL UNION ALL
SELECT 1600, 0, NULL, NULL-- 查询
SELECT
A.[group],
names = ISNULL(B1.name1 + ' ' + B1.name2, '')
+ ISNULL('/' + B2.name1 + ' ' + B2.name2, '')
+ ISNULL('/' + B3.name1 + ' ' + B3.name2, '')
FROM @表2 A
LEFT JOIN @表1 B1
ON A.id1 = B1.id
LEFT JOIN @表1 B2
ON A.id2 = B2.id
LEFT JOIN @表1 B3
ON A.id3 = B3.id
----------- ----------------------------
1400 aaa bbb/ccc ddd/eee fff
1500 ccc ddd/eee fff
1600 aaa bbb(3 行受影响)
如果表1中的数据一直增加,那查询中的B1是不是也要一直增加?
这样是不是有些问题?