/* ************************************* * T-MAC 小编 * * -->努力成长中 * * -->梦想DBA * ************************************* */ if OBJECT_ID('tb') is not null drop table tb go create table tb ( A int, B int ,kn int, leibie varchar(10 ),xuhao varchar(10)) insert tb select 22 ,10 ,12 ,'类1' ,'K1' union select 34 ,20 ,14 ,'类1' ,'K1' union select 39 ,18 ,21 ,'类2' ,'K2' go with cte as ( select *,rn=ROW_NUMBER()over(partition by xuhao order by A DESC) from tb ) select xuhao, A1=isnull(MAX(CASE WHEN RN%2=0 THEN A END),0), B1=isnull(MAX(CASE WHEN RN%2=0 THEN B END),0), kn1=isnull(MAX(CASE WHEN RN%2=0 THEN A END),0)-isnull(MAX(CASE WHEN RN%2=0 THEN B END),0), A1=isnull(MAX(CASE WHEN RN%2=1 THEN A END),0), B1=isnull(MAX(CASE WHEN RN%2=1 THEN B END),0), kn2=isnull(MAX(CASE WHEN RN%2=1 THEN A END),0)-isnull(MAX(CASE WHEN RN%2=1 THEN B END),0) FROM cte GROUP BY xuhao,(rn-1)/2 /* xuhao A1 B1 kn1 A1 B1 kn2 ---------- ----------- ----------- ----------- ----------- ----------- ----------- K1 22 10 12 34 20 14 K2 0 0 0 39 18 21*/
-设表名为T1 select a.xubao,a.a a1,a.b b1,a.a-a.b kn1,b.a a2,b.b b2,b.a-b.b kn2 from t1 a left join t1 b on a.xuhao=b.xuhao and b.leibie='类2' where a.leibie='类1'
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb ( A int, B int ,kn int, leibie varchar(10 ),xuhao varchar(10))
insert tb select
22 ,10 ,12 ,'类1' ,'K1' union select
34 ,20 ,14 ,'类1' ,'K1' union select
39 ,18 ,21 ,'类2' ,'K2'
go
with cte as
(
select *,rn=ROW_NUMBER()over(partition by xuhao order by A DESC)
from tb )
select xuhao,
A1=isnull(MAX(CASE WHEN RN%2=0 THEN A END),0),
B1=isnull(MAX(CASE WHEN RN%2=0 THEN B END),0),
kn1=isnull(MAX(CASE WHEN RN%2=0 THEN A END),0)-isnull(MAX(CASE WHEN RN%2=0 THEN B END),0),
A1=isnull(MAX(CASE WHEN RN%2=1 THEN A END),0),
B1=isnull(MAX(CASE WHEN RN%2=1 THEN B END),0),
kn2=isnull(MAX(CASE WHEN RN%2=1 THEN A END),0)-isnull(MAX(CASE WHEN RN%2=1 THEN B END),0)
FROM cte
GROUP BY xuhao,(rn-1)/2
/*
xuhao A1 B1 kn1 A1 B1 kn2
---------- ----------- ----------- ----------- ----------- ----------- -----------
K1 22 10 12 34 20 14
K2 0 0 0 39 18 21*/
不光是2个那要怎么分区呢
没事的 K8 那你分组的时候就是OUP BY xuhao,(rn-1)/8
以我写的到k2位置的。
(rn-1)/2 在rn=1 和2 的时候 (rn-1)/2 =0
3,4的时候是(rn-1)/2 =1
.....
懂了么
select a.xubao,a.a a1,a.b b1,a.a-a.b kn1,b.a a2,b.b b2,b.a-b.b kn2
from t1 a left join t1 b on a.xuhao=b.xuhao and b.leibie='类2'
where a.leibie='类1'