临时表NULL行过滤：如何提取单条含有效数据的记录

Got it, let's tackle this problem together. You're working with a temp table that has NULL values in the SICK or VAC columns, and you need to pull just one valid record (no NULLs in those columns) to match your ResultsSample. Here's how to adjust your query:

Basic Fix: Restrict to One Row #

Your existing WHERE clause is already correctly filtering out rows with NULLs in SICK or VAC—the missing piece is limiting the result to a single record. The syntax depends on your database system:

For SQL Server (since you're using a # prefixed temp table): #

Add TOP 1 to your select statement:

SELECT TOP 1 * 
FROM #TempTable 
WHERE SICK IS NOT NULL AND VAC IS NOT NULL;

For MySQL/PostgreSQL/SQLite: #

Use LIMIT 1 instead:

SELECT * 
FROM #TempTable 
WHERE SICK IS NOT NULL AND VAC IS NOT NULL
LIMIT 1;

If You Need a Specific Valid Record #

If you don't want a random valid row (e.g., you need the most recent entry, or the oldest), add an ORDER BY clause to specify which row to pick. For example, if you have a CreatedDate column:

-- SQL Server example, pick the newest record
SELECT TOP 1 * 
FROM #TempTable 
WHERE SICK IS NOT NULL AND VAC IS NOT NULL
ORDER BY CreatedDate DESC;

This will ensure you get exactly one valid row that matches your expected ResultsSample, ignoring all rows with NULLs in the target columns.

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