c# - 尝试使用 C# 执行查询时，SqlCommand 返回错误

Question

我正在从事一个项目，该项目将允许用户仅通过选择列来构建查询。

我的应用程序似乎运行良好并生成正确的查询。但是当我执行查询时，我得到以下异常

Column 'Tasks.Duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

这里没有做的是我的查询实际上包含正确的 GROUP BY 语句

这是我的应用程序生成的查询

SELECT
 [Campaign].[Title] AS [CampaignTitle]
,CASE WHEN [Task].[Duration] <= @p0 THEN @p1 WHEN [Task].[Duration] >= @p2 AND [Task].[Duration] <= @p3 THEN @p4 ELSE @p5 END AS [9pl1WbY1ZkSvSpel3g4xmw]
,COUNT(1) AS [Ib7ZS4arHkSUWhf4saEtmg]
,SUM(CASE WHEN [Task].[Duration] <= @p6 THEN @p7 ELSE @p8 END) AS [xW1GmttC6kWHsc9QIEPN9w]
FROM [Tasks] AS [Task]
INNER JOIN [Campaigns] AS [Campaign] ON [Campaign].[Id] = [Task].[CampaignId]
INNER JOIN [Users] AS [User] ON [User].[Id] = [Task].[CompletedBy]
INNER JOIN [Results] AS [Result] ON [Result].[Id] = [Task].[ResultId]
WHERE
[Result].[IsCompleted] = @p9 AND ( [User].[LastName] = @p10 OR [User].[LastName] = @p11 )
GROUP BY
[Campaign].[Title]
,CASE WHEN [Task].[Duration] <= @p13 THEN @p14 WHEN [Task].[Duration] >= @p15 AND [Task].[Duration] <= @p16 THEN @p17 ELSE @p18 END
HAVING
COUNT(1) >= @p12
ORDER BY
 [Campaign].[Title]
,CASE WHEN [Task].[Duration] <= @p19 THEN @p20 WHEN [Task].[Duration] >= @p21 AND [Task].[Duration] <= @p22 THEN @p23 ELSE @p24 END

更好的是，我尝试用参数值手动替换参数名称。然后我接受了他们的输出查询，并在 SSMS 中执行了它，并且没有任何问题。

这就是我执行查询的方式

using (SqlConnection connection = new SqlConnection(this.connectionString))
using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectCommand))
{
    try
    {
        selectCommand.Connection = connection;

        DataTable table = new DataTable();

        sqlAdapter.Fill(table);

        return table;
    }
    catch (Exception e)
    {
        throw new ReportException(string.Format("{0} {1}", e.Message, e.GetType().FullName));
    }
}

这是我用它们的值替换参数的方法

private string GetPlainQuery(SqlCommand selectCommand)
{
    string output = selectCommand.CommandText;

    for (int i = selectCommand.Parameters.Count - 1; i >= 0; i--)
    {
        var p = selectCommand.Parameters[i];

        string value = p.Value.ToString();

        if (p.SqlDbType == SqlDbType.NChar || p.SqlDbType == SqlDbType.NText || p.SqlDbType == SqlDbType.NVarChar || p.SqlDbType == SqlDbType.Text || p.SqlDbType == SqlDbType.Char || p.SqlDbType == SqlDbType.Date || p.SqlDbType == SqlDbType.DateTime || p.SqlDbType == SqlDbType.SmallDateTime)
        {
            value = string.Format("'{0}'", value.Replace("'", "''"));
        }

        output = output.Replace(p.ParameterName, value);
    }

    return output;
}

是什么导致了这个错误？我该如何解决？

已更新

这是我用值替换参数后的查询

SELECT
 [Campaign].[Title] AS [CampaignTitle]
,CASE WHEN [Task].[Duration] <= 100 THEN '<100' WHEN [Task].[Duration] >= 100 AND [Task].[Duration] <= 200 THEN 'BETWEEN 100 AND 200' ELSE '>200' END AS [9pl1WbY1ZkSvSpel3g4xmw]
,COUNT(1) AS [Ib7ZS4arHkSUWhf4saEtmg]
,SUM(CASE WHEN [Task].[Duration] <= 100 THEN 1 ELSE 0 END) AS [xW1GmttC6kWHsc9QIEPN9w]
FROM [Tasks] AS [Task]
INNER JOIN [Campaigns] AS [Campaign] ON [Campaign].[Id] = [Task].[CampaignId]
INNER JOIN [Users] AS [User] ON [User].[Id] = [Task].[CompletedBy]
INNER JOIN [Results] AS [Result] ON [Result].[Id] = [Task].[ResultId]
WHERE
[Result].[IsCompleted] = 1 AND ( [User].[LastName] = 'Yo' OR [User].[LastName] = 'Smith' )
GROUP BY
 [Campaign].[Title]
,CASE WHEN [Task].[Duration] <= 100 THEN '<100' WHEN [Task].[Duration] >= 100 AND [Task].[Duration] <= 200 THEN 'BETWEEN 100 AND 200' ELSE '>200' END
HAVING
COUNT(1) >= 0
ORDER BY
 [Campaign].[Title]
,CASE WHEN [Task].[Duration] <= 100 THEN '<100' WHEN [Task].[Duration] >= 100 AND [Task].[Duration] <= 200 THEN 'BETWEEN 100 AND 200' ELSE '>200' END

Answer 1

我的猜测是错误是由于您在选择列表中的 case 语句与您在 group by 中的不一样。我通常做的是在子查询中解析这些列，以便更容易阅读和维护外部查询。