SQL2000 表名:test
RY_ID NAME DATE_X AA BB
1 张三 2012-12-1 1 1
1 张三 2013-3-1 1
2 李四 2013-5-1 10 15
请问查询语句如何写,可得到如下结果:
1、 当时间条件为小于2013-4-1时得到:
RY_ID NAME AA BB
1 张三 2 1
2 李四 0 02、 当时间条件为小于2013-3-1时得到:
RY_ID NAME AA_sum BB_sum
1 张三 1 1
2 李四 0 0
RY_ID NAME DATE_X AA BB
1 张三 2012-12-1 1 1
1 张三 2013-3-1 1
2 李四 2013-5-1 10 15
请问查询语句如何写,可得到如下结果:
1、 当时间条件为小于2013-4-1时得到:
RY_ID NAME AA BB
1 张三 2 1
2 李四 0 02、 当时间条件为小于2013-3-1时得到:
RY_ID NAME AA_sum BB_sum
1 张三 1 1
2 李四 0 0
NAME varchar(10),
DATE_X datetime,
AA int,
BB int)
insert into #test
select 1,'张三','2012-12-1',1,1
union all select 1,'张三','2013-3-1',1,null
union all select 2,'李四','2013-5-1',10,15select * from #test1、 当时间条件为小于2013-4-1时
select a.*,isnull(b.AA,0) as AA,isnull(b.BB,0) as BB
from (select distinct RY_ID,name from #test)a
left join (select RY_ID,NAME,sum(AA) as AA,sum(BB) as BB from #test where DATE_X<'2013-4-1' group by RY_ID,NAME)b
on a.RY_ID=b.RY_ID/*
1 张三 2 1
2 李四 0 0
*/2、 当时间条件为小于2013-3-1
select a.*,isnull(b.AA,0) as AA,isnull(b.BB,0) as BB
from (select distinct RY_ID,name from #test)a
left join (select RY_ID,NAME,sum(AA) as AA,sum(BB) as BB from #test where DATE_X<'2013-3-1' group by RY_ID,NAME)b
on a.RY_ID=b.RY_ID/*
1 张三 1 1
2 李四 0 0
*/
select A.RY_ID,A.Name,sum(AA),sum(BB) from
(select * from test where DATE_X<'2013-4-1')T
right join
(select distinct RY_ID, Name from test ) A
on T.Name=A.Name
group by A.RY_ID,A.Name
SELECT DISTINCT a.RY_ID,a.[NAME],ISNULL(b.AA,0) AS AA,ISNULL(b.BB,0) AS BB
FROM Ta a
LEFT JOIN
(
SELECT [NAME],AA=SUM(ISNULL(AA,0)),BB=SUM(ISNULL(BB,0)) FROM Ta WHERE DATE_X<'2013-3-1' GROUP BY [NAME]
) b
ON a.[NAME]=b.[NAME]
ORDER BY a.RY_ID
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta
( RY_ID INT,
[NAME] VARCHAR(20),
DATE_X DATETIME,
AA INT,
BB INT
)
GO
INSERT Ta
SELECT 1,'张三','2012-12-1',1,1 UNION
SELECT 1,'张三','2013-3-1',1,NULL UNION
SELECT 2,'李四','2013-5-1',10,15
SELECT DISTINCT a.RY_ID,a.[NAME],ISNULL(b.AA,0) AS AA,ISNULL(b.BB,0) AS BB
FROM Ta a
LEFT JOIN
(
SELECT [NAME],AA=SUM(ISNULL(AA,0)),BB=SUM(ISNULL(BB,0)) FROM Ta WHERE DATE_X<'2013-3-1' GROUP BY [NAME]
) b
ON a.[NAME]=b.[NAME]
ORDER BY a.RY_ID