复合查询SQL话语

复合查询SQL语句

select *
from customer left join orders
on customer.cust_id=orders.cust_id
????? ?如果是左连接该命令将返回join左侧表(customer)中的所有记录以及右侧表中匹配的记录；右连接则反之，返回orders中所有记录和customer的匹配记录；完全连接返回两个表中的所有记录。

customer:cust_id???? cust_name?????????

???????????????????? ?100?????? liujinzhong??????????????????
????????????????????? 101?????? liujinzhong1?????????????????
????????????????????? 102?????? liujinzhong2??????????????????
??????????????????????103?????? liujinzhong3
orders: cust_id????? cust_name??? orders_id
????????????????? ? 100??????? oraders_1??????? 1
??????????????????? 105??????? oraders_2??????? 2
?????????????????? ?106??????? oraders_3??????? 3????????????????
????????????????? ? 107??????? oraders_4??????? 4???????????????????????????????????????????????
????????????????????101??????? oraders_5??????? 5???????????????????????????????????????????????
??????????????????? 102??????? oraders_6??????? 6
以上面两张表为例
（1）select *
???? from customer a,orders b
???? where? a.cust_id=b.cust_id
结果集：
???????? cust_id???? cust_name?????????cust_id????? ? cust_name??? orders_id
????????? 100?????? liujinzhong?????????????????? 100???????? oraders_1??????? 1
????????? 101?????? liujinzhong1????????????????? 101??????? oraders_5??????? 5
????????? 102?????? liujinzhong2????????????????? 102??????? oraders_6??????? 6
（2）select *
???? from customer a left join orders b
???? on a.cust_id=b.cust_id
结果集：
cust_id???? cust_name?????????????????cust_id????? cust_name??? orders_id
? 100?????? liujinzhong?????????????????????? 100?????? ???? oraders_1??????? 1
? 101?????? liujinzhong1??????????????????? 101?????????? ?oraders_5??????? 5
??102?????? liujinzhong2????????????????????102??????????? oraders_6??????? 6
????????? 103?????? liujinzhong3?????????????---????????????????? ---------?????????? ?-
（3）select *
???? from customer a right join orders b
???? on a.cust_id=b.cust_id
结果集：
???????? cust_id???? cust_name?????????cust_id????? cust_name??? orders_id
????????? 100?????? liujinzhong?????????????????? 100??????? oraders_1??????? 1
????????? 101?????? liujinzhong1?????????????????101??????? oraders_5??????? 5
????????? 102?????? liujinzhong2????????????????? 102??????? oraders_6??????? 6
???????? ? ---?????? ------------??????????????????????? ? 105??????? oraders_2??????? 2
?????????? ---?????? ------------???????????????????????? ?106??????? oraders_3??????? 3
???????? ? ---?????? ------------????????????????????????? 107??????? oraders_4??????? 4
?（4）select *
???? from customer a full join orders b
???? on a.cust_id=b.cust_id
结果集：
???????? cust_id???? cust_name????????? cust_id????? cust_name??? orders_id
????????? 100?????? liujinzhong?????????????????? 100??????? oraders_1??????? 1
????????? 101?????? liujinzhong1????????????????? 101??????? oraders_5??????? 5
????????? 102?????? liujinzhong2????????????????? 102??????? oraders_6??????? 6
????????? 103?????? liujinzhong3????????????????? ---?????? ------------????? -?????
?????????? ?---?????? ------------??????????????????????? ?105??????? oraders_2??????? 2
?????????? ?---?????? ------------???????????????????????? ?106??????? oraders_3???????3
????????? ? ---?????? ------------????????????????????????? ?107??????? oraders_4????? 4????????????????????????????????????????????????
?????????????????????????????????????????????????

2 楼 beyondsanli 2007-12-17  

动态SQL还是动态字段？      预览我的文章   编辑   删除
关键字: Database      
在一个项目中因为要重复的从不同字段取值，其他的条件都一致，
我建了个varrry中放这些字段的名称，通过循环来取值，为什么不行？
哪个高手可以指点下呀！
create or replace procedure czk is
v_input varchar2(20):='dual';
v_par varchar2(20):='dummy';
v_output varchar2(20);
begin
execute immediate 'select '||v_par|| 'into v_output from '||v_input;
dbms_output.put_line('----------------------'||v_output);
end czk;

哪位高手可以帮助我解决，动态字段和动态表名的问题呢！

查看更多 下一篇