Sql问题:
表A:
Name orderid
Wc 1
Wh 2
Zq 3
Wyc 4
Dd 5
Kk 6
表B:
name date
wc 051231
wc 060101
wyc 051231
wyc 060101
dd 060101
zq 051231
zq 060101要得到的结果是:
name date orderid
Wc 060101 1
Wh 2
Zq 060101 3
Wyc 060101 4
Dd 060101 5
Kk 6也就是说,sql查询出的要符合:
表A中的name要全部显示,并且是要按1~6排序
时间只是显示060101
表A:
Name orderid
Wc 1
Wh 2
Zq 3
Wyc 4
Dd 5
Kk 6
表B:
name date
wc 051231
wc 060101
wyc 051231
wyc 060101
dd 060101
zq 051231
zq 060101要得到的结果是:
name date orderid
Wc 060101 1
Wh 2
Zq 060101 3
Wyc 060101 4
Dd 060101 5
Kk 6也就是说,sql查询出的要符合:
表A中的name要全部显示,并且是要按1~6排序
时间只是显示060101
from a
left join b on a.name = b.name
order by a.orderid
from a,
(select max(b.date),b.name from b group by b.name) b1
where a.name = b.name(+)
order by a.orderid
select a.name,b1.date,a.orderid
from a,
(select max(b.date),b.name from b group by b.name) b1
where a.name = b1.name(+)
order by a.orderid
----- ----------
wc 1
wh 2
zq 3
wyc 4SQL> select * from testb;NAME DATE1
----- ----------
wc 16-12月-66
wc 17-12月-66
wh 16-12月-66SQL> select testa.*,m.date1 from (select * from testb
2 where testb.date1=to_date(19661216,'yyyymmdd'))m,testa
3 where testa.name=m.name(+) order by testa.orderid;NAME ORDERID DATE1
----- ---------- ----------
wc 1 16-12月-66
wh 2 16-12月-66
zq 3
wyc 4
(select name,date from tableB
where date = 060101) B
where a.name = b.name(+)
order by a.orderid