试图显示表中的某些列和

Question

我有我的SQL查询，它显示了所有需要的数据，但是我需要帮助获得一个特定的输出。我需要使用下面的查询显示一个标有循环(不在海量查询中)、LPT和SUM of PLAN_MOVES的列。我可以在Plan_Moves上进行选择和，并将其标记为Target_Daily_Moves，但不确定如何显示其他两列。请帮助我，因为我仍然在学习SQL。

我在试着展示这个预期的结果。

​

​

新的批量查询SQL W/o“保留循环名称”：

    select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt

原始批量查询SQL /o“保留循环名称”OutPut：

​

​

更新:最后注释中的SQL包含"loops_info“而不是”循环“：

     SELECT a.LOOPS_INFO AS 'LOOPS_INFO', 
          a.lpt AS 'LPT', 
          SUM(a.plan_moves) AS 'Target Daily Moves' 
    FROM (
  select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt ) AS a 
ORDER BY a.lpt ASC 
GROUP BY a.lpt 
   UNION 
  SELECT 'TOTAL' AS 'LOOPS_INFO', 
         '' AS 'LPT', 
         SUM(b.plan_moves) AS 'Target Daily Moves' 
   FROM (
    select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case WHEN dds.lpt = '1400' THEN 'MOAT'
WHEN dds.lpt = '3300' THEN 'GATE'
  WHEN dds.lpt = '4800' THEN 'S/D'
    WHEN dds.lpt = '5130' THEN 'PMD'
      WHEN dds.lpt = '5800' THEN 'CONTACT'
        WHEN dds.lpt = '6400' THEN 'VIA 1'
          WHEN dds.lpt = '6900' THEN 'VIA 2'
            WHEN dds.lpt = '7900' THEN 'VIA 3-4'
              WHEN dds.lpt = '9200' THEN 'PO'
                WHEN dds.lpt = '9348' THEN 'PARAMETRICS'
                  WHEN dds.lpt = '9950' THEN 'OUTS'
else 'none'
  end) as LOOPS_INFO,

sum(dds.sched_cur_qty) plan_moves
from device_daily_sched@smsdwde2.itg.ti.com dds, dm_lpt_attributes@Smsdwde2.itg.ti.com la, dm_device_attributes@smsdwde2.itg.ti.com da
where 
dds.facility = 'DP1DM5'
and dds.facility = la.facility
and dds.lpt = la.lpt
and dds.device = da.device
and dds.lpt in('1400','3300','4800','5130','5800','6400','6900','7900','9200','9348','9950')
and (dds.device like 'SN/%'or dds.device like 'SS/%')
and (dds.sched_dttm = trunc(sysdate))
and dds.device not in('SN/BC3ZLTM','SN/DMD8SF','SN/DMD8','SN/DMDASSEM','SN/R035','SN/RFSIGNET','SN/SMIC','SS/BC3ZL','SS/BICOM3ZL','SS/BICOM3','SS/BICOMPBO','SS/R05S','SS/THERM')
group by
da.family,
dds.lpt ) AS b 

相同的输出错误: ORA-00923: FROM关键字未在预期的位置找到

​

Answer 1

如果我对“循环”的理解是正确的，您可以将几个if命令嵌套在一起，测试lpt的值，并为每个值返回一个字符串值，作为结果集参数之一，基于lpt的值。我假设lpt的每个不同的值确定了在dds.lpt='1400‘时哪个循环的值is...so，例如，循环总是'MOAT'，对吗？

Or...use一个case块(看起来更干净)。

有关这两种语言的语法的更多信息，请参见本文：How do I perform an IF...THEN in an SQL SELECT?。

嵌套的if或case将作为另一个结果列包括在内，然后是'AS循环‘，因此结果集包含一个具有相应值的名为循环的列。

就像这样：

select
da.family,
sysdate update_dttm,
max(dds.sched_update_dttm) max_sched_update_dttm,
dds.lpt,
( case-or-if-testing-dds.lpt-values... ) loop, 
sum(dds.sched_cur_qty) plan_moves
...etc.

在前5条评论之后的其他修改答案被添加到原来的答案中：

然后，尝试将整个查询围绕两个联合的单独SELECT语句(如this...not what )进行包装，但是如果您不能在其他非SQL代码中进行任何后处理，它应该会为您提供您想要的：

   SELECT a.loop "Loop", 
          a.lpt "LPT", 
          SUM(a.plan_moves) "Target Daily Moves" 
    FROM (
  <your entire select SQL in here> 
         ) a 
ORDER BY a.lpt ASC 
GROUP BY a.lpt 
   UNION 
  SELECT "TOTAL" "Loop", 
         "" "LPT", 
         SUM(b.plan_moves) "Target Daily Moves" 
   FROM (
  <your entire select SQL in here> 
         ) b