使用变量查询 [英] Query with variables

问题描述

是否可以从查询中设置/读取变量?

Is it possible to set/read variables from within the query?

伪代码:

SELECT animal_name,
    @tallest_animal = (select top 1 height from animal order by height desc) as tallest,
    @smallest_animal = (select top 1 height from  animal order by height asc) as smallest
FROM animals
WHERE height BETWEEN @smallest_animal AND @tallest_animal

我知道可以通过使查询不同来获得结果，我的问题的实际用途太难解释了.

I know the result can be achieved by making the query different, my question's real use is too difficult to explain.

问题在于 Microsoft SQL Server.:)

It's Microsoft SQL Server in question. :)

推荐答案

是的，您可以在查询中设置变量.您的语法实际上非常接近.

Yes you can set variables within a query. Your syntax is actually quite close.

为此，您需要:

SELECT @YourVariable = Column
FROM Animals

注意:将字段分配给变量时不能使用 AS.

Note: You cannot use the AS when assigning a field to a variable.

必须确保查询中的所有字段都分配给了一个变量，否则会出现以下错误:

You must ensure that all of the fields in the query are assigned to a variable, otherwise you will get the following error:

为变量赋值的 SELECT 语句不得与数据检索操作结合使用.

A SELECT statement that assigns a value to a variable must not be combined with data- retrieval operations.

要克服这个问题，只需将 AnimalName 分配给 @AnimalName 变量即可.

To overcome this, simply assign AnimalName to an @AnimalName variable.

DECLARE @AnimalName  VARCHAR(20)
DECLARE @TallestAnimal  INT
DECLARE @SmallestAnimal INT

SELECT @AnimalName = animal_name,
   @TallestAnimal  = (select top 1 height from animal order by height desc),
   @SmallestAnimal = (select top 1 height from  animal order by height asc) 
FROM animals
WHERE height BETWEEN @SmallestAnimal AND @TallestAnimal 

此代码假设高度字段的类型为 INT.

This code is assuming the height field is of type INT.

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