对含默认日期或零值的日期时间字段进行分组合并

Got it, let's figure out how to combine your rows into one while keeping only the meaningful values. Here's a straightforward approach using standard SQL:

Raw Data #

Visit_Date   Receipt_Time          Visit_Time           Prs_Time
2018-05-01   00:28:11.3700000      00:00:00.0000000     00:00:00.0000000
1900-01-01   00:00:00.0000000      00:37:22.0000000     00:00:00.0000000
1900-01-01   00:00:00.0000000      00:00:00.0000000     00:37:22.0000000

SQL Query #

We can use aggregate functions like MAX() to automatically pull the non-default values (since your defaults are the smallest possible values for their data types). Here's the query:

SELECT
  MAX(Visit_Date) AS Visit_Date,
  MAX(Receipt_Time) AS Receipt_Time,
  MAX(Visit_Time) AS Visit_Time,
  MAX(Prs_Time) AS Prs_Time
FROM your_table_name;

If you want to be explicit about filtering out defaults (in case there's edge cases where non-default values might be smaller), you can use a CASE statement instead:

SELECT
  MAX(CASE WHEN Visit_Date != '1900-01-01' THEN Visit_Date END) AS Visit_Date,
  MAX(CASE WHEN Receipt_Time != '00:00:00.0000000' THEN Receipt_Time END) AS Receipt_Time,
  MAX(CASE WHEN Visit_Time != '00:00:00.0000000' THEN Visit_Time END) AS Visit_Time,
  MAX(CASE WHEN Prs_Time != '00:00:00.0000000' THEN Prs_Time END) AS Prs_Time
FROM your_table_name;

Expected Result #

Visit_Date   Receipt_Time          Visit_Time           Prs_Time
2018-05-01   00:28:11.3700000      00:37:22.0000000     00:37:22.0000000

Quick Explanation #

• MAX() works here because 1900-01-01 is the earliest possible date in most databases, so the valid 2018-05-01 will be the maximum value.
• For time columns, 00:00:00.0000000 is the earliest time, so your valid non-zero times will be the maximum for their columns.
• If you ever had multiple valid values for a column (not your case here), MAX() would pick the latest one—swap it for MIN() if you need the earliest instead.

内容的提问来源于stack exchange，提问作者Muhammad Shahid