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 SalesOrderDetail
where SalesOrderID in(43660,436700)
group by SalesOrderID;
select CustomerID,SalesPersonID,count(*)
from SalesOrderHeader
where CustomerID<=1000
group by CustomerID,SalesPersonID
order by CustomerID,SalesPersonID;