这个活还是让前台程序去完成吧?SQL写这个好像不好写啵,我不懂,看过一个朋友写的一样效果的,是用中间量保存chargePrice然后在结果表中逐行计算逐行更新,有点郁闷。
解决方案 »
- 怎样用SQL做后台的录入、修改、查询、删除?在哪编写?
- 有两个简单的问题,请各位大虾赐教!
- 求简单SQL语句
- A机子windows xp 安装sql2000,delphi编程,程序能让B机使用并调用A数据库,请指导一下。
- 求助~~~ Sql语句怎么写,考虑效率!!(急,在线等)
- 字符的问题
- 要被老板炒了,救我呀,数据用TRUNCATE TABLE清空了,能恢复吗?
- sql 索引问题!
- 大侠帮忙!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 垃圾ACCESS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 帮忙啊,急
- 帮忙看一段SQL,帮我改正一下~谢谢各位高手啦~
create table #charge ( Billno varchar (255) , sumPrice int , billDate datetime )insert #charge ( billno , sumprice , billdate ) values ( 'sk-001' , 500 , '2005-05-06' )
insert #charge ( billno , sumprice , billdate ) values ( 'sk-002' , 1000 , '2005-05-07' )create table #sale ( Billno varchar (255) , sumPrice int , billDate datetime )insert #sale ( billno , sumprice , billdate ) values ( 'xs-001' , 1000 , '2005-05-05' )
insert #sale ( billno , sumprice , billdate ) values ( 'xs-002' , 1500 , '2005-05-08' )
insert #sale ( billno , sumprice , billdate ) values ( 'xs-003' , 800 , '2005-05-10' )
select T.*
into #tp
from (select * from #sale union all select * from #charge) T
order by billDateselect identity(int,1,1) as no,*
into #tp2
from #tpselect * from #tp2select no,
Billno,
case when left(Billno,2)='sk' then sumPrice else 0 end as priceLess,
case when left(Billno,2)='xs' then sumPrice else 0 end as priceAdd,
(select sum(case when left(Billno,2)='sk' then -1*sumPrice else sumPrice end) from #tp2 where no<=t.no) as chargePrice
from #tp2 t
drop table #tp,#tp2,#charge,#sale/*应收表:chargeBook
No BillNo priceLess priceAdd chargePrice
1 xs-001 0 1000 1000
2 sk-001 500 0 500
3 sk-002 1000 0 -500
4 xs-002 0 1500 1000
5 xs-003 0 800 1800
*/
A.sunPrice as priceLess,
A.sumPrice as priceAdd,
(A.sunPrice-A.sumPrice) as chargePrice
from (
select billNo,'' as sumPrice,sumPrice from sale
union
select billNo,sunPrice,'' from charge ) A
A.sunPrice as priceLess,
A.sumPrice as priceAdd,
(A.sunPrice-A.sumPrice) as chargePrice
from (
select billNo,'' as sunPrice,sumPrice from sale
union
select billNo,sunPrice,'' from charge ) A
DECLARE @CHARGE TABLE(BILLNO VARCHAR(255), SUMPRICE INT, BILLDATE DATETIME)
INSERT INTO @SALE
SELECT 'xs-001', 1000, '2005-05-05'
UNION
SELECT 'xs-002', 1500, '2005-05-08'
UNION
SELECT 'xs-003', 800, '2005-05-10'INSERT INTO @CHARGE
SELECT 'sk-001', 500, '2005-05-06'
UNION
SELECT 'sk-002', 1000, '2005-05-07'SELECT IDENTITY(INT, 1, 1) AS [No], * INTO # FROM
(SELECT BILLNO, 0 AS PRICELESS, SUMPRICE AS PRICEADD, BILLDATE FROM @SALE
UNION
SELECT BILLNO, SUMPRICE AS PRICELESS, 0 AS PRICEADD, BILLDATE FROM @CHARGE) AS A
ORDER BY BILLDATESELECT [No], BILLNO, PRICELESS, PRICEADD, (SELECT SUM(PRICEADD) - SUM(PRICELESS) FROM # WHERE BILLDATE <= A.BILLDATE) AS CHARGEPRICE FROM # AS ADROP TABLE #