sbf123官网

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

核心提示:一些有用的sql语句实例以下是相关的操作代码

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

select id,age,Fullname from tableOne awhere 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 StartTime2005-08-08 and EndTime2006-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)

)

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章