串联动态SQL列
|
我有三张桌子。 tblProducts和tblOption和tblOptionValue。 （T-SQL）
我可以使用以下查询获取产品的所有可能组合：
DECLARE @ProductId INT, @Query NVARCHAR(MAX), @ProductOptionGroupId INT
SET @ProductId = 69
SET @Query = \'\'
DECLARE CC CURSOR FOR
SELECT DISTINCT OptionID
FROM tblOption
WHERE ProductId = @ProductId
OPEN CC
FETCH NEXT FROM CC INTO @ProductOptionGroupId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = @Query + \'(SELECT DISTINCT tblOptionValue.Name FROM tblOptionValue INNER JOIN tblOption on tblOptionValue.OptionID = tblOption.OptionID WHERE tblOptionValue.OptionId=\'+CAST(@ProductOptionGroupId AS VARCHAR)+\'AND tblOption.ProductId=\'+CAST(@ProductId AS VARCHAR)+
+\') AS Table\' + CAST(@ProductOptionGroupId AS VARCHAR)+\' CROSS JOIN \'
FETCH NEXT FROM CC INTO @ProductOptionGroupId
END
CLOSE CC
DEALLOCATE CC
SET @Query = \'SELECT * FROM \' + LEFT(@Query,LEN(@Query)-10)
PRINT @Query
EXEC sp_executesql @Query
DECLARE @ProductId INT, @Query NVARCHAR(MAX), @ProductOptionGroupId INT, @cName VARCHAR(300)
SET @ProductId = 70
SET @Query = \'\'
DECLARE CC CURSOR FOR
SELECT DISTINCT OptionID
FROM tblOption
WHERE ProductId = @ProductId
OPEN CC
FETCH NEXT FROM CC INTO @ProductOptionGroupId
WHILE @@FETCH_STATUS = 0
BEGIN
set @cName = (SELECT Name FROM tblOption WHERE OptionID=@ProductOptionGroupId)
SET @Query = @Query + \'(SELECT DISTINCT tblOptionValue.Name as \'+@cName+\' FROM tblOptionValue INNER JOIN tblOption on tblOptionValue.OptionID = tblOption.OptionID INNER JOIN tblProduct on tblOption.ProductID=tblProduct.ProductID WHERE tblOptionValue.OptionId=\'+CAST(@ProductOptionGroupId AS VARCHAR)+\'AND tblOption.ProductId=\'+CAST(@ProductId AS VARCHAR)+
+\') AS Table\' + CAST(@ProductOptionGroupId AS VARCHAR)+\' CROSS JOIN \'
FETCH NEXT FROM CC INTO @ProductOptionGroupId
END
CLOSE CC
DEALLOCATE CC
SET @Query = \'SELECT t2.*, t1.* FROM (SELECT * FROM \' + LEFT(@Query,LEN(@Query)-10)
SET @Query = @Query + \') as t1,
(SELECT * FROM tblProduct WHERE ProductID=70) as t2\'
PRINT @Query
EXEC sp_executesql @Query
没有找到相关结果
已邀请:
1 个回复
铰齐插
DECLARE @ProductId INT, @Query NVARCHAR(MAX), @ProductOptionGroupId INT, @cName VARCHAR(300) SET @ProductId = 70 SET @Query = \'\' DECLARE CC CURSOR FOR SELECT DISTINCT OptionID FROM tblOption WHERE ProductId = @ProductId OPEN CC FETCH NEXT FROM CC INTO @ProductOptionGroupId WHILE @@FETCH_STATUS = 0 BEGIN set @cName = (SELECT Name FROM tblOption WHERE OptionID=@ProductOptionGroupId) SET @Query = @Query + \'(SELECT DISTINCT tblOptionValue.Name as \'+@cName+\' FROM tblOptionValue INNER JOIN tblOption on tblOptionValue.OptionID = tblOption.OptionID INNER JOIN tblProduct on tblOption.ProductID=tblProduct.ProductID WHERE tblOptionValue.OptionId=\'+CAST(@ProductOptionGroupId AS VARCHAR)+\'AND tblOption.ProductId=\'+CAST(@ProductId AS VARCHAR)+ +\') AS Table\' + CAST(@ProductOptionGroupId AS VARCHAR)+\' CROSS JOIN \' FETCH NEXT FROM CC INTO @ProductOptionGroupId END CLOSE CC DEALLOCATE CC SET @Query = \'SELECT t2.*, t1.* FROM (SELECT * FROM \' + LEFT(@Query,LEN(@Query)-10) SET @Query = @Query + \') as t1, (SELECT * FROM tblProduct WHERE ProductID=70) as t2\' PRINT @Query EXEC sp_executesql @Query