在MySQL中没有得到案例语句的结果

发布于 2025-01-31 13:04:01 字数 398 浏览 4 评论 0原文

我试图在MySQL中执行以下查询，期望如下

如表中不存在记录，
如果存在记录，则返回当前时间戳，请比较开始日期和结束日期和结束日期的值，并返回适当的值。

但是，对于没有记录的第一个执行，以下查询没有返回当前的时间戳值。这里有什么问题？

SELECT 
CASE WHEN start_dt < end_dt THEN end_dt 
WHEN start_dt >= end_dt THEN start_dt 
ELSE CURRENT_TIMESTAMP() END AS start_dt 
FROM test_master_table WHERE 
schema_nm='test_db' and table_nm='test_table';

原文

I am trying to execute the below query in MYSQL, The expectation is as below

If no record exists in table, return current timestamp
if record exists, compare the values of start date and end date and return appropriate values.

However, for the first execution, where there are no records, the below query is not returning the current timestamp value. What could be wrong here?

SELECT 
CASE WHEN start_dt < end_dt THEN end_dt 
WHEN start_dt >= end_dt THEN start_dt 
ELSE CURRENT_TIMESTAMP() END AS start_dt 
FROM test_master_table WHERE 
schema_nm='test_db' and table_nm='test_table';

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

万劫不复 2025-02-07 13:04:01

else在case中不会神奇地使一行出现：没有行表示没有行。

要始终行，将查询（大部分）用作子查询并应用Cocece：

select coalesce(
  (SELECT 
    CASE
      WHEN start_dt < end_dt THEN end_dt 
      ELSE start_dt 
    END
  FROM test_master_table
  WHERE schema_nm='test_db'
  AND table_nm='test_table'),
  CURRENT_TIMESTAMP()) AS start_dt

The else in the case doesn't magically make a row appear: No rows means no rows.

To always get a row, use (most of) your query as a subquery and apply coalesce:

select coalesce(
  (SELECT 
    CASE
      WHEN start_dt < end_dt THEN end_dt 
      ELSE start_dt 
    END
  FROM test_master_table
  WHERE schema_nm='test_db'
  AND table_nm='test_table'),
  CURRENT_TIMESTAMP()) AS start_dt

回复收藏 0 原文

江南烟雨〆相思醉 2025-02-07 13:04:01

您也可以从带有一行的临时表中加入查询：

select coalesce(greatest(start_dt,end_dt),now()) start_dt
from (select 'dummy row') dummy_row
left join test_master_table on schema_nm='test_db' and table_nm='test_table'

You can also left join your query from an ad hoc table with a single row:

select coalesce(greatest(start_dt,end_dt),now()) start_dt
from (select 'dummy row') dummy_row
left join test_master_table on schema_nm='test_db' and table_nm='test_table'

回复收藏 0 原文

~没有更多了~