您的位置: 首页 > 软件教程 > MSSQL教程专题 > 一些有用的sql语句实例

一些有用的sql语句实例

8.0分
出处:网络 时间:2011-08-04

您可能感兴趣的话题: MSSQL  

核心提示:一些有用的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)

  )

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

网友评论
精品软件课程
更多 >
Adobe Flash,是Macromedi... [详细]
Dreamweaver是网页制作和管理的所见... [详细]
MySQL是一个小型关系型数据库管理系统,被... [详细]
PHP是一种 HTML 内嵌式的语言,是一种... [详细]
HTML的中文叫做“超文本标记语言”。是用来... [详细]
Dreamweaver是网页制作和管理的所见... [详细]