SQL中如何为每个特定值选取单条记录？多表查询合并实现

当然可以把这些条件合并成一个查询！而且用窗口函数的方式能很清晰地实现你要的逻辑，同时保证每个符合要求的MediaID只返回对应表2里的Top1记录。

推荐方案（适用于支持窗口函数的数据库：SQL Server、MySQL 8+、PostgreSQL等） #

用CTE（公共表表达式）结合ROW_NUMBER()窗口函数来实现，逻辑清晰且性能稳定：

WITH RankedMediaRecords AS (
    SELECT 
        t2.*,
        -- 按MediaID分组，每组内按NewIndex降序排名
        ROW_NUMBER() OVER (PARTITION BY t2.MediaID ORDER BY t2.NewIndex DESC) AS RecordRank
    FROM Table2 t2
    -- 关联表1，获取对应的CurrentIndex和isUsing状态
    INNER JOIN Table1 t1 
        ON t2.MediaID = t1.MediaID
    WHERE 
        -- 筛选表1中isUsing为null或false的记录
        (t1.isUsing IS NULL OR t1.isUsing = 0) -- 注意：如果isUsing是bit类型，false对应0；如果是字符串类型，改成'false'
        -- 筛选表2中NewIndex大于表1CurrentIndex的记录
        AND t2.NewIndex > t1.CurrentIndex
)
-- 取每个MediaID排名第一的记录
SELECT * 
FROM RankedMediaRecords 
WHERE RecordRank = 1;

针对不支持窗口函数的老版本数据库（比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用NOT EXISTS子查询来实现相同逻辑：

SELECT t2.*
FROM Table2 t2
INNER JOIN Table1 t1 
    ON t2.MediaID = t1.MediaID
WHERE 
    (t1.isUsing IS NULL OR t1.isUsing = 0)
    AND t2.NewIndex > t1.CurrentIndex
    -- 确保当前记录是该MediaID下NewIndex最大的符合条件的记录
    AND NOT EXISTS (
        SELECT 1 
        FROM Table2 t2_dup 
        WHERE t2_dup.MediaID = t2.MediaID 
          AND t2_dup.NewIndex > t2.NewIndex
          AND t2_dup.NewIndex > (SELECT CurrentIndex FROM Table1 WHERE MediaID = t2_dup.MediaID)
    );

在C#中执行的注意事项 #

你只需要把上面的SQL语句作为命令文本，使用对应的数据库连接对象（比如SqlConnection for SQL Server、MySqlConnection for MySQL）创建DbCommand，然后执行并读取结果即可。举个简单的SQL Server示例：

string sqlQuery = @"WITH RankedMediaRecords AS (
    SELECT 
        t2.*,
        ROW_NUMBER() OVER (PARTITION BY t2.MediaID ORDER BY t2.NewIndex DESC) AS RecordRank
    FROM Table2 t2
    INNER JOIN Table1 t1 
        ON t2.MediaID = t1.MediaID
    WHERE 
        (t1.isUsing IS NULL OR t1.isUsing = 0)
        AND t2.NewIndex > t1.CurrentIndex
)
SELECT * FROM RankedMediaRecords WHERE RecordRank = 1;";

using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // 按需处理每条返回的记录，比如读取字段值
                int table2Id = reader.GetInt32(reader.GetOrdinal("Table2ID"));
                // ...其他字段处理
            }
        }
    }
}

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