一些有用的sql语句实例

转载 2006年06月09日 17:32:00

    Examples

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

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);

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

DataClient    12/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

 

select   DOCUMENT_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_MAIN    a

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)

2719              2

6                   9

12                  30

1                   116

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

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)

)

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

 

相关文章推荐

收藏几条有用的SQL语句

SQL SERVER 2000中各表外键名,主键名的获取(SQL2000) SELECT   外键表ID   = b.fkeyid ,  外键表名称 = object_name(b.fkeyid) ,...

收集到的有用的sql語句

  • 2008-07-02 17:53
  • 47KB
  • 下载

一些有用的sql语句

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1法二:select top 0 * into b fro...

一些有用的sql语句

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1 法二:select top 0 * into b...

Oracle几条有用的SQL语句

查找数据库中所有字段  以对应的表 select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner='' 查每个科...

SQL Server中一些有用的日期sql语句

1.一个月第一天的 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)2.本周的星期一 SELECT DATEADD(wk, DATEDIFF(wk,0,g...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)