SQL

获取当月天数

SELECT 32-DAY( GETDATE()+32-DAY(getdate())

查看表用到的索引

sp_helpindex  表名

给表字段重命名 命名前字段UniqueID_001 命名后字段sys_guid_001

sp_rename 'TA_SubjectExamPaperHistory.UniqueID_001','sys_guid_001','column'

将表中的多个字段组合起来建立唯一索引

create unique nonclustered index UniqueBusinessData_uniqe on UniqueBusinessData
( UniqueBusinessID , PStatus ) ;

当一段sql或存储过程突然执行很慢的时候,清理下对应的表的统计信息.

--开窗函数统计一个人存在多次的情况下 只算一次  或者去重复数据 只保留一行会用到

 

CREATE PROC PLMS_TASubjectExamPaperHistoryAnalyze
@ExamCode VARCHAR(20)
AS

 

SELECT A.Question,COUNT(*) AS WrongNum FROM
(

 

SELECT row_number() over(partition by PostUser,Question order BY PostDate DESC) as rowid,Question,IsRight,PostUser FROM dbo.TA_SubjectExamPaperHistory
WHERE ExamCode = @ExamCode
AND IsRight=N'错误'

 

)A WHERE a.rowid = 1
GROUP BY A.Question

 

--电池可以多次扫描，但是只取最新扫描时间sm_time行对应的测试信息

select aa.sm_SmCode,aa.capacity,aa.model from (
SELECT row_number() over(partition by sm_SmCode order BY sm_time DESC) as rowid ,sm_SmCode,capacity,model FROM battsm
where sm_SmCode >=@BattCode and sm_SmCode <= @EndNo
) aa
where aa.rowid = 1