网学网为需要SQL SERVER的朋友们搜集整理了一些有用的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)
)
=================================
