select MAIN_ID, MAX(COL_DE_ASSOR) AS COL_DE_ASSOR, MAX(COL_OE_ASSOR) AS COL_OE_ASSOR, MAX(case when IND_FLAG=1 then col_comm end) as de_com, MAX(case when IND_FLAG=0 then col_comm end) as oe_com from TAB_MAIN left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID GROUP BY TAB_MAIN.MAIN_ID
上面漏写了MAIN_ID的表名,修正下 select TAB_MAIN.MAIN_ID, MAX(COL_DE_ASSOR) AS COL_DE_ASSOR, MAX(COL_OE_ASSOR) AS COL_OE_ASSOR, MAX(case when IND_FLAG=1 then col_comm end) as de_com, MAX(case when IND_FLAG=0 then col_comm end) as oe_com from TAB_MAIN left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID GROUP BY TAB_MAIN.MAIN_ID
select distinct main_id, COL_DE_ASSOR, COL_OE_ASSOR, (select col_comm from TAB_FCAA t2 where t1.main_id = t2.fcaa_id and t2.ind_flag = 1) de_com, (select col_comm from TAB_FCAA t2 where t1.main_id = t2.fcaa_id and t2.ind_flag = 0) oe_com from TAB_MAIN t1
select distinct MAIN_ID, COL_DE_ASSOR, COL_OE_ASSOR, de.col_comm as de_com, oe.col_comm as oe_com from TAB_MAIN left join TAB_FCAA de on TAB_MAIN.MAIN_ID = de.FCAA_ID AND de.IND_FLAG=1 left join TAB_FCAA oe on TAB_MAIN.MAIN_ID = oe.FCAA_ID AND oe.IND_FLAG=0
今天试了一下: 2楼的方法,虽然能达到目的,但总觉得结果不是很对,少了很多数据, 3楼,5楼数好像不大对。其实我是想 从得到 我写的是这样的,得到上面的结果,能得到下面这个结果吗?谢谢 。 SELECT DISTINCT FC.N_CONTROL_KEY, FC_DE_UP.V_USR_NAME V_CONTROL_DE_ASSESSOR, CASE WHEN FCAA.V_INDICATOR_FLAG=0 THEN FCAA.N_DESIGN_RATING_KEY END AS DE, FC_OE_UP.V_USR_NAME V_CONTROL_OE_ASSESSOR, CASE WHEN FCAA.V_INDICATOR_FLAG=1 THEN FCAA.N_DESIGN_RATING_KEY END AS OE FROM DBSATOMIC.FCT_CONTROL FC LEFT JOIN DBSATOMIC.FCT_CONTROL_ASSESSMENT_AGG FCAA ON FC.N_CONTROL_KEY=FCAA.N_CONTROL_KEY LEFT JOIN DBSATOMIC.USER_PROFILE FC_DE_UP ON FC.V_CONTROL_DE_ASSESSOR=FC_DE_UP.V_USR_ID LEFT JOIN DBSATOMIC.USER_PROFILE FC_OE_UP ON FC.V_CONTROL_OE_ASSESSOR=FC_OE_UP.V_USR_ID WHERE FC.N_CONTROL_KEY='129030633' ORDER BY FC.N_CONTROL_KEY;由这个 N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE 129030633 SG TNM Creator1 SG TNM Approver 1 3 129030633 SG TNM Creator1 3 SG TNM Approver 1 129030633 SG TNM Creator1 SG TNM Approver 1 2 129030633 SG TNM Creator 1 2 SG TNM Approver 1 129030633 SG TNM Creator 1 SG TNM Approver 1 129030633 SG TNM Creator 1 SG TNM Approver 1 1 129030633 SG TNM Creator 1 1 SG TNM Approver 1 得到如下 N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE 129030633 SG TNM Creator 1 3 SG TNM Approver 1 3 129030633 SG TNM Creator 1 2 SG TNM Approver 1 2 129030633 SG TNM Creator 1 1 SG TNM Approver 1 1 129030633 SG TNM Creator 1 SG TNM Approver 1 谢谢了啊 。大神们
你 #0 举例数据根本没有反映真实需求。 WITH data(N_CONTROL_KEY,V_CONTROL_DE_ASSESSOR,DE,V_CONTROL_OE_ASSESSOR,OE) AS ( -- 这里用你 #7 的查询替换 SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 3 UNION ALL SELECT 129030633,'SG TNM Creator 1', 3,'SG TNM Approver 1',NULL UNION ALL SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 2 UNION ALL SELECT 129030633,'SG TNM Creator 1', 2,'SG TNM Approver 1',NULL UNION ALL SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',NULL UNION ALL SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 1 UNION ALL SELECT 129030633,'SG TNM Creator 1', 1,'SG TNM Approver 1',NULL ) ,d AS ( SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_DE_ASSESSOR, DE FROM data ) ,o AS ( SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_OE_ASSESSOR, OE FROM data ) SELECT d.N_CONTROL_KEY, d.V_CONTROL_DE_ASSESSOR, d.DE, o.V_CONTROL_OE_ASSESSOR, o.OE FROM d JOIN O ON d.N_CONTROL_KEY = o.N_CONTROL_KEY AND ( (d.DE = o.OE) OR( (d.DE IS NULL) AND(o.OE IS NULL) ) ) ORDER BY d.DE DESC N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE ------------- --------------------- ----------- --------------------- ----------- 129030633 SG TNM Creator 1 3 SG TNM Approver 1 3 129030633 SG TNM Creator 1 2 SG TNM Approver 1 2 129030633 SG TNM Creator 1 1 SG TNM Approver 1 1 129030633 SG TNM Creator 1 NULL SG TNM Approver 1 NULL
MAX(COL_DE_ASSOR) AS COL_DE_ASSOR,
MAX(COL_OE_ASSOR) AS COL_OE_ASSOR,
MAX(case when IND_FLAG=1 then col_comm end) as de_com,
MAX(case when IND_FLAG=0 then col_comm end) as oe_com
from TAB_MAIN
left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID
GROUP BY TAB_MAIN.MAIN_ID
select TAB_MAIN.MAIN_ID,
MAX(COL_DE_ASSOR) AS COL_DE_ASSOR,
MAX(COL_OE_ASSOR) AS COL_OE_ASSOR,
MAX(case when IND_FLAG=1 then col_comm end) as de_com,
MAX(case when IND_FLAG=0 then col_comm end) as oe_com
from TAB_MAIN
left join TAB_FCAA on TAB_MAIN.MAIN_ID=TAB_FCAA.FCAA_ID
GROUP BY TAB_MAIN.MAIN_ID
select distinct main_id,
COL_DE_ASSOR,
COL_OE_ASSOR,
(select col_comm
from TAB_FCAA t2
where t1.main_id = t2.fcaa_id
and t2.ind_flag = 1) de_com,
(select col_comm
from TAB_FCAA t2
where t1.main_id = t2.fcaa_id
and t2.ind_flag = 0) oe_com
from TAB_MAIN t1
MAIN_ID,
COL_DE_ASSOR,
COL_OE_ASSOR,
de.col_comm as de_com,
oe.col_comm as oe_com
from TAB_MAIN
left join TAB_FCAA de
on TAB_MAIN.MAIN_ID = de.FCAA_ID
AND de.IND_FLAG=1
left join TAB_FCAA oe
on TAB_MAIN.MAIN_ID = oe.FCAA_ID
AND oe.IND_FLAG=0
2楼的方法,虽然能达到目的,但总觉得结果不是很对,少了很多数据,
3楼,5楼数好像不大对。其实我是想
从得到
我写的是这样的,得到上面的结果,能得到下面这个结果吗?谢谢 。
SELECT DISTINCT FC.N_CONTROL_KEY,
FC_DE_UP.V_USR_NAME V_CONTROL_DE_ASSESSOR,
CASE WHEN FCAA.V_INDICATOR_FLAG=0 THEN FCAA.N_DESIGN_RATING_KEY END AS DE,
FC_OE_UP.V_USR_NAME V_CONTROL_OE_ASSESSOR,
CASE WHEN FCAA.V_INDICATOR_FLAG=1 THEN FCAA.N_DESIGN_RATING_KEY END AS OE
FROM DBSATOMIC.FCT_CONTROL FC
LEFT JOIN DBSATOMIC.FCT_CONTROL_ASSESSMENT_AGG FCAA ON FC.N_CONTROL_KEY=FCAA.N_CONTROL_KEY
LEFT JOIN DBSATOMIC.USER_PROFILE FC_DE_UP ON FC.V_CONTROL_DE_ASSESSOR=FC_DE_UP.V_USR_ID
LEFT JOIN DBSATOMIC.USER_PROFILE FC_OE_UP ON FC.V_CONTROL_OE_ASSESSOR=FC_OE_UP.V_USR_ID
WHERE FC.N_CONTROL_KEY='129030633'
ORDER BY FC.N_CONTROL_KEY;由这个
N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE
129030633 SG TNM Creator1 SG TNM Approver 1 3
129030633 SG TNM Creator1 3 SG TNM Approver 1
129030633 SG TNM Creator1 SG TNM Approver 1 2
129030633 SG TNM Creator 1 2 SG TNM Approver 1
129030633 SG TNM Creator 1 SG TNM Approver 1
129030633 SG TNM Creator 1 SG TNM Approver 1 1
129030633 SG TNM Creator 1 1 SG TNM Approver 1
得到如下
N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE
129030633 SG TNM Creator 1 3 SG TNM Approver 1 3
129030633 SG TNM Creator 1 2 SG TNM Approver 1 2
129030633 SG TNM Creator 1 1 SG TNM Approver 1 1
129030633 SG TNM Creator 1 SG TNM Approver 1
谢谢了啊 。大神们
WITH data(N_CONTROL_KEY,V_CONTROL_DE_ASSESSOR,DE,V_CONTROL_OE_ASSESSOR,OE) AS (
-- 这里用你 #7 的查询替换
SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 3 UNION ALL
SELECT 129030633,'SG TNM Creator 1', 3,'SG TNM Approver 1',NULL UNION ALL
SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 2 UNION ALL
SELECT 129030633,'SG TNM Creator 1', 2,'SG TNM Approver 1',NULL UNION ALL
SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1',NULL UNION ALL
SELECT 129030633,'SG TNM Creator 1',NULL,'SG TNM Approver 1', 1 UNION ALL
SELECT 129030633,'SG TNM Creator 1', 1,'SG TNM Approver 1',NULL
)
,d AS (
SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_DE_ASSESSOR, DE
FROM data
)
,o AS (
SELECT DISTINCT N_CONTROL_KEY, V_CONTROL_OE_ASSESSOR, OE
FROM data
)
SELECT d.N_CONTROL_KEY,
d.V_CONTROL_DE_ASSESSOR,
d.DE,
o.V_CONTROL_OE_ASSESSOR,
o.OE
FROM d
JOIN O
ON d.N_CONTROL_KEY = o.N_CONTROL_KEY
AND ( (d.DE = o.OE)
OR( (d.DE IS NULL)
AND(o.OE IS NULL)
)
)
ORDER BY d.DE DESC
N_CONTROL_KEY V_CONTROL_DE_ASSESSOR DE V_CONTROL_OE_ASSESSOR OE
------------- --------------------- ----------- --------------------- -----------
129030633 SG TNM Creator 1 3 SG TNM Approver 1 3
129030633 SG TNM Creator 1 2 SG TNM Approver 1 2
129030633 SG TNM Creator 1 1 SG TNM Approver 1 1
129030633 SG TNM Creator 1 NULL SG TNM Approver 1 NULL