有三张表A,B,C。
A表字段部分如下:
JTB_DIV_CD,ADDRESSEE, INQUIRY_ACTION_PLAN_DATE
B表字段部分如下:
JTB_DIV_CD,MESSAGE_ADDRESSEE,MESSAGE_ACTION_PLAN_DATE
C表字段部分如下:
JTB_DIV_CD,ADDRESS, NOTE_ACTION_PLAN_DATE
以上每个表的字段属性相对应,
求一sql语句,得到如下结果:JTB_DIV_CD ADDRESSEE DATE
--------------------------------------------
A表JTB_DIV_CD A表ADDRESSEE A表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
B表JTB_DIV_CD B表ADDRESSEE B表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
C表JTB_DIV_CD C表ADDRESSEE C表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
C表JTB_DIV_CD C表ADDRESSEE C表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
也就是向把这三张表的这三个字段的数据分别归纳到一起,而且有两个需求。
1。最终结果要按DATE字段排序。
2。最终结果需要第1行到100行的数据各位大侠们!出手把!谢谢
A表字段部分如下:
JTB_DIV_CD,ADDRESSEE, INQUIRY_ACTION_PLAN_DATE
B表字段部分如下:
JTB_DIV_CD,MESSAGE_ADDRESSEE,MESSAGE_ACTION_PLAN_DATE
C表字段部分如下:
JTB_DIV_CD,ADDRESS, NOTE_ACTION_PLAN_DATE
以上每个表的字段属性相对应,
求一sql语句,得到如下结果:JTB_DIV_CD ADDRESSEE DATE
--------------------------------------------
A表JTB_DIV_CD A表ADDRESSEE A表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
B表JTB_DIV_CD B表ADDRESSEE B表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
C表JTB_DIV_CD C表ADDRESSEE C表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
C表JTB_DIV_CD C表ADDRESSEE C表DATE
A表JTB_DIV_CD A表ADDRESSEE A表DATE
也就是向把这三张表的这三个字段的数据分别归纳到一起,而且有两个需求。
1。最终结果要按DATE字段排序。
2。最终结果需要第1行到100行的数据各位大侠们!出手把!谢谢
如果不排序可以实现
用存储过程返回游标处理,直接的sql实现不了
select * from
(select * from
(select JTB_DIV_CD as JTB_DIV_CD£¬ADDRESSEE as ADDRESSEE£¬ INQUIRY_ACTION_PLAN_DATE as DATE from A
union
select JTB_DIV_CD£¬MESSAGE_ADDRESSEE£¬MESSAGE_ACTION_PLAN_DATE from B
union
select JTB_DIV_CD£¬ADDRESS£¬ NOTE_ACTION_PLAN_DATE from C
)
order by date
)
where rownum < 101
select * from
(select * from
(select JTB_DIV_CD as JTB_DIV_CD,ADDRESSEE as ADDRESSEE,INQUIRY_ACTION_PLAN_DATE as DATE from A
union
select JTB_DIV_CD,MESSAGE_ADDRESSEE,MESSAGE_ACTION_PLAN_DATE from B
union
select JTB_DIV_CD,ADDRESS,NOTE_ACTION_PLAN_DATE from C
)
order by date
)
where rownum < 101
不过,需求中还有一个问题,有时候ABC三表查询,有时候AB两表查询,有时候AC两表,有时候
是不是应该加一个判断?
判断的内容是这样的:
在X的条件下,在组合检索中有A表的查询
在Y的条件下,在组合检索中有B表的查询
在Z的条件下,在组合检索中有C表的查询请各位帮帮忙,用最简单的方法加入到
select * from
(select * from
(select JTB_DIV_CD as JTB_DIV_CD,INQUIRY_SEQ as SEQ,CUSTOMER_NO as NO,ADDRESSEE as ADDRESSEE,INQUIRY_ACTION_PLAN_DATE as DATE1 from A_TB_PER_INQUIRY_HISTORY
union
select JTB_DIV_CD,MESSAGE_SEQ as SEQ,1 as NO,MESSAGE_ADDRESSEE as ADDRESSEE,MESSAGE_ACTION_PLAN_DATE as DATE1 from A_TB_PER_MESSAGE
union
select JTB_DIV_CD,TOUR_NOTE_SEQ as SEQ,TOUR_NO as NO,ADDRESS,NOTE_ACTION_PLAN_DATE as DATE1 from A_TB_TUR_TOUR_NOTES
)
order by date1 desc
)
语句中。