现在有一个需求,要查出一段时间的数据.时间不限制
数据库表结构: 表若干,有相关联字段.每张表存储一个很大的信息记录的一个部分,关联字段内容在所在表里面可有重复.每张表里面按当月统计~
比如说~从2008-7-1日起建表~那日期字段2008-7-9日即为从08年7月1日起到7月9号的一个统计之和数字.字段日期7月22日就是7月1日到22日的统计和.而字段为2008-8-7,就是8月1日到8月7号的统计和了~ 现在要做的就是要存储过程实现这样一个表的合成. 我做了一个单表~查询日期是2008-9-1 ~ 2008-9~3 SELECT derivedtbl_2.cn_seg1, derivedtbl_2.cn_seg2, derivedtbl_2.cn_total_zw, derivedtbl_2.cn_total_rs, derivedtbl_2.cn_date, derivedtbl_2.cn_fno,
derivedtbl_2.cn_company, derivedtbl_1.ct_kgl, derivedtbl_1.ct_zgl, derivedtbl_1.ct_sprilv, derivedtbl_1.ct_tprilv, derivedtbl_1.ct_company,
derivedtbl_1.ct_date
FROM (SELECT cn_seg1, cn_seg2, cn_total_zw, cn_total_rs, cn_date, cn_fno, cn_company
FROM Center_number2
WHERE (cn_date = CONVERT(DATETIME, '2008-09-03 00:00:00', 102)) AND (cn_seg1 = 'pek') AND (cn_company = 'ca')) AS derivedtbl_2 INNER JOIN
(SELECT ct_fno, ct_seg1, ct_seg2, ct_kgl, ct_zgl, ct_sprilv, ct_tprilv, ct_company, ct_date
FROM Center_target2
WHERE (ct_company = 'ca') AND (ct_date = CONVERT(DATETIME, '2008-09-03 00:00:00', 102)) AND (ct_seg1 = 'pek')) AS derivedtbl_1 ON
derivedtbl_2.cn_fno = derivedtbl_1.ct_fno AND derivedtbl_2.cn_seg1 = derivedtbl_1.ct_seg1 AND derivedtbl_2.cn_seg2 = derivedtbl_1.ct_seg2
结果如下:
PEK KWL 378 22 2008-9-3 0:00:00 CA1225 CA 1792743 4560948 0.058384953906478118 0.035692805921813557 CA 2008-9-3 0:00:00
PEK XIY 378 262 2008-9-3 0:00:00 CA1225 CA 1792743 4560948 0.058384953906478118 0.035692805921813557 CA 2008-9-3 0:00:00
PEK KWL 377 324 2008-9-3 0:00:00 CA1311 CA 611388 711399 0.37745048247841545 0.32244897959183672 CA 2008-9-3 0:00:00
PEK NNG 492 385 2008-9-3 0:00:00 CA1335 CA 866250 1107000 0.36695621166737985 0.3 CA 2008-9-3 0:00:00
PEK NNG 487 448 2008-9-3 0:00:00 CA1375 CA 1008000 1095750 0.49809995644599303 0 CA 2008-9-3 0:00:00
PEK CGQ 594 479 2008-9-3 0:00:00 CA1637 CA 446907 554202 0.78016701461377869 0 CA 2008-9-3 0:00:00
PEK NNG 248 75 2008-9-3 0:00:00 CA903 CA 981000 2232000 0.15037037037037038 0 CA 2008-9-3 0:00:00
PEK SGN 248 143 2008-9-3 0:00:00 CA903 CA 981000 2232000 0.15037037037037038 0 CA 2008-9-3 0:00:00
PEK SIN 503 44 2008-9-3 0:00:00 CA957 CA 0 0 0 0 CA 2008-9-3 0:00:00
PEK XMN 503 318 2008-9-3 0:00:00 CA957 CA 0 0 0 0 CA 2008-9-3 0:00:00
但如果说是一个垮月查询,或者查询日期不是从本月1号开始,就要先算出两个表,然后相加或者减,.....
例1: 查2008-7-2 ~ 2008-10-14
就是要:7月31号-7月1号+8月31号+9月30号+10月14号....汗`
例2: 查2008-9-3 ~ 2008-9-14
就是要:9月14号减去9月2号. 80分跪求整个存储过程怎么写....
另外,你的表Center_number2和Center_target2与月份有什么关系?怎么才能体现这两个表是9月份的呢?7月,8月,10月相对应的表是什么?
derivedtbl_2.cn_company, derivedtbl_1.ct_kgl, derivedtbl_1.ct_zgl, derivedtbl_1.ct_sprilv, derivedtbl_1.ct_tprilv, derivedtbl_1.ct_company,
derivedtbl_1.ct_date
FROM (SELECT cn_seg1, cn_seg2, cn_total_zw, cn_total_rs, cn_date, cn_fno, cn_company
FROM Center_number2
WHERE (cn_date = CONVERT(DATETIME, '2008-09-03 00:00:00', 102)) AND (cn_seg1 = 'pek') AND (cn_company = 'ca')) AS derivedtbl_2 INNER JOIN
(SELECT ct_fno, ct_seg1, ct_seg2, ct_kgl, ct_zgl, ct_sprilv, ct_tprilv, ct_company, ct_date
FROM Center_target2
WHERE (ct_company = 'ca') AND (ct_date = CONVERT(DATETIME, '2008-09-03 00:00:00', 102)) AND (ct_seg1 = 'pek')) AS derivedtbl_1 ON
derivedtbl_2.cn_fno = derivedtbl_1.ct_fno AND derivedtbl_2.cn_seg1 = derivedtbl_1.ct_seg1 AND derivedtbl_2.cn_seg2 = derivedtbl_1.ct_seg2 以上怎么都查询的都是9-3号呢?
你不是要查询9-1到9-3号呢?