代码如下：
- SQL code
use sh2select *,(convert(varchar,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))))+'%')as myd1,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))) as myd,(convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl,(convert(varchar,(convert(decimal(10,2),round(yuqi*1.00/zs,2))*100))+'%') as yql from (select a.serviceid,a.xm,a.zu,count(b.id) as zs,(select name from zu where id=a.zu) as name , (select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc, (select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, (select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk,( select count(*) from wenti where service=a.serviceid and jhwctime<date7 and typeid<>6 and typeid<>5) as yuqi,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6 from service as a left join wenti as b on a.serviceid=b.service where b.id>0 and b.adddate between '2012-3-1' and'2012-4-9'group by a.serviceid,a.xm,a.zu ) as table1 order by zu, myd desc
现在的问题是 查询时没有能显示查询中的dfk,ywc,clz这些字段的在3月1日和4月9日的记录，还是显示的是全部记录
只根据日期限定到了查询中的字段zs 的个数，然后我试着这样写
- SQL code
(select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid and b.adddate between '2012-3-1' and'2012-4-9') as ywc,
报错 选择列表中的列 'wenti.adddate' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
该怎么写才能显示在特定日期内的记录呢？
------解决方案--------------------
建议楼主给测试数据和实现的目的。让大家写写，感觉你这个语句有点复杂了。
------解决方案--------------------
内部查询语句里，还有子查询语句
- SQL code
(select name from zu where id=a.zu) as name , (select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc, (select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, (select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk,( select count(*) from wenti where service=a.serviceid and jhwctime<date7 and typeid<>6 and typeid<>5) as yuqi,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6
------解决方案--------------------
你的写法效率太低了，这样加上条件
- SQL code
use sh2select *,(convert(varchar,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))))+'%')as myd1,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))) as myd,(convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl,(convert(varchar,(convert(decimal(10,2),round(yuqi*1.00/zs,2))*100))+'%') as yql from ( select a.serviceid, a.xm, a.zu, count(b.id) as zs, c.name , sum(case when b.typeid=4 then 1 else 0 end)as ywc, sum(case when b.typeid not in(4,7) then 1 else 0 end)as clz, sum(case when b.typeid=7 then 1 else 0 end)as dfk, sum(case when b.typeid not in(5,6) and jhwctime<date7 then 1 else 0 end) as yuqi, sum(case when b.typeid=4 and pg=1 then 1 else 0 end)as pg1, sum(case when b.typeid=4 and pg=2 then 1 else 0 end)as pg2, sum(case when b.typeid=4 and pg=3 then 1 else 0 end)as pg3, sum(case when b.typeid=4 and pg=4 then 1 else 0 end)as pg4, sum(case when b.typeid=4 and pg=5 then 1 else 0 end)as pg5, sum(case when b.typeid=4 and pg=6 then 1 else 0 end)as pg6 from service as a left join wenti as b on a.serviceid=b.service and and b.adddate between '2012-3-1' and'2012-4-9' --加上条件 left join zu as c on c.id=a.zu group by a.serviceid, a.xm, a.zu, c.name) as table1 order by zu, myd desc