|
7#
楼主 |
发表于 2012-7-23 22:26:54
|
只看该作者
本帖最后由 zrj898 于 2012-7-23 22:37 编辑
用了3个子查询把相关字段都链接起来,请看以下:
SELECT a.每周, a.周炮数, a.星期一, a.星期二, a.星期三, a.星期四, a.星期五, a.星期六, a.星期日, b.测量公里数, b.测量点数, c.复测点数
FROM 每周炮次统计 AS a, (SELECT DatePart('ww',a.观测时间) AS 每周, sum(d.点距)/1000 AS 测量公里数, count(a.点号) AS 测量点数 FROM (SELECT a.点号, a.类型, d.点距, a.观测时间 FROM 测量成果表 AS a, 测线设计数据 AS b, 设计物理点列表 AS c, [线(束)-物理点关系表] AS d, 项目 AS e WHERE a.项目ID=e.ID And a.ID=b.成果数据ID And b.重新设计ID=c.ID And c.关系表ID=d.ID And e.当前选择<>False And a.点来源=2) AS a WHERE a.类型 in ('S','G') GROUP BY DatePart('ww',a.观测时间) ORDER BY DatePart('ww',a.观测时间)) AS b, (SELECT DatePart('ww',a.观测时间) AS 每周, count(a.点号) AS 复测点数 FROM (SELECT a.点号, a.观测时间 FROM 复测点表 AS a, 项目 AS b WHERE a.项目ID=b.ID And b.当前选择<>False) AS a GROUP BY DatePart('ww',a.观测时间) ORDER BY DatePart('ww',a.观测时间)) AS c
WHERE (((a.每周)=.[每周] And (a.每周)=[c].[每周]));
每周炮次统计另外建了个查询,不能像另几个查询用括号括起来:
TRANSFORM Count(a.点号) AS 炮数
SELECT DatePart('ww',a.观测时间) AS 每周, Count(a.点号) AS 周炮数
FROM (SELECT a.点号, a.类型, a.观测时间 FROM 测量成果表 AS a, 测线设计数据 AS b, 项目 AS e WHERE a.项目ID=e.ID And a.ID=b.成果数据ID And e.当前选择<>False And a.点来源=2) AS a
WHERE a.类型='S'
GROUP BY DatePart('ww',a.观测时间)
ORDER BY DatePart('ww',a.观测时间), WeekdayName(Weekday(a.观测时间,1),False,1)
PIVOT WeekdayName(Weekday(a.观测时间,1),False,1);
可以分别建立各个查询,只是不想在左边导航栏看见太多东西,这个语句放在窗体的数据源里。 |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|