34,590
社区成员
发帖
与我相关
我的任务
分享
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)
--> 测试数据:[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*/
--> 测试数据:[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
*/
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
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
这个?
SELECT NAME,NUM+NUM/(SELECT SUM(NUM) FROM ABC where NUM>0)*(SELECT SUM(NUM) FROM ABC where NUM<0) FROM ABC