T1
NAME(司机名字) TIME(使用时间) CAR_ID(车号)
小明 2007-11-24 A
小张 2008-01-11 B
小明 2008-02-15 A
小李 2008-03-03 A
小红 2008-03-21 A
T2
车号 时间 油
A 2007-11-29 0.5
A 2007-12-21 0.2
B 2008-03-12 0.6
A 2008-01-15 0.8
B 2008-04-09 0.3
A 2008-03-13 0.8
A 2008-03-22 0.5
A 2008-04-02 0.4
A 2008-04-11 0.4结果:
小明 2007-11-24 A 1.5
小张 2008-01-11 B 0.9
小明 2008-02-15 A 0.0
小李 2008-03-03 A 0.8
小红 2008-03-21 A 1.3
请高手赐教...
NAME(司机名字) TIME(使用时间) CAR_ID(车号)
小明 2007-11-24 A
小张 2008-01-11 B
小明 2008-02-15 A
小李 2008-03-03 A
小红 2008-03-21 A
T2
车号 时间 油
A 2007-11-29 0.5
A 2007-12-21 0.2
B 2008-03-12 0.6
A 2008-01-15 0.8
B 2008-04-09 0.3
A 2008-03-13 0.8
A 2008-03-22 0.5
A 2008-04-02 0.4
A 2008-04-11 0.4结果:
小明 2007-11-24 A 1.5
小张 2008-01-11 B 0.9
小明 2008-02-15 A 0.0
小李 2008-03-03 A 0.8
小红 2008-03-21 A 1.3
请高手赐教...
然后关联t2表,找出相应时间段的sum油
我想下SQL
同一个CAR_ID不会有同一个时间...所以同一个CAR_ID的所有记录(N条)就有N-1个时间段...
这个时间段就在T2里面求和.
其实T1表里在加个结束的时间字段就很好做了....但实际情况就是这样...没有办法..
比如
T1
A 2008-2-2
A 2008-3-4
....
改成
T1
A 2008-2-2 2008-3-4
A 2008-3-4 ....
....
就可以把2008-2-2和2008-3-4这个时间段在T2表里查询,得到结果.
(select t1.* lead(time,1,0) over (partition by car_id order by time) next_time
from t1) a,
t2
where a.car_id=t2.card_id
and t2.time between a.time and a.next_time
group by a.name,a.time我去做下测试
lead(to_date(TIME,'yyyy-mm-dd'),1,null) over(PARTITION BY car_id ORDER BY to_date(TIME,'yyyy-mm-dd')) AS END
FROM t1
ORDER BY car_id,to_date(TIME,'yyyy-mm-dd')
小明 2007-11-24 A 1.5 1.5为 A再一次出现在T1表的时间即
T1
小明 2008-02-15 A
2007-11-24~ 2008-02-15 内 A在T2表所加的油量。
WITH t1 AS
(SELECT '小明' NAME, '2007-11-24' TIME, 'A' car_id
FROM DUAL
UNION ALL
SELECT '小张', '2008-01-11', 'B '
FROM DUAL
UNION ALL
SELECT '小明', '2008-02-15', 'A '
FROM DUAL
UNION ALL
SELECT '小李', '2008-03-03', 'A'
FROM DUAL
UNION ALL
SELECT '小红', '2008-03-21', 'A'
FROM DUAL),
t2 AS
(SELECT 'A' car_id, '2007-11-29' TIME, 0.5 oil
FROM DUAL
UNION ALL
SELECT 'A', '2007-12-21', 0.2
FROM DUAL
UNION ALL
SELECT 'B', '2008-03-12', 0.6
FROM DUAL
UNION ALL
SELECT 'A', '2008-01-15', 0.8
FROM DUAL
UNION ALL
SELECT 'B', '2008-04-09', 0.3
FROM DUAL
UNION ALL
SELECT 'A', '2008-03-13', 0.8
FROM DUAL
UNION ALL
SELECT 'A ', '2008-03-22', 0.5
FROM DUAL
UNION ALL
SELECT 'A', '2008-04-02', 0.4
FROM DUAL
UNION ALL
SELECT 'A', '2008-04-11', 0.4
FROM DUAL)
select t1.name,t1.time,nvl(b.oil,0) oil
from(
SELECT a.NAME, a.TIME, SUM (oil) oil
FROM (SELECT t1.*,
decode(LEAD (TIME, 1, 0) OVER (PARTITION BY car_id ORDER BY TIME),'0',to_char(sysdate,'yyyymmdd'),LEAD (TIME, 1, 0) OVER (PARTITION BY car_id ORDER BY TIME))
next_time
FROM t1) a,
t2
WHERE a.car_id = t2.car_id AND t2.TIME BETWEEN a.TIME AND a.next_time
GROUP BY a.NAME, a.TIME) b
,t1
where t1.name=b.name(+) and t1.time=b.time(+)
order by 2
出来的数据库好像和你的不一样噢Row# NAME TIME OIL1 小明 2007-11-24 1.5
2 小张 2008-01-11 0
3 小明 2008-02-15 0.5
4 小李 2008-03-03 0.8
5 小红 2008-03-21 0.8
刚才查了下..LEAD...OVER...统计连接..
只要知道这个...其他的有办法解决....其他都是简单的过滤条件....不烦大虾费心了..
/* Formatted on 2008/04/24 16:15 (Formatter Plus v4.8.8) */
WITH t1 AS
(SELECT '小明' NAME, '2007-11-24' TIME, 'A' car_id
FROM DUAL
UNION ALL
SELECT '小张', '2008-01-11', 'B'
FROM DUAL
UNION ALL
SELECT '小明', '2008-02-15', 'A'
FROM DUAL
UNION ALL
SELECT '小李', '2008-03-03', 'A'
FROM DUAL
UNION ALL
SELECT '小红', '2008-03-21', 'A'
FROM DUAL),
t2 AS
(SELECT 'A' car_id, '2007-11-29' TIME, 0.5 oil
FROM DUAL
UNION ALL
SELECT 'A', '2007-12-21', 0.2
FROM DUAL
UNION ALL
SELECT 'B', '2008-03-12', 0.6
FROM DUAL
UNION ALL
SELECT 'A', '2008-01-15', 0.8
FROM DUAL
UNION ALL
SELECT 'B', '2008-04-09', 0.3
FROM DUAL
UNION ALL
SELECT 'A', '2008-03-13', 0.8
FROM DUAL
UNION ALL
SELECT 'A', '2008-03-22', 0.5
FROM DUAL
UNION ALL
SELECT 'A', '2008-04-02', 0.4
FROM DUAL
UNION ALL
SELECT 'A', '2008-04-11', 0.4
FROM DUAL)
select t1.name,t1.time,t1.car_id,nvl(b.oil,0) oil
from(
SELECT a.NAME, a.TIME,a.car_id, SUM (oil) oil
FROM (SELECT t1.*,
decode(LEAD (TIME, 1, 0) OVER (PARTITION BY car_id ORDER BY TIME),'0',to_char(sysdate,'yyyy-mm-dd'),LEAD (TIME, 1, 0) OVER (PARTITION BY car_id ORDER BY TIME))
next_time
FROM t1) a,
t2
WHERE a.car_id = t2.car_id AND t2.TIME>= a.TIME AND t2.TIME<=a.next_time
GROUP BY a.NAME, a.TIME,a.car_id) b
,t1
where t1.name=b.name(+) and t1.time=b.time(+)
order by 2 前面测试数据里的b多了空格,出来结果不对了
Row# NAME TIME CAR_ID OIL1 小明 2007-11-24 A 1.5
2 小张 2008-01-11 B 0.9
3 小明 2008-02-15 A 0
4 小李 2008-03-03 A 0.8
5 小红 2008-03-21 A 1.3
我这只是个例子,实际情况的表比这要多2个,而且字段也复杂...主要是刚才那个地方卡住了....
再次谢谢你.
如果不介意
帮我讲下
lead(a,1,null) over (partition by b order by a)
的语法结构可以吗?
lead(a,1,null) a字段拿出来
over(order by a) 用a来排序
partition by b 这个就不理解,是以b来分组吗?
可不可以partition by b,c,...