台灣最大程式設計社群網站
線上人數
1302
 
會員總數:246038
討論主題:189625
歡迎您免費加入會員
討論區列表 >> Oracle >> oracle 兩段 sql的合併
[]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
oracle 兩段 sql的合併
價值 : 30 QP  點閱數:1471 回應數:2
樓主

kobe
門外漢
0 1
32 1
發送站內信

SELECT
P.CARDNO,
P.NIDNO,
P.NAMEC,
(SELECT NAME FROM EHIRE WHERE ID = P.HIRE) AS HIRE_NM,
P.HOLIDAYSOFYEAR,
P.SEX,
P.HOLIDAYSOFLAST1YEAR,
P.HOLIDAYSOFLAST2YEAR
FROM PSBASIC P
WHERE 1 = 1
AND P.CARDNO = 'D060';


結果:

CARDNO NIDNO NAMEC HIRE_NM SEX HOLIDAYSOFYEAR HOLIDAYSOFLAST1YEAR HOLIDAYSOFLAST2YEAR
D060 12345 王小明 契約 男 0 0 0



SELECT P.ABSTYP,
P.DAYS,
P.HOURS,
(SELECT NAME FROM EABSTYP WHERE ID = P.ABSTYP) AS ENAME
FROM (SELECT NIDNO, ABSTYP, ABSBGDT, SUM(ABSDAY) AS DAYS, SUM(ABSHOUR) AS HOURS
FROM PSABSCNT
WHERE 1 = 1
AND NIDNO = '12345'
AND TO_NUMBER(ABSBGDT) >= '1030101'
AND TO_NUMBER(ABSBGDT) <= '1031231'

GROUP BY NIDNO, ABSTYP, ABSBGDT) P
ORDER BY P.ABSTYP;



結果 :

ABSTYP DAYS HOURS ENAME
02 1 0 A
10 0 1 B
10 0 3 B



請問版上的各位前輩

該如何修改這兩段SQL 合併為一段

呈現出的結果可以為

CARDNO NIDNO NAMEC HIRE_NM SEX HOLIDAYSOFYEAR HOLIDAYSOFLAST1YEAR HOLIDAYSOFLAST2YEAR
D060 12345 王小明 契約 男 0 0 0


ENAME DAYS HOURS ENAME DAYS HOURS
A 1 0 B 0 4




非常感謝各位前輩的幫忙 感激不盡!!!

本篇文章發表於2014-07-18 10:02
1樓
作者回應

kobe
檢舉此回應
SELECT
P.CARDNO,
P.NIDNO,
P.NAMEC,
(SELECT NAME FROM EHIRE WHERE ID = P.HIRE) AS HIRE_NM,
P.HOLIDAYSOFYEAR,
P.SEX,
P.HOLIDAYSOFLAST1YEAR,
P.HOLIDAYSOFLAST2YEAR
FROM PSBASIC P
WHERE 1 = 1
AND P.CARDNO = 'D060';


結果:

CARDNO NIDNO NAMEC HIRE_NM SEX HOLIDAYSOFYEAR HOLIDAYSOFLAST1YEAR HOLIDAYSOFLAST2YEAR
D060 12345 王小明 契約 男 0 0 0



SELECT P.ABSTYP,
P.DAYS,
P.HOURS,
(SELECT NAME FROM EABSTYP WHERE ID = P.ABSTYP) AS ENAME
FROM (SELECT NIDNO, ABSTYP, ABSBGDT, SUM(ABSDAY) AS DAYS, SUM(ABSHOUR) AS HOURS
FROM PSABSCNT
WHERE 1 = 1
AND NIDNO = '12345'
AND TO_NUMBER(ABSBGDT) >= '1030101'
AND TO_NUMBER(ABSBGDT) <= '1031231'

GROUP BY NIDNO, ABSTYP, ABSBGDT) P
ORDER BY P.ABSTYP;



結果 :

ABSTYP DAYS HOURS ENAME
02 1 0 A
10 0 1 B
10 0 3 B



請問版上的各位前輩

該如何修改這兩段SQL 合併為一段

呈現出的結果可以為

CARDNO NIDNO NAMEC HIRE_NM SEX HOLIDAYSOFYEAR HOLIDAYSOFLAST1YEAR HOLIDAYSOFLAST2YEAR
D060 12345 王小明 契約 男 0 0 0


ENAME DAYS HOURS ENAME DAYS HOURS
A 1 0 B 0 4




非常感謝各位前輩的幫忙 感激不盡!!!
本篇文章回覆於2014-07-18 10:04
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
2樓
--看起來是依據 NIDNO 作為關聯的, 使用with as 會比較明白一些

with AAA as
(
SELECT P.NIDNO,
P.ABSTYP,
P.DAYS,
P.HOURS,
(SELECT NAME
FROM EABSTYP
WHERE ID = P.ABSTYP) AS ENAME
FROM (SELECT NIDNO,
ABSTYP,
ABSBGDT,
SUM(ABSDAY) AS DAYS,
SUM(ABSHOUR) AS HOURS
FROM PSABSCNT
WHERE 1 = 1
AND NIDNO = '12345'
AND TO_NUMBER(ABSBGDT) >= '1030101'
AND TO_NUMBER(ABSBGDT) <= '1031231'
GROUP BY NIDNO,
ABSTYP,
ABSBGDT) P
--ORDER BY P.ABSTYP
),
BBB as
(
SELECT P.CARDNO,
P.NIDNO,
P.NAMEC,
(SELECT NAME
FROM EHIRE
WHERE ID = P.HIRE) AS HIRE_NM,
P.HOLIDAYSOFYEAR,
P.SEX,
P.HOLIDAYSOFLAST1YEAR,
P.HOLIDAYSOFLAST2YEAR
FROM PSBASIC P
WHERE 1 = 1
AND P.CARDNO = 'D060'
)
select AAA.ABSTYP,AAA.DAYS,AAA.HOURS,AAA.ENAME, BBB.*
from AAA , BBB
where AAA.NIDNO = BBB.NIDNO

本篇文章回覆於2014-11-25 10:24
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
   
1

回覆
如要回應,請先登入.