表格内容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,查询一个商品的流水账.有进,有出,有余数.
这个产品的流水账,简单的说,就是把余数一列填上数.我现在方法是把数据都读出来,然后一条一条地判断,把进\出数与上一条的余数作算术后,得到余数,再把余数读到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