使用一个选择查询返回多个表 [英] Return multiple table using one select query

问题描述

I have a table with data as follows.

Name     Size
----------------
A          1
B          2
C          1
D          1
E          3
F          2

I have to write an SQL (sql server) which will return 3(as there  are 3 types of sizes are in the table) tables. The number of produced table can be increased by inserting new size data in the main table.

The result of the required SQL should be as follows:-
Name     Size
----------------
A          1
C          1
D          1

Name     Size
----------------
B          2
F          2

Name     Size
----------------
E          3

Is there any easy way to produce the result without using any loop in the SQL?

推荐答案

如果需要三个结果集，则答案为否.您将需要一个循环来执行此操作(除非您对语句进行了硬编码，否则情况会更糟).但是，我看不到这样做的商业案例.我建议返回一个结果集，然后在代码中将其分解(如果需要).

如果确实需要将三个(或更多)结果集返回到代码中，则还可以创建一个存储过程，该过程使用所需的Size并返回该结果集.这样，您可以更灵活地调用它，但它仍然可以处理多种情况.

这些只是重构设计的一些想法.最后，您的问题的基本答案是否"，没有循环就无法做到这一点.

If you want three resultsets, then the answer is no. You would need a loop to do that (unless you hard-coded the statements, which would be worse). However, I don''t see the business case for doing something like this. I would recommend returning one resultset and then break it apart in code (if you need to).

If you really needed three (or more) resultsets to be returned to your code, you could also create a stored procedure that takes in the Size you want and returns that resultset. That way you could be more flexible in how you called it and yet it would still handle the multiple cases.

These are just some ideas to refactor your design. In the end, the basic answer to your question is no, you cannot do that without a loop.

这篇关于使用一个选择查询返回多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！