SELECT TOP 100 PERCENT *
FROM (SELECT *, SUBSTRING(Picture, 32, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 63, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 94, 30) AS PICTUREV FROM dbo.products
WHERE (SUBSTRING(PictureB, 94, 30) <> '')
) DERIVEDTBL
ORDER BY ...
我覺得您的子串應該是這樣計算的,：
SUBSTRING(Picture, 0*31+1,30)
SUBSTRING(Picture, 1*31+1,30)
SUBSTRING(Picture, 2*31+1,30)
SUBSTRING(Picture, 3*31+1,30)
所以，在語句中這樣寫也會更好寫，更容易理解，和更不容易出錯。也不影響效率。
妳的方法除了多余建了那麽多視圖，應該說是不錯的方法了，但是實現不了不等長文件名按分隔符分離字串。
如果是分隔符為字串分段，您就必須要用我的自定義函數加我的語句。
如果是您寫的那樣，是固定長度的字串分段，那麽就完全用妳的方法，便是稍作改動，不用建中間視圖
另外，您喜歡視圖的話，可以建壹個視圖：
create view DERIVEDTBL
as
create view
SELECT *, SUBSTRING(Picture, 0*31+1,30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 1*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 2*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 3*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 4*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 5*31+1,30, 30) AS PICTUREV FROM dbo.products WHERE (SUBSTRING(PictureB, 94, 30) <> '')
--....向下任意延伸，只要改SUBSTRING中的序號就行了。
go
調用時這樣就行了：
SELECT TOP N *
FROM DERIVEDTBL
ORDER BY ...
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
壹句解決問題，這樣：
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,1,'|') AS PICTURE,1 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,2,'|') AS PICTURE,2 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,3,'|') AS PICTURE,3 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,4,'|') AS PICTURE,4 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,5,'|') AS PICTURE,5 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,6,'|') AS PICTURE,6 AS SN from TABLENAME
-- . . . . . .
ORDER BY ID,TITLE,SN
還有，您的這個函數求等長字串，不知道為什麽沒有首字串？
SUBSTRING(Picture, 32, 30)
就是這個個？
SUBSTRING(Picture, 1, 30)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
這裏只給您寫了6行,來滿足您每ID最多6張圖片的要求。寫多少行都可以，即每ID最大圖片數為6張。您可以寫100行，1000行都行！
這裏還給您多加了以個序號字段（SN），以保證圖片順序與原排列相同。如果不需要您可以去掉他。
下面的函數是壹個非常有用的自定義數，是用來提取字串中某壹部份的，請您執行壹下，把他建立到您的數據庫中，今後對您的編程會提供很大的方便。當然，本例所用的函數就是這個函數。
create FUNCTION mysplit--將以某分隔符分段的字串,按指定的順序號提取子串
(@str nvarchar(2000),--源字串
@sn int, --提取序號
@Deli varchar(1) --分隔符
)
RETURNS varchar(100)
AS
BEGIN
declare @first int,@last int,@result varchar(1000),@sn0 int
select @sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1)
while @sn0!=@sn
begin
select @sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1
end
if @last-@first-1<0
set @result=''
else
SET @RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1)
RETURN ( @RESULT )
END
GO