两个SQL查询的交替列
本文关键字:SQL 查询 两个 | 更新日期: 2023-09-27 18:24:39
我有一个oracle查询,它从两个不同的表中获取数据,并将它们显示为:
Project | 20151 | 20152 | 20153 | 20154 | Project_1 | 20151_1 | 20152_1 | 20153_1 | 20154_1
-------------------------------------------------------------------------------------------
这确实得到了我所需要的正确数据,但我需要一种方法来交替每个表中的列。例如:
Project | Project_1 | 20151 | 20151_1 | ...
---------------------------------------
我的查询如下:
SELECT * FROM(
SELECT * FROM
(
SELECT planned.YEAR || planned.MONTH as AAA, planned.DAYS as Amount, PROJ.NAME AS Project
FROM PLANNED_RESOURCE_TAB planned, USER_LINK_VIEW PROJ
WHERE ACTUAL.U_P_LINK = PROJ.ID
AND PROJ.USER_ID = '1'
)
PIVOT
(
SUM(Amount) FOR AAA in (20151,20152,20153,20154)
)
)
PLANNED__ RIGHT OUTER JOIN
(
SELECT * FROM
(
SELECT ACTUAL.YEAR || ACTUAL.MONTH as BBB, ACTUAL.DAYS as Amount, PROJ.NAME AS Project
FROM ACTUAL_RESOURCE_TAB actual, USER_LINK_VIEW PROJ
WHERE ACTUAL.U_P_LINK = PROJ.ID
AND PROJ.USER_ID = '1'
)
PIVOT
(
SUM(Amount) FOR BBB in (20151,20152,20153,20154)
)
)ACTUAL__
ON PLANNED__.PROJECT = ACTUAL__.PROJECT
数据结果将显示在ASP.NET页面上,因此,如果最好操作页面本身的列,则没有问题。我主要想知道是否可以事先使用SQL来完成这项工作,以及随后我将如何做到这一点。
列不能在第一个select语句中声明,它们将根据用户的输入而变化。数据透视表中的值仅用于我的测试目的,它们将从ASP.NET页面动态生成。
提前感谢!
您是否尝试用最外部选择的检索列替换*
?类似这样的东西:
SELECT PLANNED__.Project,
ACTUAL__.Project,
PLANNED__."20151",
ACTUAL__."20151",
PLANNED__."20152",
ACTUAL__."20152",
PLANNED__."20153",
ACTUAL__."20153",
PLANNED__."20154",
ACTUAL__."20154"
FROM
(SELECT *
FROM
(SELECT planned.YEAR
|| planned.MONTH AS AAA,
planned.DAYS AS Amount,
PROJ.NAME AS Project
FROM PLANNED_RESOURCE_TAB planned,
USER_LINK_VIEW PROJ
WHERE ACTUAL.U_P_LINK = PROJ.ID
AND PROJ.USER_ID = '1'
) PIVOT ( SUM(Amount) FOR AAA IN (20151,20152,20153,20154) )
) PLANNED__
RIGHT OUTER JOIN
(SELECT *
FROM
(SELECT ACTUAL.YEAR
|| ACTUAL.MONTH AS BBB,
ACTUAL.DAYS AS Amount,
PROJ.NAME AS Project
FROM ACTUAL_RESOURCE_TAB actual,
USER_LINK_VIEW PROJ
WHERE ACTUAL.U_P_LINK = PROJ.ID
AND PROJ.USER_ID = '1'
) PIVOT ( SUM(Amount) FOR BBB IN (20151,20152,20153,20154) )
)ACTUAL__
ON PLANNED__.PROJECT = ACTUAL__.PROJECT