SQL 在 then of case when 语句中选择子查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15795224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select subquery inside then of case when statement?
提问by Rainhider
Is there a way to run a select statement from a "then" in the sql server case/when statement? (I need to run subqueries from a then statement.) I cannot have it in the where statement.
有没有办法从sql server case/when语句中的“then”运行select语句？（我需要从 then 语句运行子查询。）我不能在 where 语句中使用它。
select
case @Group
when 6500 then (select top 10 * from Table1)
when 5450 then (select top 5 * from Table1)
when 2010 then (select top 3 * from Table1)
when 2000 then (select top 1 * from Table1)
else 0
end as 'Report'
采纳答案by Gordon Linoff
One option is to remove this from the query and do something like:
一种选择是从查询中删除它并执行以下操作：
declare @Numrows int;
select @Numrows = (case @Group
when 6500 then 10
when 5450 then 5
when 2010 then 3
when 2000 then 1
else 0
end);
select top(@NumRows) *
from Table1;
You could also do it this way:
你也可以这样做：
with const as (
select (case @Group
when 6500 then 10
when 5450 then 5
when 2010 then 3
when 2000 then 1
else 0
end) as Numrows
)
select t.*
from (select t.*, ROW_NUMBER() over () as seqnum
from table1 t
) t cross join
const
where seqnum <= NumRows;
In this case, you need to list out the columns to avoid getting seqnumin the list.
在这种情况下，您需要列出列以避免进入seqnum列表。
By the way, normally when using topyou should also have order by. Otherwise, the results are indeterminate.
顺便说一句，通常在使用时top你也应该有order by. 否则，结果是不确定的。
回答by Young Bob
You can't have a SELECT within a SELECT. You can use IF...ELSE though e.g.
SELECT 中不能有 SELECT。你可以使用 IF...ELSE 虽然例如
IF @Group = 6500
select top 10* from Table1 AS Report
ELSE IF @Group = 5450
select top 5* from Table1 AS Report
ELSE IF @Group = 2010
select top 3* from Table1 AS Report
ELSE IF @Group = 2000
select top 1* from Table1 AS Report
回答by ljh
@Gordon has the answer already. This is just second opnion. You can use dynamic query.
@Gordon 已经有了答案。这只是第二个选项。您可以使用动态查询。
declare @query varchar(max)
declare @Group int
set @query = ''
if @Group = 6500
set @query = 'select top 10 * from table1'
if @Group = 5450
set @query = 'select top 5 * from table1'
if @Group = 2010
set @query = 'select top 3 * from table1'
if @Group = 2000
set @query = 'select top 1 * from table1'
exec(@query)
