表1
F_Id F_name1 F_name2 F_num F_money
1 aaa bbb 100 200
2 ccc 200 300
3 ddd 300 300
现在想分开算两个人的工资,结果如下: F_name F_num F_money
aaa 50 100
bbb 50 100
ccc 200 300
ddd 300 300
如果F_name1,F_name2都不为空把一条记录分成两条,钱和数量对半分,如果其中有一个为空时就不分
F_Id F_name1 F_name2 F_num F_money
1 aaa bbb 100 200
2 ccc 200 300
3 ddd 300 300
现在想分开算两个人的工资,结果如下: F_name F_num F_money
aaa 50 100
bbb 50 100
ccc 200 300
ddd 300 300
如果F_name1,F_name2都不为空把一条记录分成两条,钱和数量对半分,如果其中有一个为空时就不分
create table table1
(
f_id int,
f_name1 varchar(5),
f_name2 varchar(5),
f_num int,
f_money int
)insert into table1 select 1, 'aaa', 'bbb', 100, 200
insert into table1 select 2, 'ccc', '', 200, 300
insert into table1 select 3, '', 'ddd', 300, 300 --语句
select f_name,sum(f_num) as f_num,sum(f_money) as f_money
from
(select case len(isnull(f_name1,'')) when 0 then f_name2 else f_name1 end as f_name,
f_num/2 as f_num,f_money/2 as f_money from table1
union all
select case len(isnull(f_name2,'')) when 0 then f_name1 else f_name2 end,f_num/2,f_money/2 from table1
)t1
group by f_name--结果
aaa 50 100
bbb 50 100
ccc 200 300
ddd 300 300--删除环境
drop table table1--注:不知道楼主所说的f_name1或者f_name2为空的标准是什么,如有差异,楼主可修改以上语句中的判断条件
(F_Id Int,
F_name1 Varchar(10),
F_name2 Varchar(10),
F_num Int,
F_money Int)
Insert TEST Select 1, 'aaa', 'bbb', 100, 200
Union All Select 2, 'ccc', Null, 200, 300
Union All Select 3, Null, 'ddd', 300, 300
GO
Select
F_name1 As F_name,
F_num/(Case When IsNull(F_name2,'')<>'' Then 2 Else 1 End) As F_num,
F_money/(Case When IsNull(F_name2,'')<>'' Then 2 Else 1 End) As F_money
From TEST
Where F_name1 Is Not Null
Union All
Select
F_name2,
F_num/(Case When IsNull(F_name1,'')<>'' Then 2 Else 1 End) As F_num,
F_money/(Case When IsNull(F_name1,'')<>'' Then 2 Else 1 End) As F_money
From TEST
Where F_name2 Is Not Null
Order By F_name
Go
Drop Table TEST
--Result
/*
F_name F_num F_money
aaa 50 100
bbb 50 100
ccc 200 300
ddd 300 300
*/
F_Id F_name1 F_name2 F_num F_money
1 aaa bbb 100 200
2 ccc 200 300
3 ddd 300 300 要不这样,我控制一下,F_name1不会为空,F_name2才能为空,当F_name2不为空时,便把数量和钱平分
declare @T Table
(F_Id Int,
F_name1 Varchar(10),
F_name2 Varchar(10),
F_num Int,
F_money Int)
Insert @T Select 1, 'aaa', 'bbb', 100, 200
Union All Select 2, 'ccc', Null, 200, 300
Union All Select 3, Null, 'ddd', 300, 300select * from @tselect F_name1 as F_name , F_num /2 as f_num ,f_money /2 as f_money from @t where f_name1 + f_name2 is not null
union all
select F_name2 , F_num /2 ,f_money /2 from @t where f_name1 + f_name2 is not null
union all
select isnull(rtrim(F_name1),'') + isnull(rtrim(F_name2),''),f_num ,f_money
from @t
where f_name1 + f_name2 is null