mysql分组有多列_在单个MySql存储过程上按多列分组

我有以下存储过程,我用它来显示多个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();

}

解决方法:

解决问题的一种方法是在程序中执行两个查询(一个到GROUP BY班次,一个到GROUP BY技能,并在结果中使用一个标志来指示结果是否是按班次或技能分组的数据：

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 'skill' AS type, count(agentlogin) AS totalApproved, 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 skill;

SELECT 'shift' AS type, count(agentlogin) AS totalApproved, shift AS Shift, 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;

END

然后在您的c#代码中,您将更改以下行：

this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);

至：

if (myread["Type"] == "shift") {

this.Chart2.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);

}

if (myread["Type"] == "skill") {

this.Chart1.Series["Series1"].Points.AddXY(myread["Skill"], myread["totalApproved"]);

}

标签：mysql,stored-procedures

来源： https://codeday.me/bug/20190622/1261643.html