两个表请看 "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同时使用,请大家帮忙
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同时使用,请大家帮忙
解决方案 »
- Oracle SQL问题distinct connect by
- 求高手帮忙看看 这个格式的查询
- 请教高手!mysql数据库转移到oracle数据库,通过odbc,但是查询处的汉字都是乱麻!
- update
- ==== oracle奇怪现象,一个简单的update语句不能执行。 ====
- 菜鸟 关于Oracle 异库表调用的问题!
- 为什么这么慢
- 存储过程的执行结果有问题
- ORACLE9I在登录SQLPlus Worksheet时输入用户,密码,服务名。我想问的是服务名每次登录都得手工输入吗?没有选项的吗?
- csdn 怎么老是服务器繁忙?现在都夜间快四点了,竟然还是这样!
- 跪求ORACLE学习群~~
- 关于触发器的调试问题
from (SELECT DISTINCT NAME FROM persons ) a,
xueke b
where a.name = b.name
order by B.shunxu
FROM xueke a
WHERE EXISTS (SELECT '*'
FROM persons b
WHERE b.NAME = a.NAME)
union
select * from (select a.name,b.kemu from persons a,xueke b where a.banji='02' and a.name =b.name order by a.shenxu) from b不知道这种语句执行效率怎么样,一次读几万条有没有问题
FROM xueke a
WHERE EXISTS (SELECT '*'
FROM persons b
WHERE b.NAME = a.NAME)shunxu在第二个表是没有的,不好意思
(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;
select a.name,b.kemu from persons a,xueke b where a.name = b.name
and b.kemu like a.banji||'%' order by shunxu
regexp_like(Colname,'^[A|B]')
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-yingyu
yiyang 01-shuxue
yiyang 01-yingyu
lili 01-yuwenSQL>
on a.name=b.name
希望能和大家一起探讨oracle各方面的问提
QQ群号:54775466
QQ群号:54775466
期待你的加人
积极讨论者 爱好者进
本群欢迎您的到来。
select a.name,b.kemu
from persons a,xueke b
where a.name = b.name and b.kemu LIKE a.banji||'%'
order by b.shunxu;
对于你说的UNION和ORDER BY不能同时使用,那么你包一层就可以了:
SELECT name,kemu FROM(
select a.name,b.kemu,b.shunxu
from persons a,xueke b
where a.name = b.name and b.kemu
UNION
SELECT null,null FROM persons WHERE 1=2)
ORDER BY shunxu;如果要用DISTINCT:
SELECT a.name,b.kemu
FROM (SELECT DISTINCT name FROM persons) a,xueke b
WHERE a.name=b.name
ORDER BY b.shunxu
使用UNION的SQL稍微修改下,有点点问题:
SELECT name,kemu FROM(
select a.name,b.kemu,b.shunxu
from persons a,xueke b
where a.name = b.name and b.kemu
UNION
SELECT null,null,null FROM persons WHERE 1=2)
ORDER BY shunxu;
from (SELECT DISTINCT NAME FROM persons ) a,
xueke b
where a.name = b.name
order by B.shunxu