一些有用的sql语句实例

　　1Examples
　　=======================================

　　select id,age,Fullname from tableOne a
where a.id!=(select max(id) from tableOne b where a.age=b.age and a.FullName=b.FullName)

　　=========================================

　　delete from dbo.Schedule where

　　RoomID=29 and StartTime>'2005-08-08' and EndTime<'2006-09-01' and Remark like 'preset' and UserID=107

　　and (

　　(ScheduleID>=3177 and ScheduleID<=3202 )

　　or (ScheduleID>=3229 and ScheduleID<=3254)

　　or (ScheduleID>=3307 and ScheduleID<=3332)

　　=========================================

　　delete tableOne
where tableOne.id!=(select max(id) from tableOne b where tableOne.age=b.age and tableOne.FullName=b.FullName);

　　==========================================

　　DataClient12/23/2005 5:03:38 PM

　　select top 5

　　DOC_MAIN.CURRENT_VERSION_NO as Version, DOC_MAIN.MODIFY_DATE as ModifyDT, DOC_MAIN.SUMMARY as Summary, DOC_MAIN.AUTHOR_EMPLOYEE_NAME as AuthorName, DOC_MAIN.TITLE as Title, DOC_MAIN.DOCUMENT_ID as DocumentID,Attribute.ATTRIBUTE_ID as AttributeId, Attribute.CATALOG_ID as CatalogId,DOC_STATISTIC.VISITE_TIMES as VisiteTimes, DOC_STATISTIC.DOCUMENT_ID as DocumentID2

　　from DOC_MAIN DOC_MAIN

　　Inner join CATALOG_SELF_ATTRIBUTE Attribute on DOC_MAIN.CATALOG_ID=Attribute.CATALOG_ID

　　Left join DOC_STATISTIC DOC_STATISTIC on DOC_MAIN.DOCUMENT_ID=DOC_STATISTIC.DOCUMENT_ID

　　where (DOC_MAIN.AUTHOR_EMPLOYEE_ID = 1) and (Attribute.ATTRIBUTE_ID = 11)

　　order by VisiteTimes DESC

　　====================================

　　select top 1 DOCUMENT_ID,EMPLOYEE_NAME,COMMENT_DATE,COMMENT_VALUE

　　from dbo.DOC_COMMENT

　　where DOCUMENT_ID=19 and COMMENT_DATE = (select max(COMMENT_DATE) from DOC_COMMENT where DOCUMENT_ID=19)

　　====================================

　　select TITLE, (select top 1 EMPLOYEE_NAME

　　from dbo.DOC_COMMENT where DOCUMENT_ID=19) Commentman,

　　(select top 1 COMMENT_DATE

　　from dbo.DOC_COMMENT where DOCUMENT_ID=19) COMMENT_DATE

　　from DOC_MAIN where DOCUMENT_ID=19

　　======================================

　　alter view ExpertDocTopComment

　　as

　　selectDOCUMENT_ID, max(ORDER_NUMBER ) as lastednum

　　from dbo.DOC_COMMENT

　　group by DOCUMENT_ID

　　go

　　alter view ExpertDocView

　　as

　　select TITLE, a.AUTHOR_EMPLOYEE_ID , c.EMPLOYEE_NAME , c.COMMENT_DATE

　　from dbo.DOC_MAINa

　　left join

　　ExpertDocTopComment b

　　on

　　a.DOCUMENT_ID = b.DOCUMENT_ID

　　inner join

　　DOC_COMMENT c

　　on

　　b.DOCUMENT_ID = c.DOCUMENT_ID and

　　b.lastednum = c. ORDER_NUMBER

　　======================================

　　select a.Id ,a.WindowsUsername ,

　　0 , 1 ,

　　a.Email ,

　　case b.EnFirstName when null then a.Username else b.EnFirstName end,

　　case b.EnLastName when null then a.Username else b.EnLastName end

　　from UUMS_KM.dbo.UUMS_User a

　　left join

　　UUMS_KM.dbo.HR_Employee b

　　on

　　a. HR_EmployeeId = b.id

　　=====================================

　　列出上传文档最多的五个人的ID

　　select AUTHOR_EMPLOYEE_ID,count(AUTHOR_EMPLOYEE_ID)

　　from dbo.DOC_MAIN

　　group by AUTHOR_EMPLOYEE_ID

　　order by count(AUTHOR_EMPLOYEE_ID)

　　27192

　　69

　　1230

　　1116

　　列出上传文档最多的五个人的信息

　　select distinct AUTHOR_EMPLOYEE_ID ,AUTHOR_EMPLOYEE_NAME

　　from dbo.DOC_MAIN

　　where AUTHOR_EMPLOYEE_ID

　　in (

　　select top 5 AUTHOR_EMPLOYEE_ID

　　from dbo.DOC_MAIN

　　group by AUTHOR_EMPLOYEE_ID

　　order by count(AUTHOR_EMPLOYEE_ID)

　　)

　　=================================