求一sql语句
两个表

请看 "Persons" 表：
name banji shunxu
yiyang 01 1
yiyang 02 1
lili 01 2

接下来请看 "xueke" 表：

name kemu shunxu
yiyang 01-yingyu 1
yiyang 01-shuxue 1
yiyang 02-yingyu 1
lili 01-yuwen 2

第一个表每个学生有两条记录，也可能一条，第二个表就有多个记录与之对应
我现在想取出第一个表中的姓名，第二个表中所有的记录，如果用join就double记录了。

select a.name,b.kemu from persons a,xueke b where a.name = b.name order by shunxu
这样记录就双倍。用union的话不能和order by同时使用，请大家帮忙

[解决办法]
order by可以放到最后或者加一个子查询啊..
[解决办法]
select a.name,b.kemu,B.SHUNXU
from (SELECT DISTINCT NAME FROM persons ) a,
xueke b
where a.name = b.name
order by B.shunxu
[解决办法]
xueke表里本身就有name了，应该只是对照看表persons里是否有记录吧..

SQL code

SELECT NAME, kemu, shunxu  FROM xueke a WHERE EXISTS (SELECT '*'                 FROM persons b                WHERE b.NAME = a.NAME)
[解决办法]
WITH persons AS
  (SELECT 'yiyang' NAME,'01' banji,1 shunxu FROM dual
   UNION ALL
   SELECT 'yiyang' NAME,'02' banji,1 shunxu FROM dual
   UNION ALL
   SELECT 'lili' NAME,'01' banji,2 shunxu FROM dual),
   xueke AS
   (SELECT 'yiyang' name,'01-yingyu' kemu FROM dual
    UNION ALL
    SELECT 'yiyang' name,'01-shuxue' kemu FROM dual
    UNION ALL
    SELECT 'yiyang' name,'02-yingyu' kemu FROM dual
    UNION ALL
    SELECT 'lili' name,'01-yuwen' kemu FROM dual)
SELECT a.NAME,b.kemu FROM  
  (SELECT DISTINCT NAME,shunxu FROM persons ORDER BY shunxu) a,
  xueke b
where a.name = b.NAME
ORDER BY a.shunxu;
[解决办法]
不同班级？distinct?不太明白
select a.name,b.kemu from persons a,xueke b where a.name = b.name
 and b.kemu like a.banji||'%' order by shunxu
[解决办法]
10g以上用正则
regexp_like(Colname,'^[A|B]')
[解决办法]
10g 正则 regexp_like(table_name,'^A|^B')

[解决办法]
SQL code
SQL> edi已写入 file afiedt.buf  1* select a.name,kemu from xueke a where exists(select 1 from Persons b where a.name=b.name)SQL> /NAME     KEMU-------- --------------------yiyang   02-yingyuyiyang   01-shuxueyiyang   01-yingyulili     01-yuwenSQL>
[解决办法]
为什么没有引入学号字段呢？用名字重复肯定很多，而且现有模式sql执行效率不会很高
[解决办法]
select b.* from xueke b inner join (select distinct name from persons) a
on a.name=b.name
[解决办法]
探讨

两个表

请看 "Persons" 表：
name    banji  shunxu
yiyang   01     1
yiyang   02     1
lili     01     2
  
接下来请看 "xueke" 表：

name    kemu       shunxu
yiyang  01-yingyu    1
yiyang  01-shuxue    ……