如何使用SqlDataReader获取存储过程返回的分组数据？

Question

我有一个被定义为以下代码的存储过程：

CREATE PROCEDURE GetQuestionsGroupedBySection
    @survey_id INT
AS
    SELECT S.title, Q.qtext
    FROM Sections AS S INNER JOIN Questions AS Q 
         ON S.id = Q.section_id
         WHERE S.survey_id = @survey_id
         GROUP BY S.title;

相关表格的定义位置在

CREATE TABLE Questions (
    id INT IDENTITY(1,1),
    section_id INT,
    qtext NVARCHAR(300) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (section_id) REFERENCES Sections(id) ON DELETE CASCADE
);

并且

CREATE TABLE Sections (
    id INT IDENTITY(1,1),
    title NVARCHAR(200) NOT NULL,
    survey_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(id) ON DELETE CASCADE
);

现在我正在尝试从我的服务器端代码调用它，以便将分组以List的形式获取到元素类型中。

public class QuestionsBySection
{
    public string SectionTitle { get; set; }
    public List<string> SecionQuestions;
}

（或者有更好的数据结构可以使用吗？）我现在拥有的是：

    public List<QuestionsBySection> GetQuestionsAndSection (int survid)
    {
        // survid: survey id
        List<QuestionsBySection> AllQuestions = new List<QuestionsBySection>();
        using (SqlCommand cmd = new SqlCommand("GetQuestionsGroupedBySection", this._Conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@survey_id", survid);
            this._Conn.Open();
            using ( SqlDataReader dataReader = cmd.ExecuteReader() )
            {
                while ( dataReader.Read() )
                {
                    // ... 
                }
            }
            this._Conn.Close();
        }
        return AllQuestions;
    }

但我不确定如何填写// ...。有什么提示吗？

- user5648283

---

您的存储过程似乎不是有效的SQL（Q.qtext上缺少分组或聚合）。 - Joachim Isaksson

@JoachimIsaksson 我有 GROUP BY S.title;。 - user5648283

当我尝试创建存储过程时，SQL Server会给出“列'Questions.qtext'在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中”的错误提示。只有MySQL允许您在没有聚合或分组的情况下选择字段。 - Joachim Isaksson

Answer 1

Sql group by 是错误的

你在结果中有2列，但只有1列在分组中。你可以将第二列作为分组字段添加，或使用聚合函数（例如min()或max()）。

SELECT S.title, qtext=max(Q.qtext)
    FROM .....

DataReader

只需使用字段位置作为索引参数传递给GetString方法：

var sTitle = dataReader.GetString(0);
var sQuestion = dataReader.GetString(1);

- bri

Answer 2

在使用 SQL Server 时，SQL 查询无效；当使用 group by 子句时，所有选择的字段必须包含在 group by 子句中或者是聚合函数。根据您期望的结果，有几种方法可以返回 QuestionsBySection 集合。

其中一种选项是将结果集展开，然后在应用程序层上进行聚合。

SProc 应该删除 group by 子句：

SELECT S.title, Q.qtext
FROM Sections AS S INNER JOIN Questions AS Q 
    ON S.id = Q.section_id
WHERE S.survey_id = @survey_id

在检索时，需要评估每一行以查看标题是否已被使用：

var questions = new List<QuestionsBySection>();
// create sql connection
// execute command

while (dataReader.Read())
{
    string title = dataReader.GetString("title");
    string question = dataReader.GetString("qtext");

    QuestionsBySection qbs = questions.FirstOrDefault(x => x.SectionTitle.Equals(title , StringComparison.OrdinalIgnoreCase))
    if (qbs != null)
    {
        qbs.SecionQuestions.Add(question);
        continue;
    }

    qbs = new QuestionsBySection
    {
        SectionTitle = title,
        SecionQuestions = new List<string>{ question }
    }
    questions.Add(qbs);
}

有几种其他实现结果的方法，但这应该能让您更好地了解如何聚合结果。

- Metro Smurf