表A
ps_id ps_name
1 上海
2 北京
3 广东
4 浙江
表Bps_id time tlevel
1 2007-2-2 23
1 2007-3-12 34
1 2007-4-21 11
.....
2 2007-3-21 37
2 2007-4-1 18
2 2007-2-23 21
.....求出每个省份在最近一天的tlevel值。然后根据tlevel值排序,取最大的前五名?谢谢。
ps_id ps_name
1 上海
2 北京
3 广东
4 浙江
表Bps_id time tlevel
1 2007-2-2 23
1 2007-3-12 34
1 2007-4-21 11
.....
2 2007-3-21 37
2 2007-4-1 18
2 2007-2-23 21
.....求出每个省份在最近一天的tlevel值。然后根据tlevel值排序,取最大的前五名?谢谢。
from A,B,
(select ps_id,max(time) time from B
group by ps_id) B_1
where A.ps_id=B.ps_id
and B.ps_id=B_1.ps_id
and B.time=B_1.time
(SELECT a.ps_name, b.tlevel
FROM table_a a, table_b b, (SELECT ps_id, MAX(time) time FROM table_b GROUP BY ps_id) t
WHERE a.ps_id = b.ps_id
AND b.time = t.time
AND b.ps_id = t.ps_id
ORDER BY tlevel desc
)
WHERE rownum <= 5;
(SELECT a.ps_name ps_name, b.tlevel tlevel row_number() over(partition by b.ps_id order by b.tlevel ) num
FROM table_a a, table_b b,
WHERE a.ps_id = b.ps_id
)
WHERE num <= 5;
(SELECT a.ps_name ps_name, b.tlevel tlevel, row_number() over(partition by b.ps_id order by b.tlevel ) num
FROM table_a a, table_b b,
WHERE a.ps_id = b.ps_id
)
WHERE num <= 5;
少了个逗号
(
select a.ps_id,a.time,a.tlevel
from
angle.B a,
(
select ps_id,max(time) time
from angle.B
group by ps_id
) b
where
a.ps_id=b.ps_id
and
a.time=b.time
)
where rownum<6;
select 1,'上海'
insert a (ps_id,ps_name)
select 2,'北京'
insert a (ps_id,ps_name)
select 3,'广东'
insert a (ps_id,ps_name)
select 4,'浙江'insert b(ps_id,time,tlevel)
select 1, '2007-2-2', '23'
insert b(ps_id,time,tlevel)
select 1, '2007-3-12', '34'
insert b(ps_id,time,tlevel)
select 1, '2007-4-21', '11'
insert b(ps_id,time,tlevel)
select 2, '2007-3-21', '37'
insert b(ps_id,time,tlevel)
select 2, '2007-4-1', '18'
insert b(ps_id,time,tlevel)
select 2, '2007-2-23', '21'
select top 5 a.ps_name,c.time,b.tlevel
from a,b,
(select b.ps_id ,max(b.time) as time
from b
group by b.ps_id) c
where a.ps_id = c.ps_id and
b.ps_id = c.ps_id and
c.time = b.time=========
sql server 下通过
FROM (
SELECT PS_ID,TIME,TLEVEL, ROW_NUMBER() OVER (ORDER BY TLEVEL DESC) AS RNUM
FROM (
SELECT PS_ID,TIME,TLEVEL,
ROW_NUMBER() OVER (PARTITION BY PS_ID ORDER BY TIME DESC) AS SEQ
FROM 表B
)
WHERE SEQ = 1
) B
INNER JOIN 表A A ON A.PS_ID = B.PS_ID AND B.RNUM < 6
FROM (
SELECT PS_ID,TIME,TLEVEL,
ROW_NUMBER() OVER (PARTITION BY PS_ID ORDER BY TIME DESC) AS SEQ
FROM (
select a.ps_name,b.ps_id,b.time,b.tlevel
from 表A a,表B b where a.ps_id=b.ps_id
)
where seq=1
)
order by tlevel
FROM (
SELECT PS_ID,TIME,TLEVEL,
ROW_NUMBER() OVER (PARTITION BY PS_ID ORDER BY TIME DESC) AS SEQ
FROM (
select a.ps_name,b.ps_id,b.time,b.tlevel
from 表A a,表B b where a.ps_id=b.ps_id
)
where seq=1
)
order by tlevel desc
ps_id ps_name
1 上海
2 北京
3 广东
4 浙江
tb_Bps_id time tlevel
1 2007-2-2 23
1 2007-3-12 34
1 2007-4-21 11
.....
2 2007-3-21 37
2 2007-4-1 18
2 2007-2-23 21
.....
with Tab_test1 as
(select a.ps_id, a.name,b.time b.tlevel from tb_A as a, tb_B as b where a.ps_id=b.ps_id)
whit Tab_test2 as
(select * from Tab_test
where id in (select id from (select id,max(time) from table_b) group by id as tt)
and
time in ((select id from (select id,max(time) from table_b) group by id as ttt)
)select top 5 * from Tab_test2 order by tlevel