sql语句聚合函数SUM求和+重写重载+sum与count区别

SELECT     UserId, SUM(Case ComeLate WHEN 'True' THEN 1 ELSE 0 END) AS 迟到次数,
SUM(Case OverTimeStart WHEN '2000-01-01 00:00:00.000' THEN 0 ELSE 1 END)AS 加班次数
FROM         AttendanceRecord
GROUP BY UserId

SELECT     AttendanceRecord.UserId, Users.TrueName,Users.Position,Users.BasicWage, SUM(Case AttendanceRecord.ComeLate WHEN 'True' THEN 1 ELSE 0 END) AS 迟到次数,
SUM(Case AttendanceRecord.OverTimeStart WHEN '2000-01-01 00:00:00.000' THEN 0 ELSE 1 END)AS 加班次数,
COUNT(AttendanceRecord.UserId)AS 实际上班次数
FROM       AttendanceRecord INNER JOIN
                      Users ON AttendanceRecord.UserId = Users.UserId
GROUP BY AttendanceRecord.UserId,Users.TrueName,Users.Position,Users.BasicWage

SELECT     AttendanceRecord.UserId,Users.TrueName, Users.BasicWage, Users.Position,
SUM(Case AttendanceRecord.ComeLate WHEN 'True' THEN 1 ELSE 0 END) AS 迟到次数,
SUM(Case AttendanceRecord.OverTimeStart WHEN '2000-01-01 00:00:00.000' THEN 0 ELSE 1 END)AS 加班次数,
COUNT(AttendanceRecord.UserId)AS 实际上班次数,
sum(CASE LeaveMessage.State WHEN 1 THEN LeaveMessage.AppDays ELSE 0 END)as 请假天数
FROM     AttendanceRecord INNER JOIN
                      Users ON AttendanceRecord.UserId = Users.UserId INNER JOIN
                      LeaveMessage ON Users.UserId = LeaveMessage.UserId AND AttendanceRecord.UserId = LeaveMessage.UserId
GROUP BY AttendanceRecord.UserId,Users.TrueName, Users.BasicWage, Users.Position 

 

SELECT [UserId],[TrueName],[Position],[BasicWage]
   ,(SELECT SUM(AppDays)from LeaveMessage WHERE State=1 and UserId=Users.UserId)AS 请假天数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE OverTimeStart<>'2000-01-01 00:00:00.000' and UserId=Users.UserId) AS 加班次数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE ComeLate=1 and UserId=Users.UserId) AS 迟到次数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE UserId=Users.UserId) AS 实际上班天数
  FROM [test].[dbo].[Users]

SELECT [UserId],[TrueName],[Position],[BasicWage]
   ,(select SUM(AppDays)from LeaveMessage WHERE State=1 and UserId=Users.UserId and (CONVERT(varchar(7), LeaveTime, 120) = '2013-04'))AS 请假天数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE OverTimeStart<>'2000-01-01 00:00:00.000' and UserId=Users.UserId and (CONVERT(varchar(7), RecordTime, 120) = '2013-04')) AS 加班次数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE ComeLate=1 and UserId=Users.UserId and (CONVERT(varchar(7), RecordTime, 120) = '2013-04')) AS 迟到次数
   ,(SELECT COUNT(UserId)from AttendanceRecord WHERE UserId=Users.UserId and (CONVERT(varchar(7), RecordTime, 120) = '2013-04')) AS 实际上班天数
  FROM [test].[dbo].[Users]

 

 

 

 

 重写是对里面内容的变更,但框架是一样的输入输出.

 重载运用,这里是保留了父类的方法,并加上了新的方法.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            Class2 c2 = new Class2();
            MessageBox.Show(c2.Prefix().ToString());
        }
    }
}
//result:8

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication1
{
  public  class Class1
    {
     public  int a;
        public virtual int Prefix()
        {
            a = 1 * 3;
            return a;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
  public   class Class2:Class1
    {
      public override int Prefix()
      {
          base.Prefix();
          a  += 5;
          return a;
      }
    }
}

 

 

sum()函数和count()函数的区别：

求和用累加sum(),求行的个数用累计count()
数据库中对空值的处理：sum()不计算，count()认为没有此项