两个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页面动态生成。

提前感谢！

两个SQL查询的交替列

您是否尝试用最外部选择的检索列替换*？类似这样的东西：

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
相关文章：