现有2表
//新闻表，里面有4个字段放图片pic1,pic2,pic3,pic4
tab_news (nId,sId_FK nTitle,pic1,pic2,pic3,pic4)
如：
1 1 title1 a1.jpg b1.jpg c1.jpg d1.gif
2 1 title2 a2.jpg b2.jpg c2.jpg d2.gif
//杂志编号表，里面也有4张图片
tab_Serial(sId,sTitle,picHome,picA1,picA2,picA3,picA4)
1 sTitle1 a3.jpg b3.jpg c3.jpg d3.jpg
最后我想把所有的图片取出来现在类似这样的数据记录,即把所有的图片提取出来
注意Pic1,pic2,pic3,pic4 的字段值可能为 Null的可能，最好可以也过滤掉。
vId, imgName, sId_FK
1 a1.jpg 1
2 b1.jpg 1
3 c1.jpg 1
4 d1.jpg 1
5 a2.jpg 1
6 b2.jpg 1
7 c2.jpg 1
8 d2.jpg 1
9. a3.jpg 1
10 b3.jpg 1
11 c3.jpg 1
12 d3.jpg 1
------解决方案--------------------
- SQL code
create table tab_news (nId int ,sId_FK int,nTitle varchar(100),pic1 varchar(100),pic2 varchar(100),pic3 varchar(100),pic4 varchar(100))insert into tab_newsselect 1, 1, 'title1', 'a1.jpg', 'b1.jpg', 'c1.jpg', 'd1.gif' union allselect 2, 1, 'title2', 'a2.jpg', 'b2.jpg', 'c2.jpg', 'd2.gif' create table tab_Serial (sId int ,sTitle varchar(100),picHome varchar(100),picA1 varchar(100),picA2 varchar(100),picA3 varchar(100),picA4 varchar(100))insert into tab_Serialselect 1, 'sTitle1 ', '', 'a3.jpg', 'b3.jpg', 'c3.jpg', 'd3.gif';with cte as ( select vId=ROW_NUMBER ()over(partition by b.sId_FK order by b.imgName),* from ( select * from ( select sId_FK , imgName = pic1 from tab_news union all select sId_FK , imgName = pic2 from tab_news union all select sId_FK , imgName = pic3 from tab_news union all select sId_FK , imgName = pic4 from tab_news union all select sId AS sId_FK, imgName = picA1 from tab_Serial union all select sId AS sId_FK, imgName = picA2 from tab_Serial union all select sId AS sId_FK, imgName = picA3 from tab_Serial union all select sId AS sId_FK, imgName = picA4 from tab_Serial ) a ) b)select * from cte--结果vId sId_FK imgName-------------------- ----------- --------1 1 a1.jpg2 1 a2.jpg3 1 a3.jpg4 1 b1.jpg5 1 b2.jpg6 1 b3.jpg7 1 c1.jpg8 1 c2.jpg9 1 c3.jpg10 1 d1.gif11 1 d2.gif12 1 d3.gif