SQL nvarchar对求和运算符无效

Question

我正在学习SQL，我的表看起来像这样:

Id  Name   Payd  Note
1   John   5.00  R:8days;U:5$
2   Adam   5.00  R:8days;
3   John   10.00 R:8days;
4   John   10.00 R:8days;
5   Adam   15.00 R:30days;

我想做这样的东西:

  Id  Name Usage     5.00 10.00 15.00 Sum
    1   John  5      5.00 20.00 0     25.00
    2   Adam         5.00 0     15.00 20.00

我想检查笔记列中是否有'U:5$'，然后向笔记列中有'U:5$'的客户添加5，如果没有，则不添加任何内容。

我的代码是这样的:

;with cte as (
select Customer, PaydAmount, PaydAmount as Payd, Note as Usage
from t1
)
select 
    Customer, Usage
    ,[4.00] = ISNULL([4.00],0)
    ,[5.00] = ISNULL([5.00],0)
    ,[9.00] = ISNULL([9.00],0)
    ,[10.00] = ISNULL([10.00],0)
    ,[15.00] = ISNULL([15.00],0)
    ,[18.00] = ISNULL([18.00],0)
    ,[20.00] = ISNULL([20.00],0)
    ,[25.00] = ISNULL([25.00],0)
    ,[50.00] = ISNULL([50.00],0)
    ,[Payd] =ISNULL([4.00],0) + ISNULL([5.00],0) + ISNULL([9.00],0) + ISNULL([10.00],0) + ISNULL([15.00],0) + ISNULL([18.00],0) + ISNULL([20.00],0) + ISNULL([25.00],0) + ISNULL([50.00],0)
    from cte
    pivot (
        sum(PaydAmount) for Payd in ([4.00],[5.00],[9.00], [10.00], [15.00],[18.00], [20.00], [25.00], [50.00]))pvt
        order by Customer;

Answer 1

我不确定您的完整输出代表什么。但是使用条件聚合很容易获得前三列:

select row_number() over (order by (select null)) as id,
       name,
       max(case when note like '%U:5$' then 5 end) as usage,
       sum(case when payd = 5.00 then payd else 0 end) as [5.00],
       sum(case when payd = 10.00 then payd else 0 end) as [10.00],
       sum(case when payd = 15.00 then payd else 0 end) as [15.00],
       sum(payd) as total
from cte
group by name;

注意，5、10和15列都假定payd中的值是十进制/数字类型。不建议对浮点数进行相等比较。