表格内容Nameid Tdate Tout Tin Tyu
29 2009-11-17 312.00
29 2009-11-30 286.00
29 2009-12-11 118.00
29 2009-12-22 121.00
29 2010-1-4 200.00
29 2010-1-18 118.00
29 2010-1-18 35.00
29 2010-2-28 38.00
29 2010-3-7 118.00
29 2010-3-7 30.00
29 2010-3-11 100.00
29 2010-3-18 10.00
29 2010-5-1 100.00
29 2010-5-1 35.00
29 2010-5-8 1000.00
29 2010-5-10 190.00
29 2010-7-4 81.00
29 2010-7-22 215.00
51 2009-11-17 850.50
51 2009-11-27 250.00
51 2009-12-11 126.00
51 2009-12-11 220.00
51 2009-12-12 100.00
51 2009-12-12 520.00
51 2010-1-15 178.00
51 2010-1-15 198.00
51 2010-3-14 105.00
51 2010-3-14 220.00
51 2010-5-24 300.00
51 2010-5-24 130.00
51 2010-5-24 350.00
51 2010-6-23 188.00
51 2010-6-23 35.00
表格内有多个商品出入明细数据,希望做一个Sql查询.结果是Nameid Tdate Tout Tin Tyu
29 2009-11-17 312.00
29 2009-11-30 286.00 598.00
29 2009-12-11 118.00 716.00
29 2009-12-22 121.00 837.00
29 2010-1-4 200.00 637.00
29 2010-1-18 118.00 755.00
29 2010-1-18 35.00 790.00
29 2010-2-28 38.00 828.00
29 2010-3-7 118.00 946.00
29 2010-3-7 30.00 976.00
29 2010-3-11 100.00 1076.00
29 2010-3-18 10.00 1086.00
29 2010-5-1 100.00 986.00
29 2010-5-1 35.00 951.00
29 2010-5-8 1000.00 -49.00
29 2010-5-10 190.00 141.00
29 2010-7-4 81.00 222.00
29 2010-7-22 215.00 437.00 我这里只要一个商品的流水.输入NAMEID,查询一个商品的流水账.有进,有出,有余数.
29 2009-11-17 312.00
29 2009-11-30 286.00
29 2009-12-11 118.00
29 2009-12-22 121.00
29 2010-1-4 200.00
29 2010-1-18 118.00
29 2010-1-18 35.00
29 2010-2-28 38.00
29 2010-3-7 118.00
29 2010-3-7 30.00
29 2010-3-11 100.00
29 2010-3-18 10.00
29 2010-5-1 100.00
29 2010-5-1 35.00
29 2010-5-8 1000.00
29 2010-5-10 190.00
29 2010-7-4 81.00
29 2010-7-22 215.00
51 2009-11-17 850.50
51 2009-11-27 250.00
51 2009-12-11 126.00
51 2009-12-11 220.00
51 2009-12-12 100.00
51 2009-12-12 520.00
51 2010-1-15 178.00
51 2010-1-15 198.00
51 2010-3-14 105.00
51 2010-3-14 220.00
51 2010-5-24 300.00
51 2010-5-24 130.00
51 2010-5-24 350.00
51 2010-6-23 188.00
51 2010-6-23 35.00
表格内有多个商品出入明细数据,希望做一个Sql查询.结果是Nameid Tdate Tout Tin Tyu
29 2009-11-17 312.00
29 2009-11-30 286.00 598.00
29 2009-12-11 118.00 716.00
29 2009-12-22 121.00 837.00
29 2010-1-4 200.00 637.00
29 2010-1-18 118.00 755.00
29 2010-1-18 35.00 790.00
29 2010-2-28 38.00 828.00
29 2010-3-7 118.00 946.00
29 2010-3-7 30.00 976.00
29 2010-3-11 100.00 1076.00
29 2010-3-18 10.00 1086.00
29 2010-5-1 100.00 986.00
29 2010-5-1 35.00 951.00
29 2010-5-8 1000.00 -49.00
29 2010-5-10 190.00 141.00
29 2010-7-4 81.00 222.00
29 2010-7-22 215.00 437.00 我这里只要一个商品的流水.输入NAMEID,查询一个商品的流水账.有进,有出,有余数.
解决方案 »
- 碰碰运气吧:一个DbGridEH的问题,好像比较麻烦,但愿有解!
- 关于自定义消息
- DELHPI5+SQL7.0写的数据库在WIN2000上不能写入数据到SQLSERVER(在线等)?
- delphi+sql技術論壇群 10998525 歡迎加入探討 頂者有分
- TFileStream问题
- 有没有深圳的兄弟,中秋散分了!!
- 如何用数字键盘的回车键模拟TAB键?
- 求版主别删!有谁认为论坛的新版本比原来的好?怎么觉得这么别扭?好多页都打不开了,连专家分都看不到了!!!真败了!!!
- 关于远程进程监控问题,急~~~~~~~
- 如何将一个窗口编译成EXE文件,之后再在别的窗口中调用。
- 菜鸟求助:关于多窗体
- 谁帮我看下这条语言哪错了
这个产品的流水账,简单的说,就是把余数一列填上数.我现在方法是把数据都读出来,然后一条一条地判断,把进\出数与上一条的余数作算术后,得到余数,再把余数读到STRINNDBGRID表格内....嫌麻烦,,,希望找到一条SQL语句,一次解决只需要一个 open,好清爽哦
GO
/****** Object: StoredProcedure [dbo].[SP_HR_WEEK_AUDIT] Script Date: 07/30/2010 11:53:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_HR_WEEK_AUDIT]
@SWHERE NVARCHAR(10)
AS
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW1] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW2] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW3] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HR_AUDI_WEEK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HR_AUDI_WEEK] --周稽核表
select distinct wk into _HW3 from ( select 'W'+convert(nvarchar(2),DATEPART(wk,DATEADD(day,-1,colrq))) as wk from HR_EMPLOYEE_DAY )a order by wk
SELECT B.*,C.BM INTO _HW1 FROM
(
SELECT COLAID,WK,SUM(COLWKT+COLJBT+COLXXRJB+COLJJRJB) AS SL
FROM
( select COLAID,COLRQ,'W'+convert(nvarchar(2),DATEPART(wk,DATEADD(day,-1,colrq))) AS WK,
COLWKT,COLJBT,COLXXRJB,COLJJRJB
from HR_EMPLOYEE_DAY
) A
GROUP BY COLAID,WK
) B LEFT JOIN Res_Hrd_Temp C ON B.COLAID=C.Account_Id
WHERE B.SL>CAST(@SWHERE AS FLOAT)
SELECT WK,BM,COUNT(*) AS SL INTO _HW2 FROM _HW1 GROUP BY WK,BM
declare @sql1_1 varchar(8000)
set @sql1_1 = 'select BM,'
select @sql1_1 = @sql1_1 + 'sum(case WK when '''+WK+'''then SL else 0 end) as '''+WK+''','
from (select WK from _HW3 ) as a
select @sql1_1 = left(@sql1_1,len(@sql1_1)-1) + ' INTO HR_AUDI_WEEK from _HW2 group by BM order by BM'
exec(@sql1_1)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW1] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW2] --臨時表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_HW3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_HW3] --臨時表
-- select * from HR_AUDI_WEEK
END