sql - 合并 2 行或更多行有字符和其他有货币

我通过查询得到这样的输出:

ID      customer_name_Now    customer_name_Before       MOVEMENT
123451  Rustle Bock ltd      N                          £2,121
123451  N                    Rustle Bock ltd           -£25,666,899
123452  Little Garage Ltd    N                          £6,987
123453  N                    The Big Shop               £15,850

故事是这样的，我有 2 个月的数据。在这两个月中，客户可能有也可能没有移动，这取决于上个月是我们的客户还是现在的客户。在许多情况下，这两个月都是客户，因此我得到了 2 行，如上所示。

理想的输出应该是:

ID      customer_name_Now   customer_name_Before      MOVEMENT
123451  Rustle Bock ltd     Rustle Bock ltd          -£25,664,778
123452  Little Garage Ltd   N                         £6,987
123453  N                   The Big Shop              £15,850

因此，移动应该求和以给出月份的实际移动，并且鉴于客户在两个月份都有关系，因此客户的姓名应该在两列中。

@DMK 我用来获取初始输出的查询是:

select /*+ NO_REWRITE */
customer_id,
customer_name_now,
customer_name_before,
movement

from
    (select /*+ NO_REWRITE */
    main.customer_id,
    main.customer_name_now,
    main.customer_name_before,
    main.limits_before,
    main.limits_now,
    sum(main.limits_now-main.limits_before) as movement

    from
        (select /*+ NO_REWRITE */
        customer_id,
        (customer_name_before) as customer_name_before,
        (customer_name_now) as customer_name_now,
        sum(limits_current) as limits_now,
        sum(limits_previous) as limits_before

        from
             (select /*+ NO_REWRITE */
             sub.customer_id,
             sub.customer_name_now,
             sub.customer_name_before,
             sub.limits_current,
             sub.limits_previous
             from
                 (select /*+ NO_REWRITE */
                 T2.customer_ID,
                 (T2.customer_name) customer_name_now,
                 'N' customer_name_before,

                 sum(T26.AGREED_LIMIT) limits_current,
                 0 limits_previous
                 from

                 DWH_customer_HISTORY T2,
                 DWH_TIME_DIM T25,
                 DWH_FACILITY_MONTHLY T2
                 where
                 ---some internal filters are applied here, i habe ot shown coz of security reasons----
                 and
                 T25.MONTH_END = '2012-11-30' and

                 group by
                 T2.customer_ID,
                 T2.customer_name,
                 ) sub

             union all

             select /*+ NO_REWRITE */
             sub.customer_id,
             sub.customer_name_now,
             sub.customer_name_before,
             sub.limits_current,
             sub.limits_previous
             from
                 (select /*+ NO_REWRITE */
                 T2.customer_ID,
                 'N' as customer_name_now,
                 (T2.customer_name)customer_name_before,

                 0 limits_current,
                 sum(T2.AGREED_LIMIT) limits_previous,

                from
                DWH_customer_HISTORY T2,
                DWH_TIME_DIM T25,
                DWH_FACILITY_MONTHLY T2
                where
                ---some internal filters are applied here, i habe ot shown coz of security reasons----
                and
                T25.MONTH_END = '2012-10-31'
                group by
                T2.customer_ID,
                T2.customer_name,) sub
            ) un
        group by
        customer_id,
        customer_name_now,
        customer_name_before,) main

    group by
    main.customer_id,
    main.customer_name_now,
    main.customer_name_before,
    main.limits_before,
    main.limits_now)

最佳答案

我假设您使用 SqlServer，但下面的查询也适用于 MySql。

Select c1.ID, c1.customer_name_Now, c2.customer_name_Before, Total
from Customers c1
left Join Customers c2
on c2.ID = c1.ID
left join
    (select ID as ID2, sum(MOVEMENT) as Total, count(*) as Cnt
    from Customers
    group by ID) t1
on ID2 = c1.ID
where (c1.customer_name_Now <> 'N' and c2.customer_name_Before <> 'N')
or CNT = 1

如果您不确定，请查看以下演示
SqlFiddle

查看您刚刚添加的查询后，上面的内容应该仍然有效。你要么需要

用您的查询

替换我的表 Customers

或者将您的查询结果移动到临时表并用临时表

替换我的表 Customers
我会去第二个。保存重新运行相同的查询。

关于sql - 合并 2 行或更多行有字符和其他有货币，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13970275/