一些有用的sql语句实例

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

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

selectid,age,FullnamefromtableOnea
wherea.id!=(selectmax(id)fromtableOnebwherea.age=b.ageanda.FullName=b.FullName)

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

deletefromdbo.Schedulewhere

RoomID=29andStartTime>'2005-08-08'andEndTime<'2006-09-01'andRemarklike'preset'andUserID=107

and(

  (ScheduleID>=3177andScheduleID<=3202)

 or(ScheduleID>=3229andScheduleID<=3254)

 or(ScheduleID>=3307andScheduleID<=3332)

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

deletetableOne
wheretableOne.id!=(selectmax(id)fromtableOnebwheretableOne.age=b.ageandtableOne.FullName=b.FullName);

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

DataClient   12/23/20055:03:38PM

selecttop5  

DOC_MAIN.CURRENT_VERSION_NOasVersion,DOC_MAIN.MODIFY_DATEasModifyDT,DOC_MAIN.SUMMARYasSummary,DOC_MAIN.AUTHOR_EMPLOYEE_NAMEasAuthorName,DOC_MAIN.TITLEasTitle,DOC_MAIN.DOCUMENT_IDasDocumentID,  Attribute.ATTRIBUTE_IDasAttributeId,Attribute.CATALOG_IDasCatalogId,  DOC_STATISTIC.VISITE_TIMESasVisiteTimes,DOC_STATISTIC.DOCUMENT_IDasDocumentID2

fromDOC_MAINDOC_MAIN

InnerjoinCATALOG_SELF_ATTRIBUTEAttributeonDOC_MAIN.CATALOG_ID=Attribute.CATALOG_ID

LeftjoinDOC_STATISTICDOC_STATISTIConDOC_MAIN.DOCUMENT_ID=DOC_STATISTIC.DOCUMENT_ID

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

orderbyVisiteTimesDESC

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

selecttop1DOCUMENT_ID,EMPLOYEE_NAME,COMMENT_DATE,COMMENT_VALUE

fromdbo.DOC_COMMENT

whereDOCUMENT_ID=19andCOMMENT_DATE=(selectmax(COMMENT_DATE)fromDOC_COMMENTwhereDOCUMENT_ID=19)

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

 

selectTITLE,(selecttop1EMPLOYEE_NAME

fromdbo.DOC_COMMENTwhereDOCUMENT_ID=19)Commentman,

(selecttop1COMMENT_DATE

fromdbo.DOC_COMMENTwhereDOCUMENT_ID=19)COMMENT_DATE

fromDOC_MAINwhereDOCUMENT_ID=19

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

alterviewExpertDocTopComment

as

 

select  DOCUMENT_ID,max(ORDER_NUMBER)aslastednum

fromdbo.DOC_COMMENT

groupbyDOCUMENT_ID

 

go

alterviewExpertDocView

as

selectTITLE  ,a.AUTHOR_EMPLOYEE_ID,c.EMPLOYEE_NAME,c.COMMENT_DATE

fromdbo.DOC_MAIN   a

leftjoin

ExpertDocTopCommentb

 

on

a.DOCUMENT_ID=b.DOCUMENT_ID

 

innerjoin

DOC_COMMENTc

on

b.DOCUMENT_ID=c.DOCUMENT_IDand

b.lastednum=c.ORDER_NUMBER

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

selecta.Id,a.WindowsUsername,

 0,1,

a.Email,

 

caseb.EnFirstNamewhennullthena.Usernameelseb.EnFirstNameend,

caseb.EnLastNamewhennullthena.Usernameelseb.EnLastNameend

fromUUMS_KM.dbo.UUMS_Usera

leftjoin

UUMS_KM.dbo.HR_Employeeb

on

a.HR_EmployeeId=b.id

=====================================1

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>