id money type riqi leibie
00004 90000 0 2011-04-11 00:00:00.000 01
00005 6000 0 2011-12-11 00:00:00.000 04
00009 5000 1 2011-02-11 00:00:00.000 01
00009 2000 1 2011-02-11 00:00:00.000 01
00016 4000 0 2011-05-11 00:00:00.000 03
00022 9000 1 2011-06-11 00:00:00.000 03
00005 500 1 2011-12-11 00:00:00.000 04
这是表中的一部分数据。
select id,leibie, sum(case when type='0' then money when type='1' then -1*money end)
from emp
group by id,leibie,money
一部分结果:
00010 02 -5503
00011 03 55051
00012 02 -55051
00013 04 -5491
00015 03 5491
00016 04 1051
00017 03 5851
要提取leibie=03对应的money大于100剩下的加到leibie=04中的money 中去,再判断是否会大于100
00004 90000 0 2011-04-11 00:00:00.000 01
00005 6000 0 2011-12-11 00:00:00.000 04
00009 5000 1 2011-02-11 00:00:00.000 01
00009 2000 1 2011-02-11 00:00:00.000 01
00016 4000 0 2011-05-11 00:00:00.000 03
00022 9000 1 2011-06-11 00:00:00.000 03
00005 500 1 2011-12-11 00:00:00.000 04
这是表中的一部分数据。
select id,leibie, sum(case when type='0' then money when type='1' then -1*money end)
from emp
group by id,leibie,money
一部分结果:
00010 02 -5503
00011 03 55051
00012 02 -55051
00013 04 -5491
00015 03 5491
00016 04 1051
00017 03 5851
要提取leibie=03对应的money大于100剩下的加到leibie=04中的money 中去,再判断是否会大于100
CREATE TABLE TEST
(
id VARCHAR(20),
money INT,
type INT,
riqi DATETIME,
leibie VARCHAR(20),
)
INSERT INTO TEST(id,money,type,riqi,leibie)
SELECT '00004',90000,0,'2011-04-11 00:00:00.000','01'
UNION ALL
SELECT '00005',6000,0,'2011-12-11 00:00:00.000','04'
UNION ALL
SELECT '00009',5000,1,'2011-02-11 00:00:00.000','01'
UNION ALL
SELECT '00009',2000,1,'2011-02-11 00:00:00.000','01'
UNION ALL
SELECT '00016',4000,0,'2011-05-11 00:00:00.000','03'
UNION ALL
SELECT '00022',9000,1,'2011-06-11 00:00:00.000','03'
UNION ALL
SELECT '00005',500,1,'2011-12-11 00:00:00.000','04'SELECT *
FROM TEST----提取leibie=03对应的money大于100;
;WITH
DD AS(
select id,leibie, sum(case when type='0' then money when type='1' then -1*money end) RESULT
from TEST
group by id,leibie,money
)
SELECT *
FROM DD
WHERE leibie='03' AND RESULT>100
----剩下的加到leibie=04中的money 中去
;WITH
DD AS(
select id,leibie, sum(case when type='0' then money when type='1' then -1*money end) RESULT
from TEST
group by id,leibie,money
)
UPDATE TEST
SET MONEY=(SELECT SUM(RESULT) FROM DD)-(SELECT RESULT FROM DD WHERE leibie='03' AND RESULT>100)
WHERE leibie='04'