我想在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. '
3条答案
按热度按时间hrysbysz1#
用这个怎么样
concat_ws()
在一系列case
表达?你可以添加更多的epwressions到
concat_ws()
根据需要。此函数应直接放置在select
子句(这样就不需要变量)。wgmfuz8q2#
你可以用
CONCAT_WS()
(或sql server早期版本中的类似构造:wljmcqd83#
重新排列代码