在一个mysql存储过程中按多列分组

wtzytmuj  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(414)

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

xxslljrj1#

是的,可以使用动态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 ;

prdp8dxp

prdp8dxp2#

解决问题的一种方法是在过程中执行两个查询(一对一) 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"]);
}
gxwragnw

gxwragnw3#

如果您只想重新使用c代码大小,则可以使用madhu bhaiya提供的存储过程和下面的代码来实现所需的结果:

private void GetChartData()
    {
        string MyConString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        MySqlConnection con = new MySqlConnection(MyConString);

        //PTK: TypeOfChartValue should be change with chart type you are using (I did not know what chart library you are using in your code
        Dictionary<string, TypeOfChartValue> charts = new Dictionary<string, Chart>()
        {
            { "shift", this.Chart2 },  //PTK: the first value here is a column name to use in group by , the second is the chart to fill with resulted data. this line means that the Chart2 must be filled with data grouped by  'shift'
            { "skill", this.Chart1 }  //PTK: the first value here is a column name to use in group by , the second is the chart to fill with resulted data. this line means that the Chart1 must be filled with data grouped by  'skill'
            //PTK: you can add here as many charts as you wish
        };
        foreach (string groupby in charts.Keys)
        {
            //PTK: TypeOfChartValue should be change with chart type you are using (I did not know what chart library you are using in your code
            TypeOfChartValue chart = charts[groupby];

            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("groupByColumn", groupby);

            cmd.Connection = con;
            con.Open();
            MySqlDataReader myread = cmd.ExecuteReader();
            while (myread.Read())
            {
                chart.Series["Series1"].Points.AddXY(myread["Shift"], myread["totalApproved"]);
                chart.Series["Series1"].IsValueShownAsLabel = true;
                chart.Series["Series1"].Label = "#VALY(#PERCENT)";
                chart.Series["Series1"].ToolTip = "Shift: #VALX \\nCount: #VALY";
                chart.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;
                chart.Legends.Clear();
                chart.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;
                chart.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;
                chart.Series["Series1"].Color = Color.DarkOrange;

            }
            con.Close();
        }
    }

相关问题