Sql server Query：带有TOP关键字的条件选择列 [英] Sql server Query : Conditional Select columns with TOP keyword

问题描述

  DECLARE   @ mode   INT ; 
 
  SELECT   CASE  
  WHEN   @ mode  =  0  
 那么 t.Column1，Count（t.Column2） as  Column2 
  ELSE   top   1  t.Column1，Count（t.Column2） as  Column2 
  END  
  FROM 表1 t 
   -   其中一些参数列表 
 组 按 t .Column1，t.Column2 

请阅读上面的sql语句小心。我需要在不改变查询主体的情况下通过两种模式评估查询，即。 From，Where和Group子句只应写一次，而不是在结果查询中的任何地方复制它们（每一个）



如果@mode = 0那么上面说的列应该返回，如果@mode<> 0然后前1 *的记录



两个选择条件都使用相同的给定参数列表。

当我运行上面的查询时我正面临错误关键字'top'附近的语法不正确。因为我们不能在条件选择语句中使用前1个关键字，并且选择条件的列必须与它们的数据类型匹配。



请修复上述查询而不影响逻辑查询。

解决方案

您好，



为此创建存储过程。使用过程中的If条件如下

 创建  procedure  procedurename（parameters）
  DECLARE   @ mode   INT ; 
  SET   @ mode  =  0 ; 
 
  IF   @ mode  =  0  然后 
  SELECT  t.Column1，Count（t.Column2）< span class =code-keyword> as  Column2 
  FROM 表1 t 
  -   其中一些参数列表 
 组  by  t.Column1，t.Column2 
  else  选择  top   1  * 
  FROM 表1 t 
   -   其中一些参数列表 
  Group   by  t.Column1，t.Column2 
 结束  IF  

您无法保留此格式。

主要问题不是top子句，你可以用一些东西替换它，比如

  else （选择  top   1  * 来自表1）
 结束 

但是！你不能在这种情况下选择多个字段 - 何时结构（在第一部分计算第二个字段！），所以你不能在内部选择中使用*！

< blockquote>如何使用 IF 语句并返回两个不同的结果？

  IF   @ mode < =  0  
  BEGIN  
  SELECT  t.Column1，COUNT（t.Column2） AS  Column2 
  FROM 表1 t 
  GROUP   BY  t.Column1 
  END  
  ELSE  
  BEGIN  
  SELECT   TOP   1  *  FROM 表1 
  END  

顺便说一下，在生产代码中使用 SELECT * 并非最佳做法。

我已经写了一个替代Tip / Trick的原因 SELECT * 是邪恶的： The Evil That is Select * [ ^ ]。

以下是原文：邪恶即选择* [ ^ ]。

DECLARE @mode INT; 

SELECT CASE 
		WHEN @mode = 0
			THEN t.Column1,Count(t.Column2) as Column2
		ELSE top 1 t.Column1,Count(t.Column2) as Column2 
		END
FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2

Please read the above sql statement carefully. I have requirement to evaluate the query by two modes without changing the body of the query ie. From,Where and Group clauses should be written only once and not to replicate them (each one) anywhere in the result query

if @mode = 0 then the above said columns should be return and If @mode <> 0 then top 1 * of records

Both select conditions are uses the same given set list of parameters.
When I run the above query am facing the error "Incorrect syntax near the keyword 'top'." because we could not use the top 1 keyword within conditional select statements and select condition's columns must be matched even with their datatypes.

Please fix the above query without affecting logic of the query.

解决方案

Hello,

Create Stored Procedure for the same. Use the If Condition in the procedure as follows

Create procedure procedurename(parameters)
DECLARE @mode INT;
SET @mode = 0;

IF @mode = 0 then
     SELECT t.Column1,Count(t.Column2) as Column2
FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2
 else Select top 1 * 
  FROM Table1 t
--Where some list of parameters
Group by t.Column1,t.Column2
End IF

You can not keep this format.
The main problem isn't the top clause, you can replace it with some thing like

   else (select top 1 * from Table1)
end

But! you can not select more than one field in such a case-when structure (in the first part the second field is computed!), so you can't use * in the inner select anyway!

How about just using an IF statement and returning two different results?

IF @mode <= 0
BEGIN
    SELECT t.Column1, COUNT(t.Column2) AS Column2
    FROM Table1 t
    GROUP BY t.Column1
END
ELSE
BEGIN
    SELECT TOP 1 * FROM Table1
END

By the way, it's not best practice to use SELECT * in production code.
I've written an alternative to a Tip/Trick on why SELECT * is evil: The Evil That is "Select *"[^].
And here is the original: The Evil That is "Select *"[^].

这篇关于Sql server Query：带有TOP关键字的条件选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！