基于输入时间获取下一班次时间及SQL SELECT语句FROM子句疑问

Hey there! Let's break down how to fix your code and figure out the right FROM clause for your scenario.

First, let's correct the CASE statement logic—there are two key issues in your current code:

• The THEN clause shouldn't use start_shift_time = '06:00:00' syntax; you just need to return the time value directly.
• Your interval checks use OR instead of AND, which will lead to incorrect matches (for example, any time would satisfy >= '00:00:00' OR < '06:00:00').

Here's the corrected CASE structure, plus a fix for the last condition (since a time can't be both >=14:00 and <00:00):

SELECT 
  CASE 
    WHEN CAST(rt_time_id AS TIME) >= '00:00:00' AND CAST(rt_time_id AS TIME) < '06:00:00' 
      THEN '06:00:00'
    WHEN CAST(rt_time_id AS TIME) >= '06:00:00' AND CAST(rt_time_id AS TIME) < '14:00:00' 
      THEN '14:00:00'
    WHEN CAST(rt_time_id AS TIME) >= '14:00:00' 
      THEN '00:00:00'
  END AS start_shift_time

Now, about the FROM clause: this depends entirely on where rt_time_id comes from, since it's part of a larger stored procedure/function:

• If rt_time_id is a parameter passed to the procedure/function:
  • For Oracle: Use FROM DUAL (a dummy table for single-row queries)

    SELECT 
      CASE 
        WHEN CAST(:rt_time_id AS TIME) >= '00:00:00' AND CAST(:rt_time_id AS TIME) < '06:00:00' 
          THEN '06:00:00'
        WHEN CAST(:rt_time_id AS TIME) >= '06:00:00' AND CAST(:rt_time_id AS TIME) < '14:00:00' 
          THEN '14:00:00'
        WHEN CAST(:rt_time_id AS TIME) >= '14:00:00' 
          THEN '00:00:00'
      END AS start_shift_time
    FROM DUAL;
    

  • For SQL Server: Use VALUES() to create a single-row result set with your parameter

    SELECT 
      CASE 
        WHEN CAST(@rt_time_id AS TIME) >= '00:00:00' AND CAST(@rt_time_id AS TIME) < '06:00:00' 
          THEN '06:00:00'
        WHEN CAST(@rt_time_id AS TIME) >= '06:00:00' AND CAST(@rt_time_id AS TIME) < '14:00:00' 
          THEN '14:00:00'
        WHEN CAST(@rt_time_id AS TIME) >= '14:00:00' 
          THEN '00:00:00'
      END AS start_shift_time
    FROM (VALUES (@rt_time_id)) AS t(rt_time_id);
    

  • For MySQL: You can skip the FROM clause entirely (MySQL allows SELECT without FROM for single-row queries) or use FROM DUAL

    SELECT 
      CASE 
        WHEN CAST(@rt_time_id AS TIME) >= '00:00:00' AND CAST(@rt_time_id AS TIME) < '06:00:00' 
          THEN '06:00:00'
        WHEN CAST(@rt_time_id AS TIME) >= '06:00:00' AND CAST(@rt_time_id AS TIME) < '14:00:00' 
          THEN '14:00:00'
        WHEN CAST(@rt_time_id AS TIME) >= '14:00:00' 
          THEN '00:00:00'
      END AS start_shift_time;
    

• If rt_time_id comes from a table in your database: Just replace the dummy table/VALUES clause with your actual table name, like FROM your_table_name (add a WHERE clause if you need to filter specific rows).

内容的提问来源于stack exchange，提问作者Ahsan Cheema