我有这样一个表 结构如下 (sql2000)
表名:table 字段: 司机 车号 出车日期 方量 运距 我想一次查询得到按公里数统计出 :(既根据给定时间范围,按公里数算出以下内容 要求一次获得)
5公里趟数 5公里方量 5公里出车数 5-10公里趟数 5-10公里方量 5-10公里车数 10公里以上趟数 10公里以上方量 10公里以上公里数以下是我用vfp的语句
select count(签发日期) as 出勤天数,司机,运输车号,sum(iif(运距<=5,1,0)) as 五公里趟数 ,sum(iif(运距<=5,运距,0.0)) as 五公里,sum(iif(运距<=5,方量,0.0)) as 五公里方量 ,sum(iif( 运距<=10 and 运距>5,1,0.0)) as 十公里趟数,sum(iif( 运距<=10 and 运距>5,运距,0.0)) as 十公里,sum(iif(运距<=10 and 运距>5,方量,0.0)) as 十公里方量;
,sum(iif( 运距<=25 and 运距>10,1,0.0)) as 二五趟数,sum(iif( 运距<=25 and 运距>10,运距,0.0)) as 二十五公里,sum(iif(运距<=25 and 运距>10,方量,0.0)) as 二五方量 ,sum(iif( 运距<=35 and 运距>25,1,0.0)) as 三五趟数,sum(iif( 运距<=35 and 运距>25,运距,0.0)) as 三十五公里,sum(iif(运距<=35 and 运距>25,方量,0.0)) as 三五方量;
,sum(iif( 运距>35,1,0.0)) as 三六趟数,sum(iif( 运距>35,运距,0.0)) as 三六公里,sum(iif( 运距>35,方量,0.0)) as 三六方量 from sjcq1 group by 司机 into dbf cctj3
有高手能转在c#中么 或者在sql2000里做视图也可以
谢谢
表名:table 字段: 司机 车号 出车日期 方量 运距 我想一次查询得到按公里数统计出 :(既根据给定时间范围,按公里数算出以下内容 要求一次获得)
5公里趟数 5公里方量 5公里出车数 5-10公里趟数 5-10公里方量 5-10公里车数 10公里以上趟数 10公里以上方量 10公里以上公里数以下是我用vfp的语句
select count(签发日期) as 出勤天数,司机,运输车号,sum(iif(运距<=5,1,0)) as 五公里趟数 ,sum(iif(运距<=5,运距,0.0)) as 五公里,sum(iif(运距<=5,方量,0.0)) as 五公里方量 ,sum(iif( 运距<=10 and 运距>5,1,0.0)) as 十公里趟数,sum(iif( 运距<=10 and 运距>5,运距,0.0)) as 十公里,sum(iif(运距<=10 and 运距>5,方量,0.0)) as 十公里方量;
,sum(iif( 运距<=25 and 运距>10,1,0.0)) as 二五趟数,sum(iif( 运距<=25 and 运距>10,运距,0.0)) as 二十五公里,sum(iif(运距<=25 and 运距>10,方量,0.0)) as 二五方量 ,sum(iif( 运距<=35 and 运距>25,1,0.0)) as 三五趟数,sum(iif( 运距<=35 and 运距>25,运距,0.0)) as 三十五公里,sum(iif(运距<=35 and 运距>25,方量,0.0)) as 三五方量;
,sum(iif( 运距>35,1,0.0)) as 三六趟数,sum(iif( 运距>35,运距,0.0)) as 三六公里,sum(iif( 运距>35,方量,0.0)) as 三六方量 from sjcq1 group by 司机 into dbf cctj3
有高手能转在c#中么 或者在sql2000里做视图也可以
谢谢
--如;
select [5公里趟数]=sum(case when 运距<5 then 1 else 0 end),
[5公里方量]=sum(case when 方量<5 then 1 else 0 end),
...
from [table]
where 出车日期 between 开始日期 and 结束日期
[5公里方量]=sum(case when 运距<5 then 方量 else 0 end),
...
from [table]
where 出车日期 between 开始日期 and 结束日期