oracle复杂查询

复杂select语句

操作集合

Union和Union All

查询一： select message_title, message_content, send_begin_time   from ap_message am, ap_message_receive mr  where am.message_id = mr.message_id union select message_title, message_content, send_begin_time   from ap_message am, ap_message_receive mr  where am.message_id = mr.message_id    and am.message_title = '通知'  orderby send_begin_time   查询二： select message_title, message_content, send_begin_time   from ap_message am, ap_message_receive mr  where am.message_id = mr.message_id unionall select message_title, message_content, send_begin_time   from ap_message am, ap_message_receive mr  where am.message_id = mr.message_id    and am.message_title = '通知'  orderby send_begin_time

Minux:两个集合之间不同的部分

select USER_CODE, USER_NAME   from AP_USER  where department_id = 'ef5ebee66188a451e040007f0100797c' minus select USER_CODE, USER_NAME   from AP_USER  where department_id = 'ff8080814482bb2a014486cf39d7069b'

Intersect:取2个集合之间相同的部分

select USER_CODE, USER_NAME   from AP_USER  where department_id = 'ef5ebee66188a451e040007f0100797c' intersect select USER_CODE, USER_NAME   from AP_USER  where department_id = 'ff8080814482bb2a014486cf39d7069b'

子查询

在一个select语句中嵌入一个完整的select语句

select USER_NAME, USER_CODE   from AP_USER  WHERE USER_ID =        (select USER_ID FROM AP_USER WHERE USER_CODE = 'B11302246')

表连接

select au.user_name, pu.user_code   from a1_user au, ap_user pu  where au.user_code=pu.user_code

case语句

查询一：case简单使用 select am.message_title,        am.create_date,        case am.message_status          when '0' then '未发送'          when '1' then '已发送'          when '2' then '发送失败'          else '状态不明'        end   from ap_message am   查询二：使用表达式 select au.user_name,        au.user_code,        case          when au.dshk_money='2000'then           '穷人'          when au.dshk_money='3000'then           '普通人'          else           '不是人'        end   from a1_user au

强大的DECODE函数

查询一： select decode(1, 1, 2, 3) 比较1, decode(1, 2, 3, 4) 比较2   from deliver_position  101   查询二：分类 select am.message_title,        am.create_date,        decode(am.message_type, '1', '工单消息', '其他消息')   from ap_message am   查询三：多参数匹配 select am.message_title,        am.create_date,        decode(am.message_type, '1', '工单消息','2','认证消息','3','好友消息', '其他消息')   from ap_message am

group by数据分组

查询一： select count(message_title), count(message_content), message_status   from ap_message  group by message_status   查询二： select count(message_title) countmt, count(message_content) countmc, message_status   from ap_message  group by message_status  orderby countmt desc

 

having限制分组

查询一：having筛选查询结果 select count(message_title) countmt,        count(message_content) countmc,        message_status   from ap_message  group by message_status having count(message_title)=10