//1、生成随机数ROUND(((89 - 71 -1) * RAND() + 71), 0)DECLARE @Result INTDECLARE @Upper INTDECLARE @Lower INT SET @Lower = 1 SET @Upper = 10SELECT @Result = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)SELECT @Result//2、字段为空，输出默认值 isnull(wj.wjsum,0) as wjsum//数字isnull(wj.wjsum,'无') as wjsum//字符//3、GROUP BY 中Count SELECT TOP 100 PERCENT wjs_scid,COUNT (wjs_scid) AS scidsum FROM [dbo].[wj_selectresult_wj] WHERE wjs_id = 28 GROUP BY wjs_id,wjs_cid,wjs_scid //视图或多表查询用到TOP 100 PERCENT //因为SQL规则规定，如果子查询里，有order ...，就必须有TOP，所以就用SELECT TOP 100 PERCENT来限定SELECT  wjs_scid,COUNT (DISTINCT operator_id) AS scidsum FROM [dbo].[wj_selectresult_wj] WHERE wjs_id = 28 GROUP BY wjs_scid ;//分组 去重复 计数//4、固定输出列SELECT wjc.*, scidsum,wjsperc=0,wjpsum='0' FROM [dbo].[wjcontent_wj]//5查询多字段重复select group_id,app_id,operator_id from app_messages group by group_id,app_id,operator_id having count(*) > 1//6 日期+-select DateAdd(month,-1,DateAdd(dd,-1, DateAdd(yy,5,'2017-08-04')))  //+5年 -天 -一个月//7 petapoco 分页SELECT    COUNT (*)FROM    Sys_User uLEFT JOIN Sys_Organize o ON u.DepartmentId = o.IdWHERE    (        u.DeleteMark = 0        AND u.Account LIKE @0        OR u.RealName LIKE @1    )SELECT    *FROM    (        SELECT            ROW_NUMBER () OVER (ORDER BY u.SortCode) peta_rn,            u.*, o.FullName        FROM            Sys_User u        LEFT JOIN Sys_Organize o ON u.DepartmentId = o.Id        WHERE            (                u.DeleteMark = 0                AND u.Account LIKE @0                OR u.RealName LIKE @1            )    ) peta_pagedWHERE    peta_rn > @2AND peta_rn <= @3 //字符串in 操作 charindex mysql 用instr  不太好用，不推荐使用，请用 转int 方式SELECT    Id,    Name,    ParentIdFROM    HR_PostWorkTypeWHERE    1 = 1AND charindex(PostId,'9,10,0') <> 0ORDER BY    SortCode ASC//按生日得到当前年龄 SELECT dbo.HR_Person.RealName as 姓名,Gender_Name as 性别,FLOOR(datediff(DY,Birthday,getdate())/365.25) as 年龄,dbo.HR_Person.CardNum as 身份证号,dbo.HR_Person.Org_Name as 单位,MobilePhone as 联系电话,Address as 地址 FROM [dbo].[HR_Person] WHERE--备份mssql数据库declare @strfile nvarchar(1000)SET @strfile ='D:\DB\DDPT\edu_dudao'+CONVERT(varchar(100), GETDATE(), 12)+'.bak'declare @strName nvarchar(1000)SET @strName ='edu_dudao'+CONVERT(varchar(100), GETDATE(), 12)BACKUP DATABASE [edu_dudao]TO  DISK = @strfileWITH  NAME = @strName,  NOFORMAT, NOINIT, SKIP,  STATS = 5 --按月份统计数量SELECT    YEAR (HR_WorkRecord.StartTime) 年,MONTH (HR_WorkRecord.StartTime) 月,    COUNT (1) 数量FROM    HR_WorkRecordWHEREyear(HR_WorkRecord.StartTime)='2018'GROUP BY    YEAR (HR_WorkRecord.StartTime) , MONTH (HR_WorkRecord.StartTime)select sum(case month(HR_WorkRecord.StartTime) when '1' then 1 else 0 end) as 一月,sum(case month(HR_WorkRecord.StartTime) when '2' then 1 else 0 end) as 二月,sum(case month(HR_WorkRecord.StartTime) when '3' then 1 else 0 end) as 三月,sum(case month(HR_WorkRecord.StartTime) when '4' then 1 else 0 end) as 四月,sum(case month(HR_WorkRecord.StartTime) when '5' then 1 else 0 end) as 五月,sum(case month(HR_WorkRecord.StartTime) when '6' then 1 else 0 end) as 六月,sum(case month(HR_WorkRecord.StartTime) when '7' then 1 else 0 end) as 七月,sum(case month(HR_WorkRecord.StartTime) when '8' then 1 else 0 end) as 八月,sum(case month(HR_WorkRecord.StartTime) when '9' then 1 else 0 end) as 九月,sum(case month(HR_WorkRecord.StartTime) when '10' then 1 else 0 end) as 十月,sum(case month(HR_WorkRecord.StartTime) when '11' then 1 else 0 end) as 十一月,sum(case month(HR_WorkRecord.StartTime) when '12' then 1 else 0 end) as 十二月from HR_WorkRecordwhere year(HR_WorkRecord.StartTime)='2018';--统计销售额1、每年select year(ordertime) 年，sum(Total) 销售合计from订单表group by year(ordertime)2、每月select year(ordertime) 年，month(ordertime) 月,sum(Total) 销售合计from订单表group by year(ordertime)，month(ordertime3、每日select year(ordertime) 年，month(ordertime) 月,day(ordertime) 日，sum(Total) 销售合计from订单表group by year(ordertime)，month(ordertime),day(ordertime)另外每日也可以这样:select convert(char(8),ordertime,112) dt，sum(Total) 销售合计from订单表group by convert(char(8),ordertime,112)如果需要增加查询条件，在from后加where 即可。