有没有比我现在做的更有效的方法从5个表中获取数据

本文关键字：5个 数据 获取 方法 有没有 有效 | 更新日期: 2023-09-27 17:49:48

所以我要做的是使用C#将累积数据放在ListView对象上，然后获得销售报告的销售额。正如你所看到的，它非常不稳定，我的代码就是这样，我主要想消除必须调用两个MySqlCommands和DataReaders的情况。如有任何建议，我们将不胜感激！

string query = "SELECT * FROM orderlist ol "+
               "INNER JOIN orderdb o "+
               "ON ol.order_ID = o.order_ID "+
               "INNER JOIN menudb m "+
               "ON ol.menu_ID = m.menu_ID "+   
               "WHERE o.order_date >= '"+fromdate.Date()+"' AND "+
                     "o.order_date <= '"+todate.Date()+"'";
command = new MySqlCommand(query, connection);
dr = command.ExecuteReader();
string[] info = new string[15];
while(dr.Read())
{
  string query2 = "SELECT r.*, s.stock_pricePerPiece FROM recipelist r "+
                  "INNER JOIN stocksdb s ON r.stock_ID = s.stock_ID "+
                  "WHERE r.menu_ID = '"+(dr["menu_ID"].ToString())+"'";
  MySqlCommand cmd2 = new MySqlCommand(query2, connection2);
  MySqlDataReader dr2 = cmd2.ExecuteReader();
  double menu_cost = 0;
  while(dr2.Read())
  {
      menu_cost += Convert.ToDouble(dr2["stock_pricePerPiece"].ToString());
  }
info[1] = (dr["order_ID"].ToString());
info[2] = (dr["order_date"].ToString());
info[3] = (dr["menu_name"].ToString());
info[4] = (menu_cost.ToString("#0.00"));
info[5] = (dr["menu_price"].ToString());
info[6] = (Convert.ToDouble(info[5]) - menu_cost).ToString("#0.00");
this.ReportList.Items.Add(new ListViewItem(new string[] { info[1] , info[2] , info[3] , info[4] , info[5] , info[6] }));
}

有没有比我现在做的更有效的方法从5个表中获取数据

是的，您可以在一个查询中完成此操作。你可以这样JOIN它们：

SELECT 
  r.* 
FROM  orderlist ol 
INNER JOIN orderdb    o ON ol.order_ID = o.order_ID
INNER JOIN menudb     m ON ol.menu_ID  = m.menu_ID
INNER JOIN recipelist r ON r.menu_ID   = m.menu_ID
INNER JOIN stocksdb   s ON r.stock_ID  = s.stock_ID 
WHERE o.order_date >= ...
  AND o.order_date <= ...;

更新：选择总menu_cost，即菜单上使用的所有库存项目的总成本：

SELECT 
  s.menu_cost,
  ...
FROM  orderlist ol 
INNER JOIN orderdb    o ON ol.order_ID = o.order_ID
INNER JOIN menudb     m ON ol.menu_ID  = m.menu_ID
INNER JOIN recipelist r ON r.menu_ID   = m.menu_ID
INNER JOIN
( 
   SELECT stock_ID, SUM(stock_pricePerPiece) menu_cost
   FROM stocksdb 
   GROUP BY stock_ID
)  s ON r.stock_ID  = s.stock_ID 
WHERE o.order_date >= ...
  AND o.order_date <= ...;

你这样做的方式可能会给你带来真正的性能问题，动态创建SQL并执行它们并不是很有效，因为它们在时间上很昂贵，你应该把这项工作留给DBMS，使用联接或视图来构建你想要的数据集，让你的数据库管理它，然后执行一条语句，你会在大量的数据中感受到差异。

如果你感兴趣，你可以生成查询的执行计划，看看它的性能如何，不知道mysql，但其他DBMS(如SQLServer(甚至为你提供了优化步骤，以更好的性能获得相同的结果。

string query = "SELECT ol.*, " + 
           "(SELECT SUM(stock_pricePerPiece) FROM recipelist r INNER JOIN stocksdb s ON r.stock_ID = s.stock_ID WHERE r.menu_ID = ol.menu_ID ) " +
           "AS stock_pricePerPiece FROM orderlist ol "+
           "INNER JOIN orderdb o "+
           "ON ol.order_ID = o.order_ID "+
           "INNER JOIN menudb m "+
           "ON ol.menu_ID = m.menu_ID "+ 
           "WHERE o.order_date >= '"+fromdate.Date()+"' AND "+
                 "o.order_date <= '"+todate.Date()+"'";

从第一个数据读取器获得menu_cost，您不需要第二个数据读取器

menu_cost =  Convert.ToDouble(dr["stock_pricePerPiece"].ToString());

以下是我根据累积的答案进行的SQL查询。我只是觉得我应该和你们分享一下。非常感谢你的回答！

SELECT 
DISTINCT o.order_ID, o.order_date, o.order_status, m.*, mc.menu_cost
FROM  orderlist ol 
INNER JOIN orderdb    o ON ol.order_ID = o.order_ID
INNER JOIN menudb     m ON ol.menu_ID  = m.menu_ID
INNER JOIN
(SELECT SUM(s.stock_pricePerPiece * r.recipe_quantity) AS menu_cost, m.menu_ID
    FROM recipelist r
        INNER JOIN stocksdb s ON r.stock_ID = s.stock_ID
        INNER JOIN menudb m ON r.menu_ID= m.menu_ID
    GROUP BY r.menu_ID
) 
mc ON m.menu_ID = mc.menu_ID
WHERE o.order_date >= '2012-12-22 00:00:00'
  AND o.order_date <= '2012-12-22 23:59:59';
相关文章：