C#中的sql命令错误

本文关键字：错误 命令 sql 中的 | 更新日期: 2023-09-27 18:24:13

我编写这段代码是为了从数据库中的两个表中检索一些信息。但当我运行它时，我会得到这个错误

选择列表中的列"Eaten_food.Cardserial"无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

代码：

 private void button8_Click(object sender, EventArgs e)
 {
        using (SqlConnection con = new SqlConnection(WF_AbsPres_Food.Properties.Settings.Default.DbConnectionString))
        {
            con.Open();
            SqlDataAdapter a = new SqlDataAdapter("SELECT Eaten_food.Cardserial , Eaten_food.Date , Eaten_food.Turn , Avb_food_count , Reserve_count from Reserve inner join Eaten_food on Reserve.Cardserial = Eaten_food.Cardserial group by Eaten_food.Date", con);
            SqlCommandBuilder comdBuilder = new SqlCommandBuilder(a);
            DataTable t = new DataTable();
            //t.Locale = System.Globalization.CultureInfo.InvariantCulture;
            a.Fill(t);
            bindingSource3.DataSource = t;
            /// bind the grid view with binding source
           Reserve_dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            Reserve_dataGridView.ReadOnly = true;
            Reserve_dataGridView.DataSource = bindingSource3;
            Reserve_dataGridView.DataSource = t;
            con.Close();
        }
    }

我该如何解决？

C#中的sql命令错误

问题出在您的sql查询上。如果使用Group By，则无法选择未按分组或未聚合的列（例如使用Min/Max/Avg/Count）。

所以你可以让它工作，例如，通过这种方式，在这里更改你的旧查询：

SELECT eaten_food.cardserial, 
       eaten_food.date, 
       eaten_food.turn, 
       avb_food_count, 
       reserve_count 
FROM   reserve 
       INNER JOIN eaten_food 
               ON reserve.cardserial = eaten_food.cardserial 
GROUP  BY eaten_food.date

至：

SELECT MIN(eaten_food.cardserial)AS Cardserial, 
       eaten_food.date, 
       MIN(eaten_food.turn) AS Turn, 
       SUM(avb_food_count) AS SumFoodCount, 
       SUM(reserve_count) AS SumReserveCount 
FROM   reserve 
       INNER JOIN eaten_food 
               ON reserve.cardserial = eaten_food.cardserial 
GROUP  BY eaten_food.date

您的SQL语句本身有问题。我会把它复制到SQL管理工作室并调试它

group的功能是创建汇总行，因此您选择的列要么需要是group-by子句的一部分，要么是sum（x）或count（x）等汇总类型的statmet

以下内容可能有效。。

SELECT  Eaten_food.Cardserial ,
        Eaten_food.Date ,
        Count(Eaten_food.Turn) ,
        sum(Avb_food_count) ,
        sum(Reserve_count)
FROM    Reserve
        INNER JOIN Eaten_food ON Reserve.Cardserial = Eaten_food.Cardserial
GROUP BY Eaten_food.Date, Eaten_food.CardSerial
相关文章：