时间戳

首页 > 解决方案 > 在子查询中使用 where 选择查询

sql - 在子查询中使用 where 选择查询

问题描述

我有这样的报告查询选择，

select * from (

select *
FROM
  CTIORASQL.time_sheet ts
  LEFT JOIN(
    SELECT 
        emp.employee_id,
        SUM(
        CASE
            WHEN emp.main_company_id = 9 
            THEN 
                CASE
                    WHEN sce.schedule_type_id=1 THEN sce.total_minute
                END
            WHEN sce.activity_id = 21 THEN sce.total_minute
       END)/60 utl_tot
    FROM CTIORASQL.time_sheet ts
        LEFT JOIN CTIORASQL.time_sheet_schedule sce ON ts.time_sheet_id = sce.time_sheet_id
        LEFT JOIN CTIORASQL.m_employee emp ON ts.requester_id = emp.employee_id
        where ts.schedule_date BETWEEN '02-02-2020' and '07-10-2020'
        GROUP BY emp.employee_id
  ) utl ON utl.employee_id = emp.employee_id
  
)
where ts.schedule_date BETWEEN '02-02-2020' and '07-10-2020'

我在子查询中有相同的 where，如何将 where 条件设为一个条件？谢谢你在我不能把sql代码放到后端的情况下，我只是使用sqldeveloper工具

标签： sql

解决方案

外部查询是否有任何原因。它似乎与子查询相同。只需对您想要的结果使用子查询：

select *
FROM
  CTIORASQL.time_sheet ts
  LEFT JOIN(
    SELECT 
        emp.employee_id,
        SUM(
        CASE
            WHEN emp.main_company_id = 9 
            THEN 
                CASE
                    WHEN sce.schedule_type_id=1 THEN sce.total_minute
                END
            WHEN sce.activity_id = 21 THEN sce.total_minute
       END)/60 utl_tot
    FROM CTIORASQL.time_sheet ts
        LEFT JOIN CTIORASQL.time_sheet_schedule sce ON ts.time_sheet_id = sce.time_sheet_id
        LEFT JOIN CTIORASQL.m_employee emp ON ts.requester_id = emp.employee_id
        where ts.schedule_date BETWEEN '02-02-2020' and '07-10-2020'
        GROUP BY emp.employee_id
  ) utl ON utl.employee_id = emp.employee_id

如果您对外部查询有任何其他目的，请解释。

推荐阅读