从sql表中的一行获取最大值 [英] getting max value from a row in sql table

问题描述

嗨..



我有一个表格，其中包含按月计算的员工工资清单（每个员工数据按月和年份排成一行）..



从excel表中插入..



其中列将为empid，dept，desg，1月11日，2月11日.Mar-11



等继续......我有3年的数据..从那以后我想知道个别员工的最高工资是多少，如果对那个特定员工有任何加息，就知道日期和加息工资。



我想要根据用户在.aspx页面中给出的empid检索员工薪水的最高值..



如果有任何加息，我想要检索那个..



我怎么能比较并使用.aspx页面背后的代码（C＃）得到工资的最大值

Hi..

I have a table containing list of the employee salaries according to month wise (each employee data as a row as per the month and year wise)..

It is inserted from a excel sheet..

where the columns will be as empid,dept,desg,Jan-11,Feb-11.Mar-11

and so on it continues.. and I have that of 3 years data.. from that i want to know what is the max salary of an individual employee and know the date and hike salary if any hikes are there to that particular employee.

I want to retrieve the highest value of the salary of the employee as per the empid given by the user in the .aspx page..

If any hikes are present i want to retrieve that too..

how could i compare and get the max value of the salary using the code behind(C#) of .aspx page

推荐答案

嗨Harsha，



从评论中读完你的实际要求后，

我建议你做一些想法，

Hi Harsha,

After reading your actual requirements from comments,
I suggest some idea to do,

SELECT * FROM employeeTableName WHERE empID = 101;

在数据表中更新它并执行以下操作



empid，dept，desg，1月11日，2月11日.Mar-11 .....



从数据表中读取单元格值（1月11日，2月-11Mar-11 .....），在那里你可以找到最大值

这是代码片段你可以利用

Update it in datatable and do the below manipulation

empid,dept,desg,Jan-11,Feb-11.Mar-11 .....

Read the cell value(Jan-11,Feb-11.Mar-11 .....) from datatable and there you can find the max value
Here is the code snippet you can make use of

 private void button1_Click(object sender, EventArgs e)
        {
var maxSal = 0;
 //Sample Datatable         
DataTable dt=new DataTable();
        DataColumn dc=new DataColumn("Name",typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Desg", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("SAl1", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal2", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal3", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("Sal4", typeof(string));
            dt.Columns.Add(dc);
            dc = new DataColumn("sal5", typeof(string));
            dt.Columns.Add(dc);
            DataRow dr = dt.NewRow();
//Sample record that matches your record
            dr[0] = "RK";
            dr[1] = "Software engineer";
            dr[2] = "1000";
            dr[3] = "500";
            dr[4] = "2000";
            dr[5] = "9000";
            dr[6] = "6000";
            dt.Rows.Add(dr);

        for (int i = 0; i < dt.Rows.Count; i++)
            for (int j = 3; j < dt.Columns.Count; j++) //As per you comment, need to find max value from Jan-11(3rd column) to last column
            {
                //Now you need to store it in temp variable
                if (maxSal < Convert.ToInt32(dt.Rows[i][j].ToString()))
                {
                    maxSal = Convert.ToInt32(dt.Rows[i][j].ToString());
                }
            }
  MessageBox.Show("Maximum salary is " + maxSal);
        }

现在 maxSal 有最高工资价值。

试试这个......

希望这对你有所帮助。



问候，

RK

Now maxSal has maximum salary value.
Try this......
Hope this could help you a bit.

Regards,
RK

嘿Harsha，

为简单起见，我想你有2个任务。

1。得到最大员工的工资。

2.工资上涨。



所以，首先要为此任务创建1个临时表

SalHikes（Empid，Hike，Date）



现在使用与RK建议相同的循环代码。



假设任何员工ID只有一条记录

Hey Harsha,
For simplicity i guess you have 2 tasks.
1. get max. salary for employee.
2. get any hikes in salary.

So, first create 1 temporary table for this task
SalHikes(Empid,Hike,Date)

Now use loop in code behind same as RK suggested.

Assuming there is only one record of any employee id

var maxSal;
 maxSal=0;
 for (int j = 3; j < dt.Columns.Count ; j++)  {
   if (maxSal < dt.Rows[i][j])
   {
     //Insert hike in table
     ExecuteSQL("INSERT INTO SalHikes(Empid,Hike,Date) SELECT '"+ EmpId +"','"+ Convert.ToDecimal(dt.Rows[i][j].ToString()) - maxSal +"','"+ dt.Columns[j].ColumnName +"'")     
maxSal = Convert.ToDecimal(dt.Rows[i][j].ToString());
   }
}

现在maxSal将是最大值。员工的工资和温度。表SalHikes将存储员工的远足。

显示最大值。员工的工资和使用sql查询来获取员工的加薪并在aspx页面上显示，我假设可以有很多加薪，这就是为什么我使用了temp。用于存放远足的桌子，如果只有一次远足，那么你可以直接使用变量来存储远足和远足日期。



问候

Rakshit

Now maxSal will be the max. salary of employee, and temp. table SalHikes will store the hikes of employee.
display max. salary for the employee and use sql query to fetch salary hikes of employee and display on aspx page, i assume there can be many salary hikes thats why i have used temp. table for storing hikes, if there is only one hike then you can directly use a variable to store thr hike and hike date.

Regards
Rakshit

这篇关于从sql表中的一行获取最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！