SQL Server如何使用WHILE查询从多个结果中输出一个表结果 [英] SQL Server How to output one table result from multiple results with a WHILE query
问题描述
从这个答案：有没有办法在不使用游标的情况下在TSQL中循环表变量？
我使用的方法
WHILE EXISTS（SELECT * FROM #Temp）
问题是，它输出多个表，如果可能我想输出为单个表。
声明@Id int
WHILE EXISTS（SELECT * FROM #Temp）
Begin
前1个@Id = ID从#Temp
- 此处进行一些处理
删除#Temp其中Id = @Id
结束
现在，它输出：
xy
- -
1 a
xy
- -
1 b
但我想输出： p>
xy
- -
1 a
2 b
我要实现的 ：
1234,1432,1235
我有一个过程，将字段分成记录（它与sql server 2000一起使用）：
DECLARE @String VARCHAR ）
SELECT @String = str FROM field --with the 1234,1432,1235
SELECT SUBSTRING（'，'+ @String +'，'，Number + 1，
CHARINDEX（'，'，'，'+ @String +'，'，Number + 1） - Number -1）AS str
INTO #temp
FROM master..spt_values
WHERE Type ='P'
AND Number< = LEN（'，'+ @String +'，'） - 1
AND SUBSTRING（'，'+ @String +'，'，Number，1 ）='，'
GO
现在，#temp有：
str
---
1234
1432
1235
所以我需要通过每个记录查询我需要的信息。
<我希望它输出这样的：str name age
--- - - ---
1234 Bob 23
1432 Jay 41
1235 Tim 12
当前While循环输出像这样
：str name age
--- ---- ---
1234 Bob 23
str name age
--- ---- - -
1432 Jay 41
str name age
--- ---- ---
1235 Tim 12
SET NOCOUNT ON;
DECLARE @String VARCHAR（1000）;
SELECT @String = Tnn FROM（SELECT
CO.USER_2 AS Tnn
FROM
[VMFG]。[dbo]。[CUSTOMER_ORDER] AS CO
LEFT JOIN DBO .Tnn_Header AS Tnn ON Tnn.TnnNumber = CO.USER_2 AND Tnn.StatusID ='5'WHERE CO.ID ='ORDERID'）AS Place - with the 1234,1432,1235
DECLARE @ Id nvarchar（50），
@Discount nvarchar（50），
@Spin nvarchar（50），
@Commission_Hmm nvarchar（50），
@
@TnnID nvarchar（50）;
DECLARE @Output TABLE（
TnnNumber nvarchar（50），
折扣nvarchar（50）
Spin nvarchar（50）
Commission_Hmm nvarchar 50），
Commission nvarchar（50），
TnnID nvarchar（50））;
DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR SELECT SUBSTRING（'，'+ @String +'，'，Number + 1，
CHARINDEX（'，' '+ @String +'，'，Number + 1） - Number -1）AS [ID]
FROM master..spt_values
WHERE Type ='P'
AND Number < LEN（'，'+ @String +'，'） - 1
AND SUBSTRING（'，'+ @String +'，'，Number，1）='，'
OPEN crs;
FETCH NEXT
FROM crs
INTO @Id;
WHILE（@@ FETCH_STATUS = 0）
BEGIN
- 做一些处理..
SELECT
@Id = TH.TnnNumber，
@Discount = CASE WHEN COUNT（DISTINCT TL.DiscountCodeID）> 1 THEN'Varies，View Tnn'ELSE CAST（MAX（DC.Value）AS VARCHAR（60））END，
@Spin = CASE WHEN TS.SpinID> 4 THEN'具有细节，查看Tnn'ELSE TS.Value END，
@Commission_Hmm = CASE WHEN COUNT（DISTINCT TL.Commission_Hmm）> 1 THEN'Varies，View Tnn'ELSE CAST（MAX（ISNULL（str（TL.Commission_Hmm，12），'Default Comm'））AS VARCHAR（60））END，
@Commission = CASE WHEN COUNT TL.Commission）> 1 THEN'Varies，View Tnn'ELSE CAST（MAX（ISNULL（str（TL.Commission，12），'Default Comm'））AS VARCHAR（60））END，
@TnnID = TL.TnnID
FROM DBO.Tnn_Header AS TH
LEFT JOIN DBO.Tnn_LINE AS TL ON TH.TnnID = TL.TnnID
LEFT JOIN DBO.Tnn_Spin AS TS ON TH.SpinID = TS.SpinID
LEFT JOIN DBO.Tnn_DiscountCode AS DC ON TL.DiscountCodeID = DC.DiscountCodeID
WHERE TnnNumber = @id
GROUP BY
TH.TnnNumber，
TS.SpinID，
TS.Value，
TL.TnnID
- end做一些处理..
INSERT INTO @Output（TnnNumber，Discount，Spin， Commission_Hmm，Commission，TnnID）
VALUES（@Id，@Discount，@Spin，@Commission_Hmm，@Commission，@TnnID）;
FETCH NEXT
FROM crs
INTO @Id;
END;
CLOSE crs;
DEALLOCATE crs;
SELECT TnnNumber，Discount，Spin，Commission_Hmm，Commission，TnnID
FROM @Output;
解决方案您正在浪费你的时间和精力， 。如果你完全必须（必须）采取逐行方法（CURSOR或WHILE循环），那么你最好用CURSOR。它是一个内置的结构，更高效，更少出错。您只需要使用正确的选项，例如使它
STATIC，LOCAL，READ_ONLY和FORWARD_ONLY。如果游标查询只触及临时表和/或表变量，则不需要STATIC。
将会与此争论，并说你必须以任何代价避免游标！，但他们没有做测试，看到这样一个流行的概念只是一个神话。如果他们已经做了确认它的测试，那么他们没有设置合适的选项，大多是
STATIC，它将游标查询的结果转储到临时表。没有此选项，提取新行将重新检查基表，以确保它们仍然存在， 是性能命中的地方（I / O加上锁定）。这也是为什么在仅查询临时表和/或表变量时， 不需要STATIC选项的原因。 再检查是什么意思？只需查看 @@ FETCH_STATUS 的文档即可。返回值不只是覆盖成功（0）和没有更多行（-1） ：有一个返回值，（-2），表示缺少行。SET NOCOUNT ON;
DECLARE @Id INT，
@Name sysname，
@Type VARCHAR（5）;
- 表变量替换原始查询中的＃Temp2
DECLARE @Output TABLE（ID INT NOT NULL，Name sysname，[Type] VARCHAR（5））;
- CURSOR在原始查询中替换#Temp
DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR SELECT [object_id]，name，[type]
FROM sys.objects - dbo.sysobjects for SQL 2000 - 在原始查询中的ATable
ORDER BY [object_id] ASC;
OPEN crs;
FETCH NEXT
FROM crs
INTO @Id，@Name，@Type;
WHILE（@@ FETCH_STATUS = 0）
BEGIN
INSERT INTO @Output（Id，Name，[Type]）
VALUES（@Id，@Name， @类型）;
- 做一些处理..
FETCH NEXT - 替换原始查询中的DELETE和重新选择
从crs
INTO @ Id，@Name，@Type;
END;
CLOSE crs;
DEALLOCATE crs;
SELECT Id，Name，[Type]
FROM @Output;
UPDATE
假设迭代是通过分割INT的查询语句完成的，生成的查询将类似于以下内容：
SET NOCOUNT ON;
DECLARE @String VARCHAR（1000）;
SELECT @String = str FROM [Table]; - 与1234,1432,1235
DECLARE @Id INT，
@Name NVARCHAR（50），
@Age TINYINT;
DECLARE @Output TABLE（Id INT NOT NULL，Name NVARCHAR（50），Age TINYINT）;
DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR SELECT SUBSTRING（'，'+ @String +'，'，Number + 1，
CHARINDEX（'，' '+ @String +'，'，Number + 1） - Number -1）AS [ID]
FROM master..spt_values
WHERE Type ='P'
AND Number& LEN（'，'+ @String +'，'） - 1
AND SUBSTRING（'，'+ @String +'，'，Number，1）='，'
OPEN crs;
FETCH NEXT
FROM crs
INTO @Id;
WHILE（@@ FETCH_STATUS = 0）
BEGIN
- 做一些处理..
- 设置@Name值的逻辑
- - 逻辑设置@Age的值
INSERT INTO @Output（Id，Name，Age）
VALUES（@Id，@Name，@Age）;
FETCH NEXT
FROM crs
INTO @Id;
END;
CLOSE crs;
DEALLOCATE crs;
SELECT ID，Name，Age
FROM @Output;
From this answer: Is there a way to loop through a table variable in TSQL without using a cursor?
I'm using the method
WHILE EXISTS(SELECT * FROM #Temp)The problem is that it's outputting multiple tables, if possible I'd like to output as a single table.
Declare @Id int WHILE EXISTS(SELECT * FROM #Temp) Begin Select Top 1 @Id = Id From #Temp --Do some processing here Delete #Temp Where Id = @Id EndSo right now it outputs this:
x y -- -- 1 a x y -- -- 1 bBut I'd like it to output this:
x y -- -- 1 a 2 bWhat I'm trying to achieve, I have this in a field:
1234,1432,1235
I have a process that splits the field into records(it works with sql server 2000):
DECLARE @String VARCHAR(100) SELECT @String = str FROM field --with the 1234,1432,1235 SELECT SUBSTRING(',' + @String + ',', Number + 1, CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS str INTO #temp FROM master..spt_values WHERE Type = 'P' AND Number <= LEN(',' + @String + ',') - 1 AND SUBSTRING(',' + @String + ',', Number, 1) = ',' GOSo now, #temp has:
str --- 1234 1432 1235So I need to go through each record to query the information I need.
And I'd like it to output something like this:
str name age --- ---- --- 1234 Bob 23 1432 Jay 41 1235 Tim 12The current While loop outputs it like this, which I don't want:
str name age --- ---- --- 1234 Bob 23 str name age --- ---- --- 1432 Jay 41 str name age --- ---- --- 1235 Tim 12Final Working Result:
SET NOCOUNT ON; DECLARE @String VARCHAR(1000); SELECT @String = Tnn FROM (SELECT CO.USER_2 AS Tnn FROM [VMFG].[dbo].[CUSTOMER_ORDER] AS CO LEFT JOIN DBO.Tnn_Header AS Tnn ON Tnn.TnnNumber = CO.USER_2 AND Tnn.StatusID = '5' WHERE CO.ID = 'ORDERID') AS Place --with the 1234,1432,1235 DECLARE @Id nvarchar(50), @Discount nvarchar(50), @Spin nvarchar(50), @Commission_Hmm nvarchar(50), @Commission nvarchar(50), @TnnID nvarchar(50); DECLARE @Output TABLE ( TnnNumber nvarchar(50), Discount nvarchar(50), Spin nvarchar(50), Commission_Hmm nvarchar(50), Commission nvarchar(50), TnnID nvarchar(50)); DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY FOR SELECT SUBSTRING(',' + @String + ',', Number + 1, CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1) AS [ID] FROM master..spt_values WHERE Type = 'P' AND Number <= LEN(',' + @String + ',') - 1 AND SUBSTRING(',' + @String + ',', Number, 1) = ','; OPEN crs; FETCH NEXT FROM crs INTO @Id; WHILE (@@FETCH_STATUS = 0) BEGIN -- do some processing.. SELECT @Id = TH.TnnNumber, @Discount = CASE WHEN COUNT(DISTINCT TL.DiscountCodeID) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(DC.Value) AS VARCHAR(60)) END, @Spin = CASE WHEN TS.SpinID > 4 THEN 'Has Specifics, View Tnn' ELSE TS.Value END, @Commission_Hmm = CASE WHEN COUNT(DISTINCT TL.Commission_Hmm) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX( ISNULL(str(TL.Commission_Hmm,12),'Default Comm')) AS VARCHAR(60)) END, @Commission = CASE WHEN COUNT(DISTINCT TL.Commission) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission,12),'Default Comm')) AS VARCHAR(60)) END, @TnnID = TL.TnnID FROM DBO.Tnn_Header AS TH LEFT JOIN DBO.Tnn_LINE AS TL ON TH.TnnID = TL.TnnID LEFT JOIN DBO.Tnn_Spin AS TS ON TH.SpinID = TS.SpinID LEFT JOIN DBO.Tnn_DiscountCode AS DC ON TL.DiscountCodeID = DC.DiscountCodeID WHERE TnnNumber = @id GROUP BY TH.TnnNumber, TS.SpinID, TS.Value, TL.TnnID -- end do some processing.. INSERT INTO @Output (TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID) VALUES (@Id, @Discount, @Spin, @Commission_Hmm, @Commission, @TnnID); FETCH NEXT FROM crs INTO @Id; END; CLOSE crs; DEALLOCATE crs; SELECT TnnNumber, Discount, Spin, Commission_Hmm, Commission, TnnID FROM @Output;
解决方案You are wasting your time and energy following such bad advice. If you absolutely must (extra emphasis on the must) take a row-by-row approach (CURSOR or WHILE loop), then you are better off with a CURSOR. It is a built-in construct that is more efficient, and less error-prone. You just need to use the right options, such as making it
STATIC,LOCAL,READ_ONLY, andFORWARD_ONLY. You don't needSTATICif the cursor query is only hitting temporary tables and/or table variables.People will argue with this and say that "you must avoid cursors at all cost!", but they haven't done the tests to see that such a popular notion is really just a myth. And if they have done tests that appear to confirm it, then they haven't set the appropriate options, mostly
STATIC, which dumps the result of the cursor query into a temp table. Without this option, fetching new rows will re-check the base tables to make sure that they still exist, and that is where the performance hit is (the I/O plus the locking). And that is also why you typically don't need theSTATICoption when querying only temporary tables and/or table variables. What do I mean by "re-checking"? Just look at the documentation for @@FETCH_STATUS. The return values don't just cover "success" (0) and "no more rows" (-1): there is a return value, (-2), that means "The row fetched is missing".SET NOCOUNT ON; DECLARE @Id INT, @Name sysname, @Type VARCHAR(5); -- the Table Variable replaces #Temp2 in the original query DECLARE @Output TABLE (Id INT NOT NULL, Name sysname, [Type] VARCHAR(5)); -- the CURSOR replaces #Temp in the original query DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY FOR SELECT [object_id], name, [type] FROM sys.objects -- dbo.sysobjects for SQL 2000 -- ATable in the original query ORDER BY [object_id] ASC; OPEN crs; FETCH NEXT FROM crs INTO @Id, @Name, @Type; WHILE (@@FETCH_STATUS = 0) BEGIN INSERT INTO @Output (Id, Name, [Type]) VALUES (@Id, @Name, @Type); -- do some processing.. FETCH NEXT -- replaces the DELETE and re-SELECT in the original query FROM crs INTO @Id, @Name, @Type; END; CLOSE crs; DEALLOCATE crs; SELECT Id, Name, [Type] FROM @Output;UPDATE
Given the iteration is being done over a query that splits a CSV of INTs, the resulting query would look similar to the following:
SET NOCOUNT ON; DECLARE @String VARCHAR(1000); SELECT @String = str FROM [Table]; --with the 1234,1432,1235 DECLARE @Id INT, @Name NVARCHAR(50), @Age TINYINT; DECLARE @Output TABLE (Id INT NOT NULL, Name NVARCHAR(50), Age TINYINT); DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY FOR SELECT SUBSTRING(',' + @String + ',', Number + 1, CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1) AS [ID] FROM master..spt_values WHERE Type = 'P' AND Number <= LEN(',' + @String + ',') - 1 AND SUBSTRING(',' + @String + ',', Number, 1) = ','; OPEN crs; FETCH NEXT FROM crs INTO @Id; WHILE (@@FETCH_STATUS = 0) BEGIN -- do some processing.. -- Logic to set value of @Name -- Logic to set value of @Age INSERT INTO @Output (Id, Name, Age) VALUES (@Id, @Name, @Age); FETCH NEXT FROM crs INTO @Id; END; CLOSE crs; DEALLOCATE crs; SELECT Id, Name, Age FROM @Output;
这篇关于SQL Server如何使用WHILE查询从多个结果中输出一个表结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
