SQL查询需求：获取员工信息及按国家的最新签证到期日（单行展示）

Got it, let's break down how to solve this problem. The main task here is to get each employee's core details along with their latest visa expiry date for every country—all in a single row, even if the number of countries/visas changes dynamically.

First, let's outline the core logic: we need to first find the most recent visa expiry date for each employee-country pair, then pivot those country-specific values into individual columns. Since the number of countries can vary, we can't hardcode column names—so we'll use dynamic SQL to handle that.

Below are solutions for the most common database systems, assuming standard relationships between your tables (adjust JOIN conditions if your foreign keys differ):

For SQL Server #

We'll use STRING_AGG to build dynamic column names and the built-in PIVOT operator to transpose country data into columns:

-- Step 1: Fetch distinct country names to build dynamic columns
DECLARE @CountryColumns NVARCHAR(MAX)
SELECT @CountryColumns = STRING_AGG(QUOTENAME(c.CountryName), ', ')
FROM (SELECT DISTINCT c.CountryName FROM COUNTRIES c JOIN Visas v ON c.CountryId = v.CountryId) AS CountryList

-- Step 2: Construct the dynamic pivot query
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @DynamicSQL = N'
SELECT 
    e.EmployeeIdno,
    e.Name,
    e.Surname,
    e.Designation,
    m.mobilizationidno,
    ' + @CountryColumns + '
FROM (
    -- Subquery to get each employee''s latest visa expiry per country
    SELECT 
        e.EmployeeIdno,
        e.Name,
        e.Surname,
        e.Designation,
        m.mobilizationidno,
        c.CountryName,
        MAX(v.VisaExpiryDate) AS LatestVisaExpiry
    FROM Employees e
    LEFT JOIN mobilization m ON e.EmployeeIdno = m.EmployeeIdno -- Link employee to their mobilization record
    LEFT JOIN Visas v ON e.EmployeeIdno = v.EmployeeIdno -- Link employee to their visas
    LEFT JOIN COUNTRIES c ON v.CountryId = c.CountryId -- Link visa to its country
    GROUP BY e.EmployeeIdno, e.Name, e.Surname, e.Designation, m.mobilizationidno, c.CountryName
) AS EmployeeVisaData
PIVOT (
    MAX(LatestVisaExpiry)
    FOR CountryName IN (' + @CountryColumns + ')
) AS PivotTable
ORDER BY e.EmployeeIdno'

-- Execute the dynamic query
EXEC sp_executesql @DynamicSQL

For MySQL #

MySQL doesn't have a built-in PIVOT operator, so we'll use GROUP_CONCAT to generate dynamic CASE statements for each country:

-- Step 1: Build dynamic CASE columns for each country
SET @CountryColumns = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'MAX(CASE WHEN c.CountryName = ''', c.CountryName, ''' THEN v.VisaExpiryDate END) AS `', c.CountryName, '`'
    )
) INTO @CountryColumns
FROM COUNTRIES c JOIN Visas v ON c.CountryId = v.CountryId;

-- Step 2: Construct the dynamic query
SET @DynamicSQL = CONCAT('
SELECT 
    e.EmployeeIdno,
    e.Name,
    e.Surname,
    e.Designation,
    m.mobilizationidno,
    ', @CountryColumns, '
FROM Employees e
LEFT JOIN mobilization m ON e.EmployeeIdno = m.EmployeeIdno
LEFT JOIN Visas v ON e.EmployeeIdno = v.EmployeeIdno
LEFT JOIN COUNTRIES c ON v.CountryId = c.CountryId
GROUP BY e.EmployeeIdno, e.Name, e.Surname, e.Designation, m.mobilizationidno
ORDER BY e.EmployeeIdno');

-- Execute the dynamic query
PREPARE stmt FROM @DynamicSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Key Notes: #

• Adjust JOIN conditions: If your tables use different foreign key names (e.g., mobilization.emp_id instead of mobilization.EmployeeIdno), update the JOIN clauses to match your schema.
• Handle NULL values: If an employee has no visa for a country, the column will show NULL. Use COALESCE (e.g., COALESCE(MAX(...), ''No Visa'')) to replace NULL with a user-friendly value.
• Filter visa types: If you need to include only specific visa types (e.g., work visas), add a JOIN to VISATYPES and a WHERE clause like vt.VisaType = ''Work'' (adjust to your actual type names).
• SQL Injection Risk: If CountryName can contain special characters or user input, ensure you properly escape values (the examples use QUOTENAME for SQL Server and backticks for MySQL to mitigate this).

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