SQL场景下如何对含时间的字符串进行排序？

To sort your messy time entry string into the desired format, we'll break the problem into three core steps: extract logical blocks from the input string, compute sort keys for each block, then sort and concatenate the blocks back together. Below are implementations for common SQL dialects.

Core Logic Overview #

Your input string contains three types of blocks:

1. Paired entries: HH:MMAM/PM IN | HH:MMAM/PM OUT
2. Single entries: HH:MMAM/PM IN
3. Fixed remark: Collection Remarks: counter

We'll sort blocks by the earliest time in each block (for paired/single entries) and push the remark to the end using a late "dummy" time.

PostgreSQL Implementation #

PostgreSQL has robust regex support to extract blocks easily:

-- Sample table with your input data
CREATE TABLE time_entries (id INT, raw_data TEXT);
INSERT INTO time_entries VALUES (1, '12:23PM IN | 12:26PM OUT 2:10PM IN 11:05AM IN 10:58AM IN | 11:00AM OUT 1:02PM IN | 1:05PM OUT 2:12PM IN | 2:25PM OUT Collection Remarks: counter 11:47AM IN | 11:49AM OUT 12:42PM IN 12:58PM IN 12:55PM IN 12:54PM IN 12:49PM IN | 2:45PM OUT');

WITH extracted_blocks AS (
    -- Extract all logical blocks using regex
    SELECT 
        id,
        UNNEST(REGEXP_MATCHES(raw_data, '(Collection Remarks: counter)|(\d{1,2}:\d{2}[AP]M IN \| \d{1,2}:\d{2}[AP]M OUT)|(\d{1,2}:\d{2}[AP]M IN)', 'g')) AS block
    FROM time_entries
),
block_sort_keys AS (
    -- Assign sortable time values to each block
    SELECT 
        id,
        block,
        CASE
            -- Push remark to the end with a late time
            WHEN block LIKE 'Collection Remarks:%' THEN '24:00:00'::time
            -- Use the first time in paired blocks for sorting
            WHEN block LIKE '%|%' THEN TO_TIMESTAMP(SUBSTRING(block FROM '^\d{1,2}:\d{2}[AP]M'), 'HH:MIAM')::time
            -- Use the entry time for single blocks
            ELSE TO_TIMESTAMP(SUBSTRING(block FROM '^\d{1,2}:\d{2}[AP]M'), 'HH:MIAM')::time
        END AS sort_time
    FROM extracted_blocks
)
-- Sort blocks and concatenate back into a single string
SELECT 
    id,
    STRING_AGG(block, ' ' ORDER BY sort_time) AS sorted_data
FROM block_sort_keys
GROUP BY id;

MySQL Implementation #

MySQL requires a recursive CTE to extract all regex matches since it lacks built-in unnesting functions:

-- Sample table with your input data
CREATE TABLE time_entries (id INT, raw_data TEXT);
INSERT INTO time_entries VALUES (1, '12:23PM IN | 12:26PM OUT 2:10PM IN 11:05AM IN 10:58AM IN | 11:00AM OUT 1:02PM IN | 1:05PM OUT 2:12PM IN | 2:25PM OUT Collection Remarks: counter 11:47AM IN | 11:49AM OUT 12:42PM IN 12:58PM IN 12:55PM IN 12:54PM IN 12:49PM IN | 2:45PM OUT');

WITH RECURSIVE extracted_blocks AS (
    -- Initial extraction of first block
    SELECT 
        id,
        raw_data AS remaining_data,
        REGEXP_SUBSTR(raw_data, '(Collection Remarks: counter)|([0-9]{1,2}:[0-9]{2}[AP]M IN \\| [0-9]{1,2}:[0-9]{2}[AP]M OUT)|([0-9]{1,2}:[0-9]{2}[AP]M IN)') AS block,
        1 AS iteration
    FROM time_entries
    UNION ALL
    -- Recursively extract remaining blocks
    SELECT 
        id,
        TRIM(SUBSTRING(remaining_data, LENGTH(block) + 1)),
        REGEXP_SUBSTR(TRIM(SUBSTRING(remaining_data, LENGTH(block) + 1)), '(Collection Remarks: counter)|([0-9]{1,2}:[0-9]{2}[AP]M IN \\| [0-9]{1,2}:[0-9]{2}[AP]M OUT)|([0-9]{1,2}:[0-9]{2}[AP]M IN)'),
        iteration + 1
    FROM extracted_blocks
    WHERE block IS NOT NULL AND TRIM(SUBSTRING(remaining_data, LENGTH(block) + 1)) != ''
),
block_sort_keys AS (
    -- Assign sortable time values
    SELECT 
        id,
        block,
        CASE
            WHEN block LIKE 'Collection Remarks:%' THEN STR_TO_DATE('23:59:59', '%H:%i:%s')
            WHEN block LIKE '%|%' THEN STR_TO_DATE(SUBSTRING_INDEX(block, ' ', 1), '%h:%i%p')
            ELSE STR_TO_DATE(SUBSTRING_INDEX(block, ' ', 1), '%h:%i%p')
        END AS sort_time
    FROM extracted_blocks
    WHERE block IS NOT NULL
)
-- Sort and concatenate
SELECT 
    id,
    GROUP_CONCAT(block SEPARATOR ' ' ORDER BY sort_time) AS sorted_data
FROM block_sort_keys
GROUP BY id;

SQL Server Implementation #

SQL Server uses a recursive CTE for block extraction and CONVERT to parse AM/PM times:

-- Sample table with your input data
CREATE TABLE time_entries (id INT, raw_data NVARCHAR(MAX));
INSERT INTO time_entries VALUES (1, N'12:23PM IN | 12:26PM OUT 2:10PM IN 11:05AM IN 10:58AM IN | 11:00AM OUT 1:02PM IN | 1:05PM OUT 2:12PM IN | 2:25PM OUT Collection Remarks: counter 11:47AM IN | 11:49AM OUT 12:42PM IN 12:58PM IN 12:55PM IN 12:54PM IN 12:49PM IN | 2:45PM OUT');

WITH RECURSIVE extracted_blocks AS (
    -- Initial block extraction
    SELECT 
        id,
        raw_data AS remaining_data,
        CASE 
            WHEN PATINDEX(N'%Collection Remarks: counter%', raw_data) = 1 THEN SUBSTRING(raw_data, 1, LEN(N'Collection Remarks: counter'))
            WHEN CHARINDEX(N'|', raw_data) > 0 THEN SUBSTRING(raw_data, 1, CHARINDEX(N' OUT ', raw_data) + 4)
            ELSE SUBSTRING(raw_data, 1, CHARINDEX(N' IN ', raw_data) + 3)
        END AS block,
        1 AS iteration
    FROM time_entries
    UNION ALL
    -- Recursive extraction of remaining blocks
    SELECT 
        id,
        TRIM(SUBSTRING(remaining_data, LEN(block) + 1)),
        CASE 
            WHEN PATINDEX(N'%Collection Remarks: counter%', TRIM(SUBSTRING(remaining_data, LEN(block) + 1))) = 1 THEN SUBSTRING(TRIM(SUBSTRING(remaining_data, LEN(block) + 1)), 1, LEN(N'Collection Remarks: counter'))
            WHEN CHARINDEX(N'|', TRIM(SUBSTRING(remaining_data, LEN(block) + 1))) > 0 THEN SUBSTRING(TRIM(SUBSTRING(remaining_data, LEN(block) + 1)), 1, CHARINDEX(N' OUT ', TRIM(SUBSTRING(remaining_data, LEN(block) + 1))) + 4)
            ELSE SUBSTRING(TRIM(SUBSTRING(remaining_data, LEN(block) + 1)), 1, CHARINDEX(N' IN ', TRIM(SUBSTRING(remaining_data, LEN(block) + 1))) + 3)
        END AS block,
        iteration + 1
    FROM extracted_blocks
    WHERE block IS NOT NULL AND TRIM(SUBSTRING(remaining_data, LEN(block) + 1)) != N''
),
block_sort_keys AS (
    -- Assign sortable time values
    SELECT 
        id,
        block,
        CASE
            WHEN block LIKE N'Collection Remarks:%' THEN CAST(N'23:59:59' AS TIME)
            WHEN block LIKE N'%|%' THEN CONVERT(TIME, SUBSTRING(block, 1, CHARINDEX(N' ', block) - 1), 100)
            ELSE CONVERT(TIME, SUBSTRING(block, 1, CHARINDEX(N' ', block) - 1), 100)
        END AS sort_time
    FROM extracted_blocks
    WHERE block IS NOT NULL
)
-- Sort and concatenate
SELECT 
    id,
    STRING_AGG(block, N' ') WITHIN GROUP (ORDER BY sort_time) AS sorted_data
FROM block_sort_keys
GROUP BY id;

Notes #

• The regex patterns are tailored to your specific input format. If your data has variations (e.g., different remark text, 24-hour time), adjust the regex and sort key logic accordingly.
• All implementations preserve the paired IN | OUT entries as single blocks, sorting them by their initial IN time.

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