mysql - 在单个 MySql 存储过程中按多列分组

Question

我有下面的存储过程，我用它来显示多个 ASP 图表项目的数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetChartApprovedData`(in siteValue varchar(45),
in skillValue varchar(100), in shiftValue varchar(100), in tmValue varchar(45), 
in grpmValue varchar(45), in dateValue date, in dateValue1 date)
BEGIN
SELECT count(agentlogin) AS totalApproved, shift AS Shift, skill AS Skill, tm AS TM, grpM AS GrpM
   FROM approved
   WHERE (sitevalue IS NULL
           OR site = sitevalue)
         AND (skillvalue IS NULL
               OR skill = skillvalue)
         AND (shiftvalue IS NULL
               OR shift = shiftvalue)
         AND (tmValue IS NULL
                OR tm = tmValue)
         AND (grpmValue IS NULL
                OR grpM = grpmValue)
         AND (dateValue IS NULL
                OR date BETWEEN dateValue AND dateValue1)
                group by shift, skill;
END

当我使用上面的存储过程在 ASP 图表中显示数据时，我得到以下结果

两个图表给出了相同的分组结果。我想要的是第一个图表我希望它按类次分组，第二个图表我希望它按技能分组。是否可以在不对两者使用单独的存储过程的情况下实现这一目标？请告诉我。提前致谢:)

private void GetChartData()
{
    string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    MySqlConnection con = new MySqlConnection(MyConString);
    MySqlCommand cmd = new MySqlCommand("GetChartApprovedData");
    cmd.CommandType = CommandType.StoredProcedure;
    string siteValue = null;
    DateTime? dateValue = null;
    DateTime? dateValue1 = null;
    if (ddlSite.SelectedValue != null && ddlSite.SelectedValue != "0")
    {
       siteValue = ddlSite.SelectedValue;
    }
    if (ViewState["Date"] != null && ViewState["Date"].ToString() != "0")
    {
        dateValue = DateTime.Parse(ViewState["Date"].ToString());
    }
    if (ViewState["Date1"] != null && ViewState["Date1"].ToString() != "0")
    {
        dateValue1 = DateTime.Parse(ViewState["Date1"].ToString());
    }
    cmd.Parameters.AddWithValue("siteValue", siteValue);
    cmd.Parameters.AddWithValue("dateValue", dateValue);
    cmd.Parameters.AddWithValue("dateValue1", dateValue1);
    cmd.Connection = con;
    con.Open();
    MySqlDataReader myread = cmd.ExecuteReader();
    while (myread.Read())
    {
        this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);
        Chart2.Series["Series1"].IsValueShownAsLabel = true;
        Chart2.Series["Series1"].Label = "#VALY(#PERCENT)";
        Chart2.Series["Series1"].ToolTip = "Shift: #VALX \\nCount: #VALY";
        Chart2.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;
        Chart2.Legends.Clear();
        Chart2.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
        Chart2.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
        Chart2.Series["Series1"].Color = Color.DarkOrange;


        this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);
        Chart1.Series["Series1"].IsValueShownAsLabel = true;
        Chart1.Series["Series1"].Label = "#VALY(#PERCENT)";
        Chart1.Series["Series1"].ToolTip = "Skill: #VALX \\nCount: #VALY";
        Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;
        Chart1.Series["Series1"].Color = Color.DarkOrange;
        Chart1.Series["Series1"].LabelBackColor = Color.White;
        Chart1.Legends.Clear();
        Chart1.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
        Chart1.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
   }
   con.Close();
}

Answer 1

是的，可以使用 Dynamic SQL .我们可以创建一个查询字符串，然后准备并执行它。这将允许我们指定动态列名，这是不可能直接实现的。

您还需要使用另一个参数来指定在Group By中使用哪一列

DELIMITER $$

CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetChartApprovedData`(in siteValue varchar(45),
                                   in skillValue varchar(100), 
                                   in shiftValue varchar(100), 
                                   in tmValue varchar(45), 
                                   in grpmValue varchar(45), 
                                   in dateValue date, 
                                   in dateValue1 date, 
                                   in groupByColumn varchar(64)) 
-- add extra in parameter, groupByColumn, to specify which column to group upon 

BEGIN

SET query_str = CONCAT('SELECT 
                          count(agentlogin) AS totalApproved, 
                          shift AS Shift, 
                          skill AS Skill, 
                          tm AS TM, 
                          grpM AS GrpM
                        FROM approved
                        WHERE (sitevalue IS NULL
                               OR site = sitevalue)
                          AND (skillvalue IS NULL
                               OR skill = skillvalue)
                          AND (shiftvalue IS NULL
                               OR shift = shiftvalue)
                          AND (tmValue IS NULL
                               OR tm = tmValue)
                          AND (grpmValue IS NULL
                               OR grpM = grpmValue)
                          AND (dateValue IS NULL
                               OR date BETWEEN dateValue AND dateValue1)
                        GROUP BY ', 
                        groupByColumn); -- concatenate the group by column param

-- prepare the query
PREPARE stmt FROM query_str;

-- execute the query
EXECUTE stmt;

-- Clear up
DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;