在oracle中求一简单sql

[快跑蜗牛哥]

表a 表b
A表 主要使用到的栏位
S_id,name,sname,C_s 其中s_id 为主键
B表 主要使用的栏位
B_id,S_id,Cr_date其中B_id为主键
现在要得到表a中，所有C_s等于1的资料，
b表中Cr_date 在今年建立的资料显示在前面。
（注意不是只显示b表中有B_id的资料）
（Cr_date为建立时间，b表中的S_id显示多次）

[大明湖畔_帅锅]

发了100多个贴了 这个语句 应该是比较基础的吧...

[woof_1128]

一楼正解

[fw0124]

select b.b_id, b.s_id, b.cr_date, a.name, a.sname from b
  left join a on b.s_id=a.s_id
  where a.c_s=1
  order by b.cr_date desc

[快跑蜗牛哥]

[Quote=引用 1 楼 的回复:]

SQL code
select b.b_id, b.s_id, b.cr_date, a.name, a.sname from b
left join a on b.s_id=a.s_id
where a.c_s=1
order by b.cr_date desc
[/Quote]

不符合要求！
1.b表的资料 不需要显示
只显示a表中的所有资料
（ a.c_s=1 满足条件的显示出1条记录）
2.b表作用，只是条件
< 即 当 b.s_id=a.s_id and to_char(b.Cr_date,'yyyy')='2012'>
对应的 a.s_id 的资料显示在前面

[shiyiwan]

SELECT *
FROM a t1
WHERE c_s = 1
AND EXISTS
  (SELECT 1 FROM b WHERE b.s_id = t1.s_id AND TO_CHAR(cr_date,'yyyy') = '2012'
  );

[jwlee_313]

我写了一个： with a as (select '1' s_id, 'a' name, 'aa' sname, '1' c_s from dual union all select '2' s_id, 'a' name, 'aa' sname, '1' c_s from dual), b as (select '1' b_id, '1' s_id, date '2012-10-10' cr_date from dual union all select '2' b_id, '1' s_id, date '2011-10-10' cr_date from dual union all select '3' b_id, '2' s_id, date '2012-09-09' cr_date from dual) select t.s_id, t.name, t.sname, t.c_s, t.max_date from (select distinct a.*, max(b.cr_date) over(partition by b.s_id) max_date from a, b where a.s_id = b.s_id(+) and a.c_s = '1' order by max_date) t order by decode(extract(year from t.max_date), extract(year from sysdate), t.max_date, null)

[shuiniu0410]

select * from (select distinct a.*,row_number() over(partition by b.b_id order by b.cr_date)rn from a,b where a.s_id = b.s_id(+) and a.c_s = '1' )t where t.rn =1