1. 爱易网页
  2. MSSQL教程
  3. 无法在查询时限定日期,该怎么解决

无法在查询时限定日期,该怎么解决

日期：2014-05-18　 浏览次数：20540 次

无法在查询时限定日期
代码如下：
SQL code
use sh2
select *,(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 sh2
select *,
(convert(varchar,(cast(
(convert(decimal,pg1)*3+co

相关资料更多>

  1. 怎么合并表
  2. 数据库记要查找难题。
  3. SQL 2012系统管理方面有关问题，内存及客户资源管理
  4. 数据库备份出现异常
  5. 联合查询两张表如何做,来看看
  6. 请教，什么是“实例”啊
  7. 哪位高手帮小弟我分析一上死锁日志
  8. SqlServer 2008 R2 怎么实现数据双向同步
  9. xp能否安装SQL2005啊解决方案