<myTable1>UID 1,2,3TopicID1 1,2,3 //与myTable2的TopicID对应
TopicID2 4,5,7 //与myTable2的TopicID对应<myTable2>TopicID 1,2,3,4,5,6,7
CID 11,12,13,14,15,16,17要建立一个视图,查询出来的结果为UID TopicID1 CID1 TopicID2 CID2
1 1 11 4 14
2 2 12 5 15
3 3 13 7 17请问这样的视图查询该怎么编写呢?
TopicID2 4,5,7 //与myTable2的TopicID对应<myTable2>TopicID 1,2,3,4,5,6,7
CID 11,12,13,14,15,16,17要建立一个视图,查询出来的结果为UID TopicID1 CID1 TopicID2 CID2
1 1 11 4 14
2 2 12 5 15
3 3 13 7 17请问这样的视图查询该怎么编写呢?
insert A select 1, 1, 4
union all select 2, 2, 5
union all select 3, 3, 7create table B(TopicID int, CID int)
insert B select 1,11
union all select 2,12
union all select 3,13
union all select 4,14
union all select 5,15
union all select 6,16
union all select 7,17select UID
TopicID1,
CID1=(select CID from B where TopicID=TopicID1),
TopicID2,
CID2=(select CID from B where TopicID=TopicID2)
from A
--result
TopicID1 CID1 TopicID2 CID2
----------- ----------- ----------- -----------
1 11 4 14
2 12 5 15
3 13 7 17(3 row(s) affected)
select UID,
TopicID1,
CID1=(select CID from B where TopicID=TopicID1),
TopicID2,
CID2=(select CID from B where TopicID=TopicID2)
from A
--resultUID TopicID1 CID1 TopicID2 CID2
----------- ----------- ----------- ----------- -----------
1 1 11 4 14
2 2 12 5 15
3 3 13 7 17(3 row(s) affected)
as
select m.uid , m.TopicID1 , m.cid1 , n.TopicID2 , n.cid2 from
(select mytable1.uid, mytable1.TopicID1 , mytable2.cid as cid1 from mytable1 , mytable2 where mytable1.topicid1 = mytable2.topicid) m,
(select mytable1.uid, mytable1.TopicID2 , mytable2.cid as cid2 from mytable1 , mytable2 where mytable1.topicid2 = mytable2.topicid) n
where m.uid = n.uid