深入行列转换----多行转多列,多行的计算

时间:2022-05-28 10:32:39

碰到朋友一个问题,基于Oracle环境,有点复杂,直接看代码。

我的测试环境是sql server 2014

create table test101(
[门店] int
,[缴费大类] int
,[支付方式] int
,[付款] int,
[手续费] int
)
insert into test101
values
(1,0,1,10,2),
(1,0,2,10,2),
(1,0,3,10,2),
(1,0,4,10,2),
(1,1,1,10,2),
(1,1,2,10,2),
(1,1,3,10,2),
(1,1,4,10,2),
(1,2,1,10,2),
(1,2,2,10,2),
(1,2,3,10,2),
(1,2,4,10,2),
(1,3,1,10,2),
(1,3,2,10,2),
(1,3,3,10,2),
(1,3,4,10,2)

数据如下:
深入行列转换----多行转多列,多行的计算

 

 

 然后是要求出挣得钱和缴多少税。

消费大类:3为退款,0~2为入账大类。

 现在要计算每一种支付方式,挣了多少钱,缴了多少税。

逻辑算法:

  挣钱:当支付方式为1时:累加支付方式0~2的付款数,减去消费大类为3(退款)的付款数,即为支付方式1所挣的钱。

  手续费:当支付方式为1时:累加支付方式0~2的手续费,减去消费大类为3(退款)的手续费,即为支付方式1所需要承担的手续费。

深入行列转换----多行转多列,多行的计算

挣钱:这里计算一下是10+10+10-10;

手续费:这里计算一下是2+2+2-2;

以此类推,算出每个门店下所有支付方式对应挣得钱和所需手续费。

 

方法:1(我朋友的方法,对此我是无比佩服),在实际业务中,肯定会有多条

[门店],[支付方式],[缴费大类]相同的,而付款和手续费不同的数据,所以实际业务中应当用sum代替max,这里只是测试就用Max了。

select [门店],[支付方式],
max(case when [缴费大类]=0 then isnull([付款],0) else 0 end) +
max(case when [缴费大类]=1 then isnull([付款],0) else 0 end) +
max(case when [缴费大类]=2 then isnull([付款],0) else 0 end) -
max(case when [缴费大类]=3 then isnull([付款],0) else 0 end) as [付款],
max(case when [缴费大类]=0 then isnull([手续费],0) else 0 end) +
max(case when [缴费大类]=1 then isnull([手续费],0) else 0 end) +
max(case when [缴费大类]=2 then isnull([手续费],0) else 0 end) -
max(case when [缴费大类]=3 then isnull([手续费],0) else 0 end) as [付款]
from test101 t
group by [门店],[支付方式]
order by [门店]

看结果:(因为值都是一样,所以每个支付方式都是一样)
深入行列转换----多行转多列,多行的计算

 

 

 

 (1)原理剖析:通过门店与支付方式为分组,对支付方式进行行转列,并带着对应的付款数与手续费

select [门店],[支付方式],
max(case when [缴费大类]=0 then [付款] end) as '0',
max(case when [缴费大类]=1 then [付款] end) as '1',
max(case when [缴费大类]=2 then [付款] end) as '2',
max(case when [缴费大类]=3 then [付款] end) as '3',
max(case when [缴费大类]=0 then [手续费] end) as '4',
max(case when [缴费大类]=1 then [手续费] end) as '5',
max(case when [缴费大类]=2 then [手续费] end) as '6',
max(case when [缴费大类]=3 then [手续费] end) as '7'  
from test101 t
group by [门店],[支付方式]
order by [门店]

深入行列转换----多行转多列,多行的计算
可是这不就是sum嘛,把其转成负一就好
 SELECT  门店,支付方式,
      SUM(付款 *  case when [缴费大类] = '3' then   -1 else 1 end ) as 付款, 
      SUM(手续费 *  case when [缴费大类]  = '3' then   -1 else 1 end ) as 手续费
  FROM  表  group by 门店,支付方式 
 

 

 (2)由此可见,这里我们类似于下面这类形式的

  max(case when [缴费大类]=0 then [付款] end)

其实是获取,每个门店、每个消费方式对应缴费大类的值。

所以,我们可以直接通过下面这类值,来获取当个【门店】下当个【支付方式】对应【缴费大类】下的【付款】与【手续费】

  max(case when [缴费大类]=0 then isnull([付款],0) else 0 end) +
  max(case when [缴费大类]=1 then isnull([付款],0) else 0 end) +
  max(case when [缴费大类]=2 then isnull([付款],0) else 0 end) -
  max(case when [缴费大类]=3 then isnull([付款],0) else 0 end)

然后用此表达式就实现了我们的逻辑算法。

   挣钱:当支付方式为1时:累加支付方式0~2的付款数,减去消费大类为3(退款)的付款数,即为支付方式1所挣的钱。

   手续费:当支付方式为1时:累加支付方式0~2的手续费,减去消费大类为3(退款)的手续费,即为支付方式1所需要承担的手续费。

 

当然,也可以去用我们传统的方法:

深入行列转换----多行转多列,多行的计算

 

 (3)这里问题也来了,如果支付方式过多,1个门店会有很多行,这不利于我们查看,这里还需要再行转列一下,把支付方式变成列,付款和手续费变成对应转换后的列值

 

;with test1 as 
(
    select [门店],[支付方式],
        max(case when [缴费大类]=0 then [付款] end) as '0',
        max(case when [缴费大类]=1 then [付款] end) as '1',
        max(case when [缴费大类]=2 then [付款] end) as '2',
        max(case when [缴费大类]=3 then [付款] end) as '3',
        max(case when [缴费大类]=0 then [手续费] end) as '4',
        max(case when [缴费大类]=1 then [手续费] end) as '5',
        max(case when [缴费大类]=2 then [手续费] end) as '6',
        max(case when [缴费大类]=3 then [手续费] end) as '7'  
    from test101 t
    group by [门店],[支付方式]
) ,
test2 as (
    select  [门店],[支付方式],[0]+[1]+[2]-[3] as [付款],[4]+[5]+[6]-[7] as [手续费] 
    from test1
),
test3 as (
    select [门店],[1],[2],[3],[4]  , '付款' as [tpye] 
    from (select [门店],[支付方式],[付款] from test2) t
    pivot ( max([付款]) for [支付方式] in ([1] ,[2],[3],[4]) ) t1
    union all
    select [门店],[1],[2],[3],[4],'手续费' as [tpye] 
    from (select [门店],[支付方式],[手续费] from test2) t
    pivot ( max([手续费]) for [支付方式] in ([1],[2],[3],[4]) ) t1
)
select * from test3


test2数据:就是(2)中的图
深入行列转换----多行转多列,多行的计算
test3查询完结果如图:
深入行列转换----多行转多列,多行的计算

 



 很明显,我这个方法效率很低,要多次查询表,会造成太多额外的开销。如果有多个需要这样展示的选项,那开销将大一点,除非数据量特别小!不过,cte会把数据缓存在内存中,逻辑读还是比较快的,在数据量比较小的情况下不影响!如果数据量比较大,可以用全局临时表(避免需要重复创建)+给其建立索引,来优化,这样就会快一些了。

 而,我的朋友是这么写的

with test1 as 
(select [门店],[支付方式],
    max(case when [缴费大类]=0 then isnull([付款],0) else 0 end) +
    max(case when [缴费大类]=1 then isnull([付款],0) else 0 end) +
    max(case when [缴费大类]=2 then isnull([付款],0) else 0 end) -
    max(case when [缴费大类]=3 then isnull([付款],0) else 0 end) as [付款],
    max(case when [缴费大类]=0 then isnull([手续费],0) else 0 end) +
    max(case when [缴费大类]=1 then isnull([手续费],0) else 0 end) +
    max(case when [缴费大类]=2 then isnull([手续费],0) else 0 end) -
    max(case when [缴费大类]=3 then isnull([手续费],0) else 0 end) as [手续费]
from test101 t
group by [门店],[支付方式]
)
select 
  test1.[门店] ,
   max(case when test1.[支付方式] = '1' then [付款] end ) as [付款_1],
   max(case when test1.[支付方式] = '1' then [手续费] end ) as [手续费_1], 
   max(case when test1.[支付方式] = '2' then [付款] end ) as [付款_2],
   max(case when test1.[支付方式] = '2' then [手续费] end ) as [手续费_2], 
   max(case when test1.[支付方式] = '3' then [付款] end ) as [付款_3],
   max(case when test1.[支付方式] = '3' then [手续费] end ) as [手续费_3]  ,
   max(case when test1.[支付方式] = '4' then [付款] end ) as [付款_4],
   max(case when test1.[支付方式] = '4' then [手续费] end ) as [手续费_4]

  from test1
  group by test1.[门店]

这里的case when 都可以用decode来代替,显得代码少一点点。
结果:高下立判,他的方法高明的多,但这种情况,支付方式多了,或者显示的数据多了(这里只有付款数和手续费),那列的字段数量也将是灾难级的。看个人习惯喜欢看哪种方式展示数据。

深入行列转换----多行转多列,多行的计算
 

 

 或许是我还不太会用pivot,总觉得今天case when给了我很大震撼。。以前也不知道还能这么用,学习了,感谢小余同学的提问,互相学习共勉。

今天心里还在念叨这个事,于是又想了很多,去请教了一个人,终于用pivot unoivot解决了

;with t2 as
(
SELECT  门店,支付方式,
      SUM(付款 *  case when [缴费大类]  = '3' then   -1 else 1 end ) as 付款,
      SUM(手续费 *  case when [缴费大类]  = '3' then   -1 else 1 end ) as 手续费
  FROM  test101  group by 门店,支付方式
  )
   ----***第二步将加工过的数据 行转列
select * from 
(
---****先将第一步汇总的数据源列转行 select * from (SELECT [门店] ,[支付方式] ,[付款] ,[手续费] from t2 ) p UNPIVOT ( [Money] FOR PayClass IN (付款, 手续费) ) m
) p
----***列转行结束 PIVOT ( SUM([Money]) FOR [支付方式] IN ( [1],[2],[3],[4] ) ) as pvt ---***行专列结束


结果如下:
深入行列转换----多行转多列,多行的计算

 

 

 

 多行转列(2):~

另一种模式,我想这样转换

  深入行列转换----多行转多列,多行的计算

 

--项目:商品流通平台
SELECT * FROM dbo.r_request                --我的单
SELECT * FROM dbo.r_requestdetail        --我的单明细

SELECT * FROM dbo.r_stock                --我的货
SELECT * FROM dbo.r_stockdetail            --我的货明细

 
 
CREATE TABLE #T
(
    id UNIQUEIDENTIFIER,
    A INT,
    B INT  
)

INSERT INTO #T(    id,    A,    B) VALUES('EF28B498-A186-4B9B-AE38-3A2629F18377',1,2),('EF28B498-A186-4B9B-AE38-3A2629F18377',3,4),('EF28B498-A186-4B9B-AE38-3A2629F18377',5,6),('D22B8E18-B69D-4763-9C1E-E28BBBE15369',7,8)



SELECT * FROM #T

SELECT id,
SUM(CASE WHEN x=1 THEN A ELSE NULL END) AS A1,
SUM(CASE WHEN x=1 THEN B ELSE NULL END) AS B1,
SUM(CASE WHEN x=2 THEN A ELSE NULL END) AS A2,
SUM(CASE WHEN x=2 THEN B ELSE NULL END) AS B2,
SUM(CASE WHEN x=3 THEN A ELSE NULL END) AS A3,
SUM(CASE WHEN x=3 THEN B ELSE NULL END) AS B3
FROM 
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS x  FROM #T 
) A
GROUP BY id 

这样是固定的,其实是有问题的(比如我的X在一个大范围内比如1~10000都有可能,不能1+1一步一步判断吧),那么如果我要动态呢

DECLARE @M INT 
SELECT @M=MAX(A) FROM 
(
SELECT COUNT(*) A  FROM #T GROUP BY id 
)A 
DECLARE @W VARCHAR(max)=''
DECLARE @SQL VARCHAR(max)=''
SELECT @W=@W+',SUM(CASE WHEN x='+CONVERT(VARCHAR(10),number)+' THEN A ELSE NULL END) AS A1'+',SUM(CASE WHEN x='+CONVERT(VARCHAR(10),number)+' THEN B ELSE NULL END) AS B1' FROM master..spt_values WHERE type='P' AND number>0 AND number<=@M
SET @SQL='SELECT id'+@W+' 
FROM 
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS x  FROM #T 
) A
GROUP BY id '

EXEC (@SQL)

感谢sql server技术群--东莞-小小  的支持与帮助。

 

 

善用笛卡尔积

需求:

  深入行列转换----多行转多列,多行的计算

declare @temp table(a varchar(50), b varchar(16), c int);
insert into @temp values
('2', '201705', 50),
('2', '201708', 200),
('23', '201708', 1),
('23', '201705', 2);

select * from @temp as a left join @temp as b on(b.a=a.a) where a.b='201705' and b.b='201708';

select * from @temp pivot(sum(c) for b in([201705],[201708])) as x

  结果:

  深入行列转换----多行转多列,多行的计算