首先,有两个表,一个是用户存钱的表,一个是用户消费(包括收入和支出)的两个表,例如,表结构大体如下:
用户存钱的表(cunqian):
id user_id volume time
1 0001 100 2007-06-11
2 0001 100 2007-06-15
3 0001 100 2007-06-18
用户消费的表(shouzhi):
id user_id volume time flag
1 0001 50 2007-06-18 0
2 0001 10 2007-06-18 1
3 0001 8 2007-06-18 1
业务逻辑如下:首先用户在存钱(cunqian)表存钱,然后就可以消费了;消费时在消费(shouzhi)表中记录消费情况,如果flag值是0,证明是花钱了;flag值是1,证明是收入了。
我在程序里控制:
SELECT SUM(VOLUME) FROM CUNQIAN WHERE USER_ID='0001' 得到指定用户的总金额
SELECT SUM(VOLUME) FROM SHOUXIAO WHERE FLAG=1 AND USER_ID='0001' 得到指定用户的总收入
SELECT SUM(XS_VOLUME) FROM SHOUXIAO WHERE FLAG=0 AND USER_ID='0001' 得到指定用户的总支出(消费)
然后在程序计算用户当前的总余额。需求:怎么在SQL里实现同样的功能,最好是用视图实现,存储亦可,不之道MYSQL5支不支持存储过程,只要不是像我这样在程序里控制就行。我的SQL垃圾,先多谢大家了。
用户存钱的表(cunqian):
id user_id volume time
1 0001 100 2007-06-11
2 0001 100 2007-06-15
3 0001 100 2007-06-18
用户消费的表(shouzhi):
id user_id volume time flag
1 0001 50 2007-06-18 0
2 0001 10 2007-06-18 1
3 0001 8 2007-06-18 1
业务逻辑如下:首先用户在存钱(cunqian)表存钱,然后就可以消费了;消费时在消费(shouzhi)表中记录消费情况,如果flag值是0,证明是花钱了;flag值是1,证明是收入了。
我在程序里控制:
SELECT SUM(VOLUME) FROM CUNQIAN WHERE USER_ID='0001' 得到指定用户的总金额
SELECT SUM(VOLUME) FROM SHOUXIAO WHERE FLAG=1 AND USER_ID='0001' 得到指定用户的总收入
SELECT SUM(XS_VOLUME) FROM SHOUXIAO WHERE FLAG=0 AND USER_ID='0001' 得到指定用户的总支出(消费)
然后在程序计算用户当前的总余额。需求:怎么在SQL里实现同样的功能,最好是用视图实现,存储亦可,不之道MYSQL5支不支持存储过程,只要不是像我这样在程序里控制就行。我的SQL垃圾,先多谢大家了。
这个里面的XS_VOLUME在这里就是shouzhi表里的VOLUME,刚才粘贴过来的时候疏忽了。
说一下,volume是金额。
创建一个传入user_id值的存储过程,得到结果~~~
顶~~~~~~~
怎么人不多啊?还是提的问题不科学啊 ~~~~
CREATE PROCEDURE a @@SUM money OUTPUT
AS
SELECT @@SUM = SUM(VOLUME) FROM CUNQIAN WHERE USER_ID='0001'
GO
declare @b money
exec a @b output
select @b+@b
print convert(varchar(12),@b)--------------
第二种方法
declare @c money
SELECT @c = SUM(VOLUME) FROM CUNQIAN WHERE USER_ID='0001'
select @b+@b
print convert(varchar(12),@c)
CREATE VIEW yueV as
SELECT C1.SV+S1.SV-S2.sv FROM
(SELECT SUM(VOLUME) as SV FROM CUNQIAN WHERE USER_ID='0001' ) as C1,
(SELECT SUM(VOLUME) as SV FROM SHOUXIAO WHERE FLAG=1 AND USER_ID='0001') as S1,
(SELECT SUM(VOLUME) as SV FROM SHOUXIAO WHERE FLAG=0 AND USER_ID='0001') as S2好想是这个样子写的啦,基本语句是你写的,我组合了一下,没有经过测试.
SELECT C1.SV+S1.SV-S2.sv FROM
(SELECT SUM(VOLUME) as SV FROM CUN WHERE USER_ID='0001' ) as C1,
(SELECT SUM(VOLUME) as SV FROM SZ WHERE FLAG=1 AND USER_ID='0001') as S1,
(SELECT SUM(VOLUME) as SV FROM SZ WHERE FLAG=0 AND USER_ID='0001') as S2~~~~~~~~~~~~~~~~~多谢,结贴
CREATE VIEW YE (YE_VOLUME, USER_ID,SHOU,ZHI,ZONG) AS
SELECT C1.SV+S1.SV-S2.SV ,C1.USER_ID AS USER_ID,S1.SV,S2.SV,C1.SV FROM
(SELECT SUM(VOLUME) AS SV, USER_ID FROM CUN GROUP BY USER_ID) AS C1,
(SELECT SUM(VOLUME) AS SV, USER_ID FROM SZ WHERE FLAG=1 GROUP BY USER_ID) AS S1,
(SELECT SUM(VOLUME) AS SV, USER_ID FROM SZ WHERE FLAG=0 GROUP BY USER_ID) AS S2 WHERE C1.USER_ID=S1.USER_ID AND C1.USER_ID=S2.USER_ID