求一个SQL语句的写法,高手赶紧进来拿分!绝对不难!

yyhao 2003-01-16 10:53:16
原始数据表如下:
日期    产品ID     产量
----- -------- ------
2002-1-1     A   20
2002-1-1     B          30
2002-1-1     C      40
2002-2-1     A      30
2002-2-1     B      10
2002-3-1     A      60
2002-3-1     C      40
......

要想得到的结果如下:
日期 产品    本月产量 累计产量
----- -----    -------- --------
2002-3-1      A 60 110
2002-3-1      B 0 30
2002-3-1      C 40 80
......




...全文
5 点赞 收藏 9
写回复
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
happydreamer 2003-01-28
select l.日期,l.产品ID,l.产量, sum(r.产量 ) as 总进货数量
from test l
join test r
on l.日期 >= r.日期 and l.产品ID=r.产品ID
where l.日期='2002-3-1'
group by l.产品ID,l.日期, l.产量
order by l.产品ID,l.日期
回复
错了:

select '2002-3-1' as 日期,a.产品id,isnull(sum(b.产量),0) as 本月产量,max(a.累计产量) as 累计产量
from (
select 产品ID,sum(产量) as 累计产量
from tablename where 日期<='2002-3-1' group by 产品ID
) as a left join tablename as b
on a.产品ID=b.产品ID
and b.日期='2002-3-1'
group by a.产品id
回复
select b.日期,a.产品id,sum(b.产量) as 本月产量,max(a.累计产量) as 累计产量
from (
select 产品ID,sum(产量) as 累计产量
from tablename where 日期<='2002-3-1' group by 产品ID
) as a left join tablename as b
on a.产品ID=b.产品ID
and b.日期='2002-3-1'
回复
happydreamer 2003-01-28

select c.日期,c.id,isnull(d.产量,0) into #tmp from

(select * from
(select distinct 日期 from ttt) as a
cross join
(select 'a' as id
union all
select 'b' as id
union all
select 'c' as id
)as b
) as c
left join ttt d on c.日期=d.日期
and c.id=d.id

得到
2002-01-01 00:00:00.000 a 20
2002-01-01 00:00:00.000 b 30
2002-01-01 00:00:00.000 c 40
2002-02-01 00:00:00.000 a 30
2002-02-01 00:00:00.000 b 10
2002-02-01 00:00:00.000 c 0
2002-03-01 00:00:00.000 a 60
2002-03-01 00:00:00.000 b 0
2002-03-01 00:00:00.000 c 40


再做连接
select l.日期,l.产品ID,l.产量, sum(r.产量 ) as 总进货数量
from #tmp l
join #tmp r
on l.日期 >= r.日期 and l.产品ID=r.产品ID
where l.日期='2002-3-1'
group by l.产品ID,l.日期, l.产量
order by l.产品ID,l.日期

回复
DainelLee 2003-01-17
来晚了
回复
select 日期,
产品ID as 产品,
sum(产量) as 本月产量,
(select sum(产量) from tablename
where 日期 <= '2002-3-1'
and 产品ID = A.产品ID) as 累计产量
from tablename as A
group by 日期, 产品ID
where 日期 = '2002-3-1'
order by 产品ID
回复
hjhing 2003-01-17
select distinct 日期,产品,
(select sum(产量) from yourTable where 日期='2002-3-1' and 产品=T.产品) as本月产量,
(select select sum(产量) from yourTable where 日期<'2002-4-1' and 产品=T.产品) as 累计产量
from yourtablename as T
where 日期='2002-3-1'
回复
microlong 2003-01-17
select 日期,产品,本月产量,累计产量 from yourtablename where 日期='2002-3-1' group by 产品
回复
yyhao 2003-01-16
上面写错了一个数。
原始数据表如下:
日期    产品ID     产量
----- -------- ------
2002-1-1     A 20
2002-1-1     B          30
2002-1-1     C      40
2002-2-1     A      30
2002-2-1     B      10
2002-3-1     A      60
2002-3-1     C      40
......

要想得到的结果如下:
日期 产品    本月产量 累计产量
----- -----    -------- --------
2002-3-1      A 60 110
2002-3-1      B 0 40
2002-3-1      C 40 80
......
回复
发动态
发帖子
非技术版
创建于2007-09-28

1.1w+

社区成员

MS-SQL Server 非技术版
申请成为版主
社区公告
暂无公告