求Sql查询语句(50分)

第1个表：monthpro<br>monthNo &nbsp; startDate &nbsp;EndDate Period <br>1 &nbsp; &nbsp; &nbsp; &nbsp; 2007-12-26 &nbsp;2008-1-25 &nbsp;1<br>2 &nbsp; &nbsp; &nbsp; &nbsp; 2008-1-26 &nbsp; 2008-2-25 &nbsp;2<br>3 &nbsp; &nbsp; &nbsp; &nbsp; 2008-2-26 &nbsp; 2008-3-25 &nbsp;3<br><br>第2个表：Sale<br>usercode &nbsp;fullname &nbsp;billdate &nbsp;qty <br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; 2008-1-22 &nbsp;10<br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; 2008-1-23 &nbsp; 5<br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; 2008-2-28 &nbsp;15<br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; 2008-3-1 &nbsp; &nbsp;1<br><br>要求得到如下结果<br><br>usercode &nbsp;fullname &nbsp; &nbsp;qty &nbsp; &nbsp;monthNo<br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; &nbsp;15 &nbsp; &nbsp; 1<br>1001 &nbsp; &nbsp; &nbsp;电话 &nbsp; &nbsp; &nbsp; &nbsp;16 &nbsp; &nbsp; 3<br>查询结果中的qty列（合计数量），monthNo列是根据表2中的billdate值来分组计算的。

select usercode,fullname,billdate,qty from Sale<br>与第一个表没有关联

用SQl得到这两个表的查询结果，楼上只是得到第2个表的结果

有点不明白LZ的意思呀!<br>结果应该直接从第二个表取出就好了啊!

select a.usercode, a.fullname, b.monthNo, sum(a.qty) as qty<br>&nbsp; from Sale a<br>&nbsp; &nbsp; inner join monthpro b <br>&nbsp; on(a.billdate between b.startDate and b.endDate)<br>group by a.usercode, a.fullname, b.monthNo<br><br>这是 SQL Server 的语法

完整的测试代码<br><br><br><br>---------------------------------------------<br>--生成 monthpro 模拟数据<br>create table #monthpro(<br>&nbsp; monthNo int,<br>&nbsp; startDate smalldatetime,<br>&nbsp; endDate smalldatetime,<br>&nbsp; Period int<br>)<br><br>insert #monthpro values(1,'2007-12-26','2008-1-25',1)<br>insert #monthpro values(2,'2008-1-26','2008-2-25',2)<br>insert #monthpro values(3,'2008-2-26','2008-3-25',3)<br><br><br>---------------------------------------------<br>--生成 Sale 模拟数据<br>create table #Sale(<br>&nbsp; usercode varchar(10),<br>&nbsp; fullname varchar(10),<br>&nbsp; billdate smalldatetime,<br>&nbsp; qty int<br>)<br><br>insert #Sale values('1001','电话','2008-1-22',10)<br>insert #Sale values('1001','电话','2008-1-23',5)<br>insert #Sale values('1001','电话','2008-2-28',15)<br>insert #Sale values('1001','电话','2008-3-1',1)<br><br>---------------------------------------------<br><br>select * from #monthpro<br>select * from #Sale<br><br><br>--输出结果<br>select a.usercode, a.fullname, b.monthNo, sum(a.qty) as qty<br>&nbsp; from #Sale a<br>&nbsp; &nbsp; inner join #monthpro b <br>&nbsp; on(a.billdate between b.startDate and b.endDate)<br>group by a.usercode, a.fullname, b.monthNo<br><br><br>drop table #monthpro<br>drop table #Sale

谢谢plenilune的指导，我改进了一下，作了如下的查询，在SQL查询器中可以成功运行，但<br>我想保存为SQL视图，在access中调用这个查询，但在SQl企业管理器中却提示：“SQL不支持Case语句构造”，如何才能保存为SQL视图以方便Access链接到SQl数据库中这个视图来查看结果数据。<br>select c.usercode,c.fullname,sum(A0801) as '0801',sum(A0802) as '0802',sum(A0803) as '0803',sum(A0804) as '0804'<br>from <br>(<br>select a.usercode, a.fullname,<br>case when b.period = '1' then sum(a.qty) else 0 end as A0801 ,<br>case when b.period = '2' then sum(a.qty) else 0 end as A0802 ,<br>case when b.period = '3' then sum(a.qty) else 0 end as A0803, &nbsp;<br>case when b.period = '4' then sum(a.qty) else 0 end as A0804<br>from 销售明细查询 a<br>&nbsp; &nbsp; inner join monthproc b <br>&nbsp; on(a.billdate between b.startDate and b.endDate)<br>group by a.usercode, a.fullname, b.monthNo,b.period<br>) c<br>group by c.usercode,c.fullname

access用IIF,例如<br>case when b.period = '1' then sum(a.qty) else 0 end as A0801 <br>改为<br>iif(b.period = '1' , sum(a.qty), 0 ) as A0801

接受答案了.