SQL Server 技巧

SQL Server查询连续号码段的技巧

问题如下：

有一个表结构：

fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

注：第二个字段内可能是连续的数据，也许存在断点。

如何才能查询出来这样的结果，查询出连续的记录来。

类似下面这个示例：

2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

下面是一个很巧妙的解决方法：

SELECT b.fphm, MIN(b.kshm) Start_HM, MAX(b.kshm) End_HM
FROM (SELECT a.*, TO_NUMBER(a.kshm - ROWNUM) cc
FROM (SELECT *
FROM t
ORDER BY fphm, kshm) a) b
GROUP BY b.fphm, b.cc

FPHM START_HM END_HM
---------- -------- --------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

---

SQL Server 生成连续日期的方法总结

在网上开到几种生成连续日期的方法，放到这里总结一下，便于自己和大家以后参考

第一种，利用master..spt_values来生成连续日期

 declare @date datetime

  set @date='2009-02-05'

  select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number,121)

  from master..spt_values

  where type='p'

  and number< datediff(dd,@date,dateadd(mm,1,@date))

  --输出2008年至今以来的月份列表：

  create table Mon

  (

  ID int identity(1,1),

  Mon varchar(6)

  )

  GO

  DECLARE

  @BeginMonth varchar(6),

  @EndMonth varchar(6)

  SELECT

  @BeginMonth='200801',

  @EndMonth='200906'

  INSERT Mon(Mon)

  SELECT

  CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)

  FROM

  master..spt_values

  WHERE

  type='P'

  and

  DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'

  SELECT * FROM Mon

  DROP TABLE Mon

PS：这是利用表master..sp_values查询连续数字的，但是范围要在2048以内，不包括2048。

select number from

  master..spt_values

  where type='P' and number between 1 and 1000

第二种，直接生成连续日期，表temptab(date)只是临时表

Declare @mindate datetime

Declare @maxdate datetime

set @mindate  = '2010-01-01'

set @maxdate  = '2010-02-01'

;with temptab(date) as

( select @mindate

union all

select dateadd(d,1,temptab.date) as date

from temptab

where dateadd(d,1,temptab.date)<=@maxdate

)

select * from temptab

---

使用SQL语句取相关日期（当月天数、当月第一天、当月最后一天、本年最后一天、当月第一个星期）

--当月天数
select day(dateadd(ms, -3, dateadd(m, datediff(m, 0, getdate())+1, 0)))
---当月第一天
select dateadd(d, -day(getdate())+1, getdate()) 
---当月最后一天
select dateadd(d, -day(getdate()), dateadd(m, 1, getdate()))
--本年最后一天
select dateadd(d, -day(getdate()), dateadd(m, 12, getdate()))
--当月第一个星期一
select dateadd(wk, datediff(wk, '', dateadd(dd, 6 - day(getdate()), getdate())), '') 

---

SQL Server 2000/2005 分页SQL — 单条SQL语句

方法1：
适用于 SQL Server 2000/2005

SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
          (
          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
          )
ORDER BY id

方法2：
适用于 SQL Server 2000/2005

SELECT TOP 页大小 *
FROM table1
WHERE id >
          (
          SELECT ISNULL(MAX(id),0) 
          FROM 
                (
                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
                ) A
          )
ORDER BY id

方法3：
适用于 SQL Server 2005

SELECT TOP 页大小 * 
FROM 
        (
        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
        ) A
WHERE RowNumber > 页大小*(页数-1)

说明：页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

---

SQL Server 使用CTE实现递归查询

递归CTE是SQL SERVER 2005中重要的增强之一。一般我们在处理树，图和层次结构的问题时需要用到递归查询。

CTE的语法如下：

WITH CTE AS
(
    SELECT EmpId, ReportTo, FName FROM Employ WHERE EmpId=1
    UNION ALL
    SELECT emp.EmpId, emp.ReportTo, emp.FName FROM CTE JOIN Employ as emp ON CTE.EmpId=emp.ReportTo
)

 

递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。

 

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO