国庆之前的一道难题，做好放假！

[samyou]

有一批记录，想将负数自动按照正数项目所含正数的比例分摊，如下：
细类 金额
A 400
B -100
C -200
D 500

总正数：900 总负数：-300
组合成：
A 400+4/9*(-300)
B 500+5/9*(-300)


请问各位大侠，sql有头绪吗？

[qqbz]

木有。
用存储过程或在客户端软件实现。

[-Tracy-McGrady-]

看不懂你写的，国庆不用休息了。

[汤姆克鲁斯]

唯一不明白的就是
a
b
c
d
怎么变成 a b 了

[天下如山]

这个?
SELECT NAME,NUM+NUM/(SELECT SUM(NUM) FROM ABC where NUM>0)*(SELECT SUM(NUM) FROM ABC where NUM<0) FROM ABC

[ttpsan550]

update tb set col=COL+COL/(select SUM(COL)from tb where COL>0) *(select SUM(COL)from tb where COL<0) 
where COL>0

不知道楼主是不是这个意思

[samyou]

C、D两项不需要显示了，因为它所属的负数要分摊到正数的项目里面。

[samyou]

B、C两项不需要显示了，因为它所属的负数要分摊到正数的项目里面。
而且不要用update啊，要用select，源数据不能进行修改。

[ttpsan550]

[Quote=引用 7 楼 的回复:]

B、C两项不需要显示了，因为它所属的负数要分摊到正数的项目里面。
而且不要用update啊，要用select，源数据不能进行修改。
[/Quote]
汗 把 upate 改成 select 不就好了

select  col=COL+COL/(select SUM(COL)from tb where COL>0) *(select SUM(COL)from tb where COL<0)
from tb 
where COL>0

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([细类] VARCHAR(1),[金额] INT)
INSERT [tb]
SELECT 'A',400 UNION ALL
SELECT 'B',-100 UNION ALL
SELECT 'C',-200 UNION ALL
SELECT 'D',500
--------------开始查询--------------------------

SELECT [细类],[金额]*1.0/(SELECT SUM([金额]) FROM [tb] WHERE [金额]>0)*(SELECT SUM([金额]) FROM [tb] WHERE [金额]<0) 
FROM [tb] WHERE [金额]>0
----------------结果----------------------------
/* 
细类	(无列名)
A	-133.333333333200
D	-166.666666666500
*/

[汤姆克鲁斯]

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([细类] VARCHAR(1),[金额] INT)
INSERT [tb]
SELECT 'A',400 UNION ALL
SELECT 'B',-100 UNION ALL
SELECT 'C',-200 UNION ALL
SELECT 'D',500
--------------开始查询--------------------------

SELECT [细类],[金额]+[金额]*1.0/(SELECT SUM([金额]) FROM [tb] WHERE [金额]>0)*(SELECT SUM([金额]) FROM [tb] WHERE [金额]<0) 
FROM [tb] WHERE [金额]>0
----------------结果----------------------------
/* 
细类	(无列名)
A	266.666666666800
D	333.333333333500*/

[gogodiy]

CREATE TABLE T1
(
	xilei VARCHAR(2),
	jine INT
)
INSERT INTO T1
SELECT 'A', 400 UNION ALL
SELECT 'B', -100 UNION ALL
SELECT 'C', -200 UNION ALL
SELECT 'D', 500
SELECT * FROM T1 WITH(NOLOCK)

;WITH AAA AS
(
	SELECT	SUM(CASE WHEN jine>=0 THEN jine ELSE 0 END) AS zheng,
			SUM(CASE WHEN jine<0 THEN jine ELSE 0 END) AS fu
	FROM	T1
)
,BBB AS
(
	SELECT	ROW_NUMBER() OVER(ORDER BY xilei) AS RowIndex,
			* 
	FROM	T1 AS A WITH(NOLOCK) CROSS JOIN 
			AAA AS B
	WHERE	A.jine>0
)
,CCC AS
(
	SELECT	CASE WHEN RowIndex=1 THEN 'A' ELSE 'B' END AS xilei,
			LTRIM(jine)+'+'+LTRIM(LEFT(jine,1))+'/'+LTRIM(LEFT(zheng,1))+'*'+'('+LTRIM(fu)+')' AS gongshi
	FROM	BBB
)
SELECT	*
FROM	CCC

--执行结果
xilei gongshi
----- ---------------------------------
A     400+4/9*(-300)
B     500+5/9*(-300)