JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to postflat modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 [Re:amandalau]
LiaoLuke





發文: 106
積分: 0
於 2009-12-03 15:51 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
select student_num, student_name ,'1' as formNum
from student_table
where type=1

Result:
student_num  student_name formNum
------------------  ----------------------- -----------------------
1        Apple Lee         1
2        James Hsieh         1
4        Victor Wang         1

select student_num, student_name ,'-1' as formNum
from student_table
where type=2

Result:
student_num  student_name formNum
------------------  ----------------------- -----------------------
1       Apple Lee         -1
3        Holly Lo         -1
4        Victor Wang         -1

CREATE GLOBAL TEMPORARY TABLE table_name_01 (
tmp01_dt VARCHAR2(60),
tmp01_gn VARCHAR2(60)
)ON COMMIT DELETE ROWS

CREATE GLOBAL TEMPORARY TABLE table_name_02 (
tmp02_dt VARCHAR2(60),
tmp02_gn VARCHAR2(60)
)ON COMMIT DELETE ROWS

select t.*, t.rowid from table_name_01 t;
select t.*, t.rowid from table_name_02 t;

Insert into table_name_01 (tmp01_dt , tmp01_gn )VALUES('1','Apple Lee');
Insert into table_name_01 (tmp01_dt , tmp01_gn )VALUES('2','James Hsieh');
Insert into table_name_01 (tmp01_dt , tmp01_gn )VALUES('4','Victor Wang');

Insert into table_name_02 (tmp02_dt , tmp02_gn )VALUES('1','Apple Lee');
Insert into table_name_02 (tmp02_dt , tmp02_gn )VALUES('3','Holly Lo');
Insert into table_name_02 (tmp02_dt , tmp02_gn )VALUES('4','Victor Wang');

select A , B ,sum(formNum)
from(
select tmp01_dt as A,tmp01_gn as B, 1 as formNum from table_name_01
union all
select tmp02_dt as A,tmp02_gn as B,-1 as formNum from table_name_02 )
group by A , B

Result:
student_num  student_name formNum
------------------  ----------------------- -----------------------
1       Apple Lee        0
2        James Hsieh         1
3        Holly Lo         -1
4        Victor Wang         0


http://www.techonthenet.com/oracle/functions/decode.php >>>

select A , B ,decode(sum(formNum),1,'來自table 1',-1,'來自table 2','都有')
from(
select tmp01_dt as A,tmp01_gn as B, 1 as formNum from table_name_01
union all
select tmp02_dt as A,tmp02_gn as B,-1 as formNum from table_name_02 )
group by A , B


LiaoLuke edited on 2009-12-03 15:57
reply to postreply to post
話題樹型展開
人氣 標題 作者 字數 發文時間
1658 想請教一下取出二個sql command中,資料不重覆的SQL下法 amandalau 973 2009-12-03 00:58
1483 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 jimmy14 298 2009-12-03 09:23
1457 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 jimmy14 440 2009-12-03 10:03
1498 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 amandalauhm 169 2009-12-03 13:16
1506 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 juevampire 109 2009-12-03 13:33
1472 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 LiaoLuke 113 2009-12-03 15:24
1434 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 LiaoLuke 2288 2009-12-03 15:51
1409 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 mow 549 2009-12-03 15:51
1456 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 LiaoLuke 610 2009-12-03 16:02
1643 Re:想請教一下取出二個sql command中,資料不重覆的SQL下法 amandalauhm 46 2009-12-03 17:43
» JWorld@TW »  JDBC/SQL討論區

reply to postflat modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8