现有一表,其格式及内容如下大类 小类 名称 进货日期 进货金额 出货日期 出货金额
水果 苹果 红富士 2005-1-1 10000 2005-2-8 13000
水果 苹果 国光 2005-2-5 20000 2005-2-15 25000
干果 桃脯 益民桃脯 2005-1-9 9000 2005-1-20 11000
水果 香蕉 海南香蕉 2005-2-3 8000 2005-2-9 9900
......希望得到如下表,(如果指定年为2005年,月份为2005年1月)大类 小类 年进货金额 年出货金额 月进货金额 月出货金额
水果 苹果 38000 47900 8000 0
干果 桃脯 8000 10000 9000 11000
......
(如果指定年为2005年,月份为2005年2月)大类 小类 年进货金额 年出货金额 月进货金额 月出货金额
水果 苹果 38000 47900 28000 47900
干果 桃脯 8000 10000 0 0
......请大侠指教,谢谢
水果 苹果 红富士 2005-1-1 10000 2005-2-8 13000
水果 苹果 国光 2005-2-5 20000 2005-2-15 25000
干果 桃脯 益民桃脯 2005-1-9 9000 2005-1-20 11000
水果 香蕉 海南香蕉 2005-2-3 8000 2005-2-9 9900
......希望得到如下表,(如果指定年为2005年,月份为2005年1月)大类 小类 年进货金额 年出货金额 月进货金额 月出货金额
水果 苹果 38000 47900 8000 0
干果 桃脯 8000 10000 9000 11000
......
(如果指定年为2005年,月份为2005年2月)大类 小类 年进货金额 年出货金额 月进货金额 月出货金额
水果 苹果 38000 47900 28000 47900
干果 桃脯 8000 10000 0 0
......请大侠指教,谢谢
(select sum(进货金额)from Table where 进货日期 between '2005-01-01' and '2006-01-01') as 年进货金额,
(select sum(出货金额)from Table where 出货日期 between '2005-01-01' and '2006-01-01') as 年出货金额,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005-01-01' and '2005-01-31'
group by 大类,小类没测试,你自己试试
(select sum(进货金额)from Table where 进货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as 年进货金额,
(select sum(出货金额)from Table where 出货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as 年出货金额,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005-01-01' and '2005-01-31'
group by 大类,小类或者这样写:
select a.大类, a.小类, b.年进货金额, c.年出货金额, a.月进货金额, a.月出货金额 from
(select 大类,小类,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005- 01-01' and '2005-01-31' group by 大类,小类) as a
join
(select sum(进货金额) as 年进货金额 from Table where 进货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as b
on a.大类=b.大类 and a.小类=b.小类
join
(select sum(出货金额)from Table where 出货日期 between '2005-01-01' and '2005-12- 31' group by 大类, 小类) as c
on a.大类=c.大类 and a.小类=c.小类
order by 大类,小类
select a.大类, a.小类, b.年进货金额, c.年出货金额, a.月进货金额, a.月出货金额 from
(select 大类,小类,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005- 01-01' and '2005-01-31' group by 大类,小类) as a
join
(select sum(进货金额) as 年进货金额 from Table where 进货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as b
on a.大类=b.大类 and a.小类=b.小类
join
(select sum(出货金额) as 年出货金额 from Table where 出货日期 between '2005-01-01' and '2005-12- 31' group by 大类, 小类) as c
on a.大类=c.大类 and a.小类=c.小类
order by 大类,小类
(select 大类,小类,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005- 01-01' and '2005-01-31' group by 大类,小类) as a
join
(select sum(进货金额) as 年进货金额 from Table where 进货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as b
on a.大类=b.大类 and a.小类=b.小类
join
(select sum(出货金额) as 年出货金额 from Table where 出货日期 between '2005-01-01' and '2005-12- 31' group by 大类, 小类) as c
on a.大类=c.大类 and a.小类=c.小类
order by a.大类,a.小类
(select 大类,小类,
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 between '2005-01-01' and '2005-01-31' and 出货日期 between '2005- 01-01' and '2005-01-31' group by 大类,小类) as a
join
(select 大类, 小类, sum(进货金额) as 年进货金额 from Table where 进货日期 between '2005-01-01' and '2005-12-31' group by 大类, 小类) as b
on a.大类=b.大类 and a.小类=b.小类
join
(select 大类, 小类, sum(出货金额) as 年出货金额 from Table where 出货日期 between '2005-01-01' and '2005-12- 31' group by 大类, 小类) as c
on a.大类=c.大类 and a.小类=c.小类
order by a.大类,a.小类
谢谢ourlin(学编程的会计师)
sum(进货金额)as 月进货金额 ,sum(出货金额)as 月出货金额 from Table
where 进货日期 (between '2005-01-01' and '2005-01-31') or (出货日期 between '2005-01-01' and '2005-01-31') group by 大类,小类) as a