如何从多列FIX数据表中提取单行有效字段数据？

Alright, let's figure out how to solve this. You need to grab a single row of valid data from your table—specifically the fields 8_BeginString, 9_BodyLength, 34_MsgSeqNum, and 35_MsgType—while skipping any rows with NULL values in those fields. Here's how to do it, plus how to get that exact output format you want:

Step 1: SQL Query to Filter Valid Rows #

First, we'll target rows where all four required fields are non-NULL, then pull just one of those rows. The exact syntax varies slightly by SQL dialect:

For PostgreSQL, MySQL, SQLite: #

SELECT 
    8_BeginString, 
    9_BodyLength, 
    34_MsgSeqNum, 
    35_MsgType
FROM your_table_name
WHERE 
    8_BeginString IS NOT NULL
    AND 9_BodyLength IS NOT NULL
    AND 34_MsgSeqNum IS NOT NULL
    AND 35_MsgType IS NOT NULL
LIMIT 1;

For SQL Server, Access: #

SELECT TOP 1
    8_BeginString, 
    9_BodyLength, 
    34_MsgSeqNum, 
    35_MsgType
FROM your_table_name
WHERE 
    8_BeginString IS NOT NULL
    AND 9_BodyLength IS NOT NULL
    AND 34_MsgSeqNum IS NOT NULL
    AND 35_MsgType IS NOT NULL;

Replace your_table_name with your actual table name. If there are multiple valid rows and you want to prioritize a specific one (like the highest message sequence number), add an ORDER BY 34_MsgSeqNum DESC right before the LIMIT 1/TOP 1 clause.

Step 2: Match Your Exact Output Format #

To get the formatted output with headers and separators you specified, you can concatenate results directly in SQL. Here's an example that works across most dialects:

SELECT '8_BeginString 9_BodyLength 34_MsgSeqNum 35_MsgType' AS formatted_output
UNION ALL
SELECT '------------- ------------ ------------ ----------'
UNION ALL
SELECT 
    CONCAT(8_BeginString, ' ', 9_BodyLength, ' ', 34_MsgSeqNum, ' ', 35_MsgType)
FROM your_table_name
WHERE 
    8_BeginString IS NOT NULL
    AND 9_BodyLength IS NOT NULL
    AND 34_MsgSeqNum IS NOT NULL
    AND 35_MsgType IS NOT NULL
LIMIT 1;

This will output exactly the structure you need:

8_BeginString 9_BodyLength 34_MsgSeqNum 35_MsgType
------------- ------------ ------------ ----------
FIX.4.4 486 157 AE

If your fields have inconsistent lengths and you need perfectly aligned columns, use padding functions like LPAD (PostgreSQL/MySQL) or RIGHT (SQL Server) to standardize spacing between values.

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