select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb union all select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb
select 入库货单号,出库货单号,A货数目,B货数目,费用A,费用B from( select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序 from tb union all select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序 from tb )t order by 排序
select 入库货单号,CAST(NULL AS VARCHAR(30)) as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb union all select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb
--新手上路 if (OBJECT_ID('TB') is not null)drop table tbcreate table tb ([入库货单号] varchar(20),[出库货单号] varchar(20),[A货数目] int, [B货数目] int,[费用A] int,[费用B] int)insert into tb select '111','222','10','15','50','100' insert into tb select '333','444','10','15','50','100'select [入库货单号],null as [出库货单号],[A货数目],[B货数目],[费用A]/2,[费用B]/2 from tb union all select null as [入库货单号],[出库货单号],[A货数目],[B货数目],[费用A]/2,[费用B]/2 from tb/* 入库货单号 出库货单号 A货数目 B货数目 ---------- ---------- ----------- ----------- ----------- ----------- 111 NULL 10 15 25 50 333 NULL 10 15 25 50 NULL 222 10 15 25 50 NULL 444 10 15 25 50(4 行受影响) */
create table #test (入库货单号 nvarchar(10),出库货单号 nvarchar(10),A货数目 int,B货数目 int,费用A int,费用B int) insert #test select '999','333',54,78,32,33 union all select '111','222',10,15,50,100 union all select '333','444',10,15,50,100select (case when number=1 then 入库货单号 else '' end) as 入库货单号, (case when number=2 then 出库货单号 else '' end) as 出库货单号, A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from #test as t join master..spt_values as v on v.number between 1 and 2 and v.type='p'--入库货单号 出库货单号 A货数目 B货数目 费用A 费用B ------------ ---------- ----------- ----------- ----------- ----------- --111 10 15 25 50 --333 10 15 25 50 --999 54 78 16 16 -- 222 10 15 25 50 -- 444 10 15 25 50 -- 333 54 78 16 16
create table tb ([入库货单号] varchar(20),[出库货单号] varchar(20),[A货数目] int, [B货数目] int,[费用A] int,[费用B] int)insert into tb select '111','222','10','15','50','100' insert into tb select '333','444','10','15','50','100' goselect 入库货单号,出库货单号,A货数目,B货数目,费用A,费用B from( select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序 from tb union all select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序 from tb )t order by 排序,入库货单号 descdrop table tb/***************入库货单号 出库货单号 A货数目 B货数目 费用A 费用B -------------------- -------------------- ----------- ----------- ----------- ----------- 111 NULL 10 15 25 50 NULL 222 10 15 25 50 333 NULL 10 15 25 50 NULL 444 10 15 25 50(4 行受影响)
select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B into caifen from test union all select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from test
select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb
union all
select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb
select 入库货单号,出库货单号,A货数目,B货数目,费用A,费用B
from(
select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序
from tb
union all
select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序
from tb
)t
order by 排序
union all
select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B from tb
--新手上路
if (OBJECT_ID('TB') is not null)drop table tbcreate table tb ([入库货单号] varchar(20),[出库货单号] varchar(20),[A货数目] int,
[B货数目] int,[费用A] int,[费用B] int)insert into tb
select '111','222','10','15','50','100'
insert into tb
select '333','444','10','15','50','100'select [入库货单号],null as [出库货单号],[A货数目],[B货数目],[费用A]/2,[费用B]/2 from tb
union all
select null as [入库货单号],[出库货单号],[A货数目],[B货数目],[费用A]/2,[费用B]/2 from tb/*
入库货单号 出库货单号 A货数目 B货数目
---------- ---------- ----------- ----------- ----------- -----------
111 NULL 10 15 25 50
333 NULL 10 15 25 50
NULL 222 10 15 25 50
NULL 444 10 15 25 50(4 行受影响)
*/
create table #test
(入库货单号 nvarchar(10),出库货单号 nvarchar(10),A货数目 int,B货数目 int,费用A int,费用B int)
insert #test
select '999','333',54,78,32,33 union all
select '111','222',10,15,50,100 union all
select '333','444',10,15,50,100select
(case when number=1 then 入库货单号 else '' end) as 入库货单号,
(case when number=2 then 出库货单号 else '' end) as 出库货单号,
A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B
from #test as t join master..spt_values as v
on v.number between 1 and 2 and v.type='p'--入库货单号 出库货单号 A货数目 B货数目 费用A 费用B
------------ ---------- ----------- ----------- ----------- -----------
--111 10 15 25 50
--333 10 15 25 50
--999 54 78 16 16
-- 222 10 15 25 50
-- 444 10 15 25 50
-- 333 54 78 16 16
create table tb ([入库货单号] varchar(20),[出库货单号] varchar(20),[A货数目] int,
[B货数目] int,[费用A] int,[费用B] int)insert into tb
select '111','222','10','15','50','100'
insert into tb
select '333','444','10','15','50','100'
goselect 入库货单号,出库货单号,A货数目,B货数目,费用A,费用B
from(
select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序
from tb
union all
select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B,入库货单号 as 排序
from tb
)t
order by 排序,入库货单号 descdrop table tb/***************入库货单号 出库货单号 A货数目 B货数目 费用A 费用B
-------------------- -------------------- ----------- ----------- ----------- -----------
111 NULL 10 15 25 50
NULL 222 10 15 25 50
333 NULL 10 15 25 50
NULL 444 10 15 25 50(4 行受影响)
select 入库货单号,NULL as 出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B
into caifen
from test
union all
select NULL as 入库货单号,出库货单号,A货数目,B货数目,费用A/2 as 费用A,费用B/2 as 费用B
from test