c# - 如何计算有多少客户在 SQL 中购买了产品

Question

我有一个显示公司销售产品的 C# 应用程序。该列旁边是产品类型，最后一列是购买该产品的客户数量。这是我填充前两列的 SQL 查询：

string selectQuery;
selectQuery = "SELECT Products.ProductID, Products.ProductTypeID, ";
selectQuery = selectQuery + "Products.ProductName, Products.YearlyPremium, ProductTypes.ProductType ";
selectQuery = selectQuery + "FROM Products ";
selectQuery = selectQuery + "INNER JOIN ProductTypes ON Products.ProductTypeID = ProductTypes.ProductTypeID "

现在我需要计算出有多少客户购买了每种产品。我想我需要使用该COUNT(*)方法来获取 CustomerID，但现在确定如何将其集成到此查询中。这是架构

我用来在 listView 中显示数据的代码是这样的：

SqlConnection conn = ConnectionsManager.DatabaseConnection();
        SqlDataReader rdr = null;
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(selectQuery, conn);
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Products product = new Products(int.Parse(rdr["ProductID"].ToString()),
                                     int.Parse(rdr["ProductTypeID"].ToString()),
                                     rdr["ProductName"].ToString(),
                                     Math.Round(decimal.Parse(rdr["YearlyPremium"].ToString()), 2));
                ProductTypes productType = new ProductTypes(int.Parse(rdr["ProductTypeID"].ToString()),
                                     rdr["ProductType"].ToString());

                ListViewItem lvi = new ListViewItem(product.ProductName.ToString());
                lvi.SubItems.Add(productType.ProductType.ToString());
                \\lvi.SubItems.Add(customer.CustomerID.ToString()); <---this will be the line to display the customer count
                lvMain.Items.Add(lvi);
            }
            if (rdr != null)
                rdr.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Unsuccessful" + ex);
        }

标签： c#sql

Answer 1

尝试使用 Group by 子句。它会起作用的。 例子：

SELECT COUNT(ProductId),CustomerId
FROM Product
GROUP BY CustomerId;

这种