通过适当的时期
问题描述:
连接表PostgreSQL数据库中我有2个表像这样的:通过适当的时期
表项目:
item_id item save_date (date dd/mm/yy)
----------------------------------------------
1 car 01/12/11
2 wheel 10/12/11
3 screen 11/12/11
4 table 15/12/11
表时间:
period_id period_name start (date dd/mm/yy)
------------------------------------------------
1 period1 05/12/11
2 period2 09/12/11
3 period3 12/12/11
所以我想获得一个连接表格添加到表格项目中最适合的时期(如果在匹配上)。如果项目的save_date在该期间的开始日期之后,则期间将匹配。
例如,对于“汽车”的日期为01/12/2011,所以我去了台期间在那里我可以看到各个时期在该日期之后开始,所以我必须将其设置为null。如果我拿“屏”的日期是11/12/11所以我应该采取间隔2期间,因为它是相匹配的最新产品。
所以连接表将导致:
item_id item save_date period_name
----------------------------------------------
1 car 01/12/11 null
2 wheel 10/12/11 period2
3 screen 11/12/11 period2
4 table 15/12/11 period3
什么是做这个连接的最佳方式?
谢谢
答
SET search_path='tmp';
DROP TABLE items CASCADE;
CREATE TABLE items
(item_id INTEGER NOT NULL PRIMARY KEY
, item VARCHAR
, save_date date NOT NULL
);
INSERT INTO items(item_id,item,save_date) VALUES
(1, 'car', '2011-12-01')
,(2, 'wheel', '2011-12-10')
,(3, 'screen', '2011-12-11')
,(4, 'table', '2011-12-15')
;
DROP TABLE periods CASCADE;
CREATE TABLE periods
(period_id INTEGER NOT NULL PRIMARY KEY
, period_name VARCHAR
, start_date date NOT NULL
);
INSERT INTO periods(period_id,period_name,start_date) VALUES
(1, 'period1', '2011-12-05')
,(2, 'period2', '2011-12-09')
,(3, 'period3', '2011-12-12')
;
-- self-join to find the next interval
WITH pe AS (
SELECT p0.period_id,p0.period_name,p0.start_date
, p1.start_date AS end_date
FROM periods p0
-- must be a left join; because the most recent interval is still open
-- (has no successor)
LEFT JOIN periods p1 ON p1.start_date > p0.start_date
WHERE NOT EXISTS (
SELECT * FROM periods px
WHERE px.start_date > p0.start_date
AND px.start_date < p1.start_date
)
)
SELECT it.item_id
, it.item
, it.save_date
, pe.period_id
, pe.period_name
, pe.start_date
, pe.end_date
FROM items it
LEFT JOIN pe
ON it.save_date >= pe.start_date
AND (it.save_date < pe.end_date OR pe.end_date IS NULL)
;
结果:
item_id | item | save_date | period_id | period_name | start_date | end_date
---------+--------+------------+-----------+-------------+------------+------------
1 | car | 2011-12-01 | | | |
2 | wheel | 2011-12-10 | 2 | period2 | 2011-12-09 | 2011-12-12
3 | screen | 2011-12-11 | 2 | period2 | 2011-12-09 | 2011-12-12
4 | table | 2011-12-15 | 3 | period3 | 2011-12-12 |
(4 rows)
答
交叉应用或共同相关的子查询将工作。
子查询方法为跨应用仅适用于SQL Server 2005的(未经测试)
SELECT Item_Id,
Item,
Save_Date,
(
SELECT MAX(Period_Name)
FROM Periods
WHERE Start = (
SELECT MAX(start) MaxStart
FROM periods P
WHERE I.Save_Date > P.start)
) AS Period_Name
FROM ITEMS I
Period3应该是仍然有效(直到永恒),因为没有晚期的日期存在? – wildplasser 2011-12-15 22:43:23