合并具有不同列的查询 [英] merging queries with different columns
问题描述
我有两个类似的SQL查询,但只有不同的where子句过滤器才能提取适当的管理员名称(point_of_contact,admin).我需要产生将两个过滤查询的结果结合在一起的结果,但我不确定该如何解决.列相同,除了联系点和管理员.我需要admin和point_of_contact在不同的列中.有一个联系点或一个管理员,但都没有留下一个空位.我尝试了递归sql,并且case语句和Im遇到了一些麻烦.我在下面将查询分为两个简单查询.(DB2-不确定版本)
I have two similar sql queries that only have different where clause filters to extract the appropriate admin names (point_of_contact, admin). I need to produce results that combines the results of both filtered queries and I was not sure of how to tackle that. The columns are the same except for point of contact and admin. I need the admin and point_of_contact to be in different columns. There is either a point of contact or admin but not both leaving one null. I have tried recursive sql, and case statements and Im having some trouble. I separated the queries into two simple queries below. (DB2 - not sure of the version)
SELECT
DP.DIM_PROJECT_ID,
DP.PROJECT_NAME,
DP.POINT_OF_CONTACT,
DP.FIELD,
DD.YEAR
FROM FACT_Table FAT
RIGHT OUTER JOIN DIM_A AS DA on FAT.DIM_A_ID = DA.DIM_A_ID
INNER JOIN DIM_P DP on DA.DIM_P_ID = DP.DIM_P_ID
INNER JOIN BRIDGE_USER BUP on BUP.BRIDGE_ID = DP.DIM_P_ID
INNER JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
WHERE DA.AWARD_CATEGORY <> 'N/A'
AND DD.YEAR = '2013'
and (
SELECT count(BUP_INNER.ADMIN_FLAG) FROM BRIDGE_USER BUP_INNER
INNER JOIN DIM_P DP_INNER on BUP_INNER.DIM_P_ID = DP_INNER.DIM_P_ID
WHERE DP_INNER.DIM_P_ID = DP.DIM_P_ID
AND BUP_INNER.ADMIN_FLAG = 'Y'
) = 0
GROUP BY DA.AWARD_TYPE_NAME, DA.AWARD_CATEGORY, DP.PROJECT_NAME, DP.POINT_OF_CONTACT, DD.YEAR
和
SELECT distinct
DP.DIM_PROJECT_ID,
DU.NAME_LAST CONCAT ', ' CONCAT t1.NAME_FIRST AS ADMIN,
DP.PROJECT_NAME,
DP.PROJECT_TITLE,
DD.YEAR,
DP.FIELD
FROM FACT_Table as FAT
INNER JOIN DIM_P DP ON FAT.DIM_P_ID = DP.DIM_P_ID
INNER JOIN BRIDGE_USER BUP on DP.DIM_P_ID = BUP.DIM_P_ID
INNER JOIN DIM_USER DU ON FAT.DIM_USER_ID = DU.DIM_USER_ID
INNER JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
INNER JOIN DIM_A DA ON FAT.DIM_A_ID = DA.DIM_A_ID
WHERE BUP.ADMIN_FLAG = 'Y'
and DD.YEAR = '2013'
and DA.AWARD_CATEGORY <> 'NA'
)
GROUP BY
DP.DIM_P_ID,
DU.NAME_LAST CONCAT ', ' CONCAT DU.NAME_FIRST,
DP.PROJECT_NAME,
DP.TITLE,
DD.YEAR,
DP.FIELD
推荐答案
设置多余的未使用列为空的列
Set up extra columns that are null where they are not used
SELECT distinct
DP.DIM_PROJECT_ID,
DP.PROJECT_NAME,
DP.PROJECT_TITLE,
null as admin,
DP.POINT_OF_CONTACT,
DP.FIELD,
DD.YEAR
FROM FACT_Table FAT
RIGHT OUTER
JOIN DIM_A DA on FAT.DIM_A_ID = DA.DIM_A_ID
JOIN DIM_P DP on DA.DIM_P_ID = DP.DIM_P_ID
JOIN BRIDGE_USER BUP on BUP.BRIDGE_ID = DP.DIM_P_ID
JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
WHERE DA.AWARD_CATEGORY <> 'N/A'
and DD.YEAR = '2013'
and NOT EXISTS (
SELECT *
FROM BRIDGE_USER BUP_INNER
JOIN DIM_P DP_INNER
on DP_INNER.DIM_P_ID = BUP_INNER.DIM_P_ID
WHERE DP_INNER.DIM_P_ID = DP.DIM_P_ID
AND BUP_INNER.ADMIN_FLAG = 'Y'
)
UNION ALL
SELECT distinct
DP.DIM_PROJECT_ID,
DP.PROJECT_NAME,
DP.PROJECT_TITLE,
DU.NAME_LAST CONCAT ', ' CONCAT t1.NAME_FIRST AS ADMIN,
null as POINT_OF_CONTACT,
DD.YEAR,
DP.FIELD
FROM FACT_Table as FAT
JOIN DIM_P DP ON FAT.DIM_P_ID = DP.DIM_P_ID
JOIN BRIDGE_USER BUP on BUP.DIM_P_ID = DP.DIM_P_ID
and BUP.ADMIN_FLAG = 'Y'
JOIN DIM_USER DU ON FAT.DIM_USER_ID = DU.DIM_USER_ID
JOIN DIM_DATE DD on DD.DATE_KEY = DA.A_START_DATE_ID
and DD.YEAR = '2013'
JOIN DIM_A DA ON DA.DIM_A_ID = FAT.DIM_A_ID
and DA.AWARD_CATEGORY <> 'NA'
NOT EXISTS应该更有效,因为一旦找到满足子查询的一行,它将返回一个答案.另一方面,COUNT(*)将继续搜索满足子查询的所有所有行.
NOT EXISTS should be more efficient since it will return an answer the moment one row is found satisfying the subquery. COUNT(*) on the other hand, will continue searching for all rows that satisfy the subquery.
我之所以省略了GROUP BY子句,是因为不涉及聚合函数,因此我不理解您要使用它们做什么.
I left out your GROUP BY clauses because I did not understand what you were trying to do with them since there are no aggregate functions involved.
这篇关于合并具有不同列的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!