一个查询在sql server中获得多个结果 [英] one query get multiple results in sql server
问题描述
嗨我有一个查询,如果我执行我需要多个基于条件的结果字段。
Ex:
假设一个表有ID和状态
查询:从表中选择id,status
结果: 1 结果 set
28 待定
28 待定
28 待定
28 待定
26 待定
26 待定
26 等待
26 待定
但我想要2个结果集以下..
1 结果设置
28 待定
28 待定
28 待定
28 待定
第二个结果 set
26 待定
26 待定
26 待定
26 待定
基于我需要创建表格的ID。
请帮忙。谢谢提前。
谢谢,
Aravind G
DECLARE @ tmp TABLE (ID INT ,[状态] VARCHAR ( 50 ))
INSERT INTO @ tmp (ID,[Status])
VALUES ( 1 ,' 待定'),( 1 ,' 待定'),
( 1 ,' 待定'),( 9 ,' 待定'),
( 9 ,' 待定'),( 18 ,' 待定'),
( 18 ,' 待定'),( 18 ,' 待定'),
( 18 ,' 待定'),( 25 ,' 待定'),
( 25 ,' 待定'),( 25 ,' 待定')
DECLARE @ curid INT = 1
DECLARE @ maxid INT = 0
SELECT @ maxid = MAX(ID) FROM @ tmp
WHILE (@ curid< = @ maxid)
BEGIN
IF EXISTS ( SELECT * FROM @ tmp WHERE ID = @curid )
BEGIN
SELECT * FROM @ tmp WHERE ID = @ curid
END
SET < span class =code-sdkkeyword> @ curid + = 1
END
按预期返回结果集。但我需要警告你:上面的代码会在很短的时间内执行大量的查询。它可能会导致几个问题。我建议在一个数据库中获取整个数据,然后 - 在客户端 - 将数据拆分为较小的部分。
您需要一个基于id值的条件,请查看 SQL WHERE子句 [ ^ ]
当然,你必须用不同的id执行两次sql。
Hi I have one query if i execute i need multiple result fields based on condition.
Ex:
Suppose one table has ID and status
Query : select id,status from table
Result: 1 Result set
28 Pending
28 Pending
28 Pending
28 Pending
26 Pending
26 Pending
26 Pending
26 Pending
But i want 2 result sets like below..
1 result set
28 Pending
28 Pending
28 Pending
28 Pending
2nd result set
26 Pending
26 Pending
26 Pending
26 Pending
based on ID i need to create tables.
Please help this.Thanks for advance.
Thanks,
Aravind G
Try this:
DECLARE @tmp TABLE(ID INT, [Status] VARCHAR(50)) INSERT INTO @tmp(ID, [Status]) VALUES(1, 'Pending'),(1, 'Pending'), (1, 'Pending'),(9, 'Pending'), (9, 'Pending'),(18, 'Pending'), (18, 'Pending'),(18, 'Pending'), (18, 'Pending'),(25, 'Pending'), (25, 'Pending'),(25, 'Pending') DECLARE @curid INT = 1 DECLARE @maxid INT = 0 SELECT @maxid = MAX(ID) FROM @tmp WHILE (@curid<=@maxid ) BEGIN IF EXISTS(SELECT * FROM @tmp WHERE ID = @curid) BEGIN SELECT * FROM @tmp WHERE ID = @curid END SET @curid +=1 END
Returns result sets as expected. But i need to warn you: above code executes lots of queries in short period of time. It might cause several issues. I'd suggest to get entire data at ones and then - on client side - split data to smaller portions.
You need a condition based on the value of id, check out the SQL WHERE Clause[^]
Of course, then you have to execute the sql twice each with different id.
这篇关于一个查询在sql server中获得多个结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!