我有一张表,其中每个商店的销售额如下：
SQL> select * from sales;
ID ID_STORE DATE TOTAL
---------- -------- ---------- -------------------------------
1 1 2010-01-01 500.00
2 1 2010-01-02 185.00
3 1 2010-01-03 135.00
4 1 2009-01-01 165.00
5 1 2009-01-02 175.00
6 5 2010-01-01 130.00
7 5 2010-01-02 135.00
8 5 2010-01-03 130.00
9 6 2010-01-01 100.00
10 6 2010-01-02 12.00
11 6 2010-01-03 85.00
12 6 2009-01-01 135.00
13 6 2009-01-02 400.00
14 6 2009-01-07 21.00
15 6 2009-01-08 45.00
16 8 2009-01-09 123.00
17 8 2009-01-10 581.00
17 rows selected.
我需要做的是比较该表中的两个日期范围.假设我需要知道2009年1月1日至2009年1月10日之间的销售差异,再看看2010年1月1日至2010年1月10日.
我想建立一个查询,返回如下内容：
ID_STORE_A DATE_A TOTAL_A ID_STORE_B DATE_B TOTAL_B
---------- ---------- --------- ---------- ---------- -------------------
1 2010-01-01 500.00 1 2009-01-01 165.00
1 2010-01-02 185.00 1 2009-01-02 175.00
1 2010-01-03 135.00 1 NULL NULL
5 2010-01-01 130.00 5 NULL NULL
5 2010-01-02 135.00 5 NULL NULL
5 2010-01-03 130.00 5 NULL NULL
6 2010-01-01 100.00 6 2009-01-01 135.00
6 2010-01-02 12.00 6 2009-01-02 400.00
6 2010-01-03 85.00 6 NULL NULL
6 NULL NULL 6 2009-01-07 21.00
6 NULL NULL 6 2009-01-08 45.00
6 NULL NULL 8 2009-01-09 123.00
6 NULL NULL 8 2009-01-10 581.00
因此,即使在一个或另一个范围内没有销售,它也应该用NULL填充空白.
到目前为止,我已经提出了这个快速查询,但是我有时从销售到销售2的“日期”在每一行中都是不同的：
SELECT sales.*, sales2.*
FROM sales
LEFT JOIN sales AS sales2
ON (sales.id_store=sales2.id_store)
WHERE sales.date >= '2010-01-01'
AND sales.date <= '2010-01-10'
AND sales2.date >= '2009-01-01'
AND sales2.date <= '2009-01-10'
ORDER BY sales.id_store ASC, sales.date ASC, sales2.date ASC
我想念什么？
解决方法:
通过使用IBM Informix Dynamic Server 11.50.FC6,我可以使用以下SQL序列来获得所需的结果：
设定
CREATE TABLE sales
(
id INTEGER NOT NULL,
id_store INTEGER NOT NULL,
date DATE NOT NULL,
total DECIMAL(10,2) NOT NULL
);
INSERT INTO sales VALUES( 1, 1, '2010-01-01', 500.00);
INSERT INTO sales VALUES( 2, 1, '2010-01-02', 185.00);
INSERT INTO sales VALUES( 3, 1, '2010-01-03', 135.00);
INSERT INTO sales VALUES( 4, 1, '2009-01-01', 165.00);
INSERT INTO sales VALUES( 5, 1, '2009-01-02', 175.00);
INSERT INTO sales VALUES( 6, 5, '2010-01-01', 130.00);
INSERT INTO sales VALUES( 7, 5, '2010-01-02', 135.00);
INSERT INTO sales VALUES( 8, 5, '2010-01-03', 130.00);
INSERT INTO sales VALUES( 9, 6, '2010-01-01', 100.00);
INSERT INTO sales VALUES(10, 6, '2010-01-02', 12.00);
INSERT INTO sales VALUES(11, 6, '2010-01-03', 85.00);
INSERT INTO sales VALUES(12, 6, '2009-01-01', 135.00);
INSERT INTO sales VALUES(13, 6, '2009-01-02', 400.00);
INSERT INTO sales VALUES(14, 6, '2009-01-07', 21.00);
INSERT INTO sales VALUES(15, 6, '2009-01-08', 45.00);
INSERT INTO sales VALUES(16, 8, '2009-01-09', 123.00);
INSERT INTO sales VALUES(17, 8, '2009-01-10', 581.00);
询问
SELECT *
FROM (SELECT s1.id AS s1id,
NVL(s1.id_store, s2.id_store) AS s1store,
NVL(s1.date, MDY(MONTH(s2.date), DAY(s2.date),
YEAR(s2.date)+1)) AS s1date,
s1.total AS s1total,
s2.id AS s2id,
NVL(s2.id_store, s1.id_store) AS s2store,
NVL(s2.date, MDY(MONTH(s1.date), DAY(s1.date),
YEAR(s1.date)-1)) AS s2date,
s2.total AS s2total
FROM sales AS s1 FULL JOIN sales AS s2
ON s1.id_store = s2.id_store
AND s1.date BETWEEN '2010-01-01' AND '2010-01-10'
AND s2.date BETWEEN '2009-01-01' AND '2009-01-10'
AND DAY(s1.date) = DAY(s2.date)
AND MONTH(s1.date) = MONTH(s2.date)
) AS s3
WHERE s1_date BETWEEN '2010-01-01' AND '2010-01-10'
AND s2_date BETWEEN '2009-01-01' AND '2009-01-10'
ORDER BY s1_id_store ASC, s1_date ASC;
结果
s1id s1store s1date s1total s2id s2store s2date s2total
1 1 2010-01-01 500.00 4 1 2009-01-01 165.00
2 1 2010-01-02 185.00 5 1 2009-01-02 175.00
3 1 2010-01-03 135.00 1 2009-01-03
6 5 2010-01-01 130.00 5 2009-01-01
7 5 2010-01-02 135.00 5 2009-01-02
8 5 2010-01-03 130.00 5 2009-01-03
9 6 2010-01-01 100.00 12 6 2009-01-01 135.00
10 6 2010-01-02 12.00 13 6 2009-01-02 400.00
11 6 2010-01-03 85.00 6 2009-01-03
6 2010-01-07 14 6 2009-01-07 21.00
6 2010-01-08 15 6 2009-01-08 45.00
8 2010-01-09 16 8 2009-01-09 123.00
8 2010-01-10 17 8 2009-01-10 581.00
说明
为了获得这种“正确”,需要进行大量的实验. Informix具有DATE构造函数MDY(),该函数带有三个整数参数：月,日和年(名称为助记符).它还具有三个分析函数：DAY(),MONTH()和YEAR(),它们返回date参数的日,月和年.使用FULL JOIN的内部查询为您提供的结果在左侧和右侧都为空. ON子句中的5部分标准似乎是必要的；否则,外部查询中的条件必须更加复杂和混乱-如果可以使其完全起作用.然后,外部选择中的条件可确保选择正确的数据.内部查询中NVL()表达式的一个优点是商店ID列既相同又不为null,并且两个date列都不为null,因此order by子句可以更简单-关于商店ID和任一date列.
在Informix中,也可以将日期表达式重做为：
NVL(s1.date, s2.date + 1 UNITS YEAR)
NVL(s2.date, s1.date - 1 UNITS YEAR)
实际上,使用该符号在幕后进行了多种类型转换,但是它可以为您提供相同的结果,并且额外的计算可能并不那么重要.
在Informix中等待时也会出现故障.您不能在任何2月29日前后加上或减去1年-因为第二年或下一年没有2月29日.您需要注意数据.如果不是这样,您最终可能会比较2008-02-29和2009-02-28的数据(以及比较2008-02-28和2009-02-28的数据).有一个称为“两次录入簿记”的过程,但这不是它的含义,如果“ 2008-02-29加1年”为2009-02-28,您的计算可能会感到困惑. Informix会产生一个错误；那不是很有帮助.您可能对存储过程进行了编码,可能没有返回日期与之比较的2008-02-29加1年返回NULL.
您应该能够相当轻松地使日期算术适应MySQL.其余代码无需更改.