t-sql-select语句期间设置变量

jm2pwxwz 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(251)

我想在sql查询的中间设置一个变量。
我有多个'flags'，我希望查询在result列中报告有关客户的所有true标志。因此，我不能使用case语句，因为它一旦找到第一个“true”语句就停止计算。为了解决这个问题，我使用了一个set if if语句将相关的标志附加到一个变量，然后在result列中报告该变量。但是，我没有任何进展。
当然，下面的代码不会运行。如何重新组织此查询以使其符合犹太教义？
预期结果如下：

代码如下：

DECLARE 
@CountOfOrders int = 0,
@resultstring varchar(max) = ''

SELECT 
Customers.CustomerName,
CountOfOrders.CountOfOrders,
@resultstring as Result

From 
--Get some basic data from the query's base table:
(Select 'James Bond' as CustomerName) Customers

--Get some extra data about James Bond's orders from a different table,
--Let's just say he's got 35 orders:
OUTER APPLY (
                SELECT CAST(35 AS INT) as CountOfOrders
            ) CountOfOrders

--Now do some number crunching:
Set @CountOfOrders = CountOfOrders.CountOfOrders
IF @CountOfOrders > 10 set @resultstring = @resultstring + 'James Bond has greater than 10 orders. '
IF @CountOfOrders > 20 set @resultstring = @resultstring + 'James Bond has greater than 20 orders. '
IF @CountOfOrders > 30 set @resultstring = @resultstring + 'James Bond has greater than 30 orders. '
IF @CountOfOrders > 40 set @resultstring = @resultstring + 'James Bond has greater than 40 orders. '
IF @CountOfOrders > 50 set @resultstring = @resultstring + 'James Bond has greater than 50 orders. '

sql sql-server count String tsql

来源：https://stackoverflow.com/questions/63309680/t-sql-set-variable-during-select-statement

3条答案

按热度按时间

hrysbysz1#

用这个怎么样 concat_ws() 在一系列 case 表达？

concat_ws(
    ' ',
    case when CountOfOrders > 10 
        then concat(CustomerName, ' has more than 10 orders.')
    end,
    case when CountOfOrders > 20 
        then concat(CustomerName, ' has more than 20 orders.')
    end,
    case when CountOfOrders > 30 
        then concat(CustomerName, ' has more than 30 orders.')
    end
) as result

你可以添加更多的epwressions到 concat_ws() 根据需要。此函数应直接放置在 select 子句（这样就不需要变量）。

赞(0）回复(0）举报 2021-07-24

wgmfuz8q2#

你可以用 CONCAT_WS() （或sql server早期版本中的类似构造：

select @resultstring = CONCAT_WS(' ',
                                 (CASE WHEN CountOfOrders > 10 THEN 'James Bond has greater than 10 orders.' END),
                                 (CASE WHEN CountOfOrders > 20 THEN 'James Bond has greater than 20 orders.' END),
                                 . . .
                                )
from . . .;

赞(0）回复(0）举报 2021-07-24

wljmcqd83#

重新排列代码

DECLARE 
@CountOfOrders int = 0,
@resultstring varchar(max) = ''

--Get some extra data about James Bond's orders from a different table,
--Let's just say he's got 35 orders:

--Now do some number crunching:
Set @CountOfOrders = (
                SELECT CAST(35 AS INT) as CountOfOrders
            ) 
IF @CountOfOrders > 10 set @resultstring = @resultstring + 'James Bond has greater than 10 orders. '
IF @CountOfOrders > 20 set @resultstring = @resultstring + 'James Bond has greater than 20 orders. '
IF @CountOfOrders > 30 set @resultstring = @resultstring + 'James Bond has greater than 30 orders. '
IF @CountOfOrders > 40 set @resultstring = @resultstring + 'James Bond has greater than 40 orders. '
IF @CountOfOrders > 50 set @resultstring = @resultstring + 'James Bond has greater than 50 orders. ' 

SELECT 
Customers.CustomerName,
@CountOfOrders,
@resultstring as Result

From 
--Get some basic data from the query's base table:
(Select 'James Bond' as CustomerName) Customers

赞(0）回复(0）举报 2021-07-24

我来回答

t-sql-select语句期间设置变量

3条答案

相关问题

热门标签

最新问答