SQL语法、关键字

1、having：

SQL Server查询第31到40条数据：select top 10 * from (select top 40 ID from A order by ID) as a order by a.ID desc select * from(select *,ROW_NUMBER() over(order by ID)as 'userID' from A) as a where a.userID between 31 and 40

2、top

SQL Server查询第31到40条数据：select top 10 * from (select top 40 ID from A order by ID) as a order by a.ID desc select * from(select *,ROW_NUMBER() over(order by ID)as 'userID' from A) as a where a.userID between 31 and 40

3、CHARINDEX模糊查询：

select *  from A where CHARINDEX(@ProjectName,LbtProjectInfo.ProjectName)>0

4、case   ....  when  .....  then    ....    when ...... then   .....  else    ..... end 

select sf.ID,sf.XMBH,sf.GCMC,sf.Title,sf.HTBH,sf.PGBH,sf.PGJS,case sf.Lbt4  when 1 then  '已派工'  when  2 then  '试验出报告' else '其它'  end  as 'Lbt4' from  dbo.SceneFlow sf where sf.Lbt4='1'

5、ROW_NUMBER() over(order by  .....)

select ROW_NUMBER() over(order by ID)as 'rownum',* from dbo.LbtProjectInfo

 select * from     (select ROW_NUMBER() over(order by ID asc) as 'rowNumber', * from LbtProjectInfo) as temp    where rowNumber between 1 and 10

相关博客：http://blog.csdn.net/fanbin168/article/details/41749509

6、查询时虚构一列：

select *,'启用' as qy  from dbo.LbtProjectInfo

SQL语法规则：

group  by使用：

select SalesOrderID,sum(orderQuty)from  SalesOrderDetailwhere  SalesOrderID in(43660,436700)group  by SalesOrderID;select  CustomerID,SalesPersonID,count(*)from SalesOrderHeaderwhere  CustomerID<=1000group by   CustomerID,SalesPersonIDorder by CustomerID,SalesPersonID;