34,590
社区成员
发帖
与我相关
我的任务
分享
declare @a table (ID int IDENTITY,PID int,PNAME varchar(20),PNUM int,PTIME datetime)
insert @a
select 100001,'AA',100,getdate()-1 Union ALL
select 100002,'AB',100,getdate()-1 Union ALL
select 100001,'AA',200,getdate()-2 Union ALL
select 100002,'AB',200,getdate()-2 Union ALL
select 100001,'AA',300,getdate()-3 Union ALL
select 100002,'AB',300,getdate()-3 Union ALL
select 100001,'AA',400,getdate()-4 Union ALL
select 100002,'AB',400,getdate()-4 Union ALL
select 100001,'AA',500,getdate()-5 Union ALL
select 100002,'AB',500,getdate()-5
declare @b table (Buy_ID int IDENTITY,Buy_PID int,Buy_PNAME varchar(20),Buy_PNUM int,Buy_PTIME datetime)
insert @b
select 100001,'AA',10,getdate()-1 Union ALL
select 100002,'AB',10,getdate()-1 Union ALL
select 100001,'AA',20,getdate()-2 Union ALL
select 100002,'AB',20,getdate()-2 Union ALL
select 100001,'AA',30,getdate()-3 Union ALL
select 100002,'AB',30,getdate()-3 Union ALL
select 100001,'AA',40,getdate()-4 Union ALL
select 100002,'AB',40,getdate()-4 Union ALL
select 100001,'AA',50,getdate()-5 Union ALL
select 100002,'AB',50,getdate()-5
declare @z1 datetime
declare @z2 datetime
set @z1='2009-02-07 08:00:00'
set @z2='2009-02-08 07:59:59'
select * from @a
select * from @b
select a.PNAME 品名,
ISNULL(sum(e.zt),0) 前一天结余,
sum(a.PNUM) 当天进货,sum(b.Buy_PNUM) 当天销售
from @a a
join @b b
on a.PID=b.Buy_PID and a.PNAME=b.Buy_PNAME
left join (
select PID,PNAME,sum(cc.PNUM)-sum(dd.Buy_PNUM) zt
from @a cc
join @b dd on cc.PID=dd.Buy_PID and cc.PNAME=dd.Buy_PNAME
where (cc.PTIME BETWEEN @z1-1 AND @z2-1) and (dd.Buy_PTIME BETWEEN @z1-1 AND @z2-1)
group by cc.PID,cc.PNAME
) e
on a.PID=e.PID and a.PNAME=e.PNAME
where (a.PTIME BETWEEN @z1 AND @z2) and (b.Buy_PTIME BETWEEN @z1 AND @z2)
group by a.PNAME
/****
/*
--表1
ID PID PNAME PNUM PTIME
3 100001 AA 200 2009-02-07 12:40:05.793
4 100002 AB 200 2009-02-07 12:40:05.793
5 100001 AA 300 2009-02-06 12:40:05.793
6 100002 AB 300 2009-02-06 12:40:05.793
--表2
Buy_ID Buy_PID Buy_PNAME Buy_PNUM Buy_PTIME
3 100001 AA 20 2009-02-07 12:40:05.793
4 100002 AB 20 2009-02-07 12:40:05.793
5 100001 AA 30 2009-02-06 12:40:05.793
6 100002 AB 30 2009-02-06 12:40:05.793
--结果
品名 前一天结余 当天进货 当天销售
AA 270 200 20
AB 270 200 20
*/
CREATE TABLE [表1] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[PID] [int] NOT NULL,
[PName] [nvarchar] (50) NOT NULL,
[PNum] [int] NOT NULL),
[PTime] [datetime] NOT NULL DEFAULT (getdate())
CREATE TABLE [表2] (
[Buy_ID] [int] IDENTITY (1, 1) NOT NULL,
[Buy_PID] [int] NOT NULL,
[Buy_PName] [nvarchar] (250) NULL,
[Buy_PNum] [int] NOT NULL,
[Buy_PTime] [datetime] NOT NULL DEFAULT (getdate())
请给出表结构,测试数据,相关算法和需要的结果.谢谢!
给的数据少了点,理解也知道这里了
select 前一天的结余 = ((select sum(pnum) from 表1 where ptime < convert(nvarchar(11),getdate(),120) and pid = a.pid)
- (select sum(pnum) from 表2 where ptime < convert(nvarchar(11),getdate(),120) and buy_pid = a.pid))
,当天进货 = (select sum(pnum) from 表1 where pid = a.pid and convert(nvarchar(11),ptime ,120) = a.convert(nvarchar(11),ptime ,120))
,当天销售 = (select sum(pnum) from 表2 where buy_pid = a.pid and convert(nvarchar(11),buy_ptime ,120) = a.convert(nvarchar(11),ptime ,120))
select convert(nvarchar(11),getdate(),120)