SQL实例进阶-学习sql server2005 step by step(七)

1.master..spt_values表

比如，输出一年的月份表，输出1000以内的自然数等等。数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。

例如

输出1000以内的自然数：

select number from master..spt_values

where type='P' and 

number between 1 and 1000

--输出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
--计算一个日期所在的星期内的所有日期

declare @date datetime
 
set @date='20090423'
 
select [本周所有日期]=convert(varchar(10),dateadd(dd,a.number,@date+2-datepart(dw,@date)),120) from master..spt_values a where type='p' and number<=6
 
 
 
/*
 
本周所有日期     
 
---------- 
 
2009-04-20
 
2009-04-21
 
2009-04-22
 
2009-04-23
 
2009-04-24
 
2009-04-25
 
2009-04-26
 
 
 
（所影响的行数为 7 行）
 
*/



 

2.CHARINDEX和PATINDEX函数

CHARINDEX和PATINDEX函数常常用来在一段字符中搜索字符或者字符串。如果被搜索的字符中包含有要搜索的字符，那么这两个函数返回一个非零的整数，这个整数是要搜索的字符在被搜索的字符中的开始位数。PATINDEX函数支持使用通配符来进行搜索，然而CHARINDEX不支持通佩符。接下来，我们逐个分析这两个函数。 
如何使用CHARINDEX函数 
CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。

CHARINDEX函数调用方法如下： 
CHARINDEX   (   expression1   ,   expression2   [   ,   start_location   ]   ) 
   
Expression1是要到expression2中寻找的字符中，start_location是CHARINDEX函数开始在expression2中找expression1的位置。 
   
CHARINDEX函数返回一个正整数，返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串，那么函数整数“0”。

让我们看看下面的函数命令执行的结果： 
CHARINDEX('SQL',   'Microsoft   SQL   Server') 
   
这个函数命令将返回在“Microsoft   SQL   Server”中“SQL”的起始位置，在这个例子中，CHARINDEX函数将返回“S”在“Microsoft   SQL   Server”中的位置11。 
接下来，我们看这个CHARINDEX命令： CHARINDEX('7.0',   'Microsoft   SQL   Server   2000') 
   
在这个例子中，CHARINDEX返回零，因为字符串“7.0”   不能在“Microsoft   SQL   Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。 
   
第一个例子，假设你要显示Northwind数据库Customer表前5行联系人列的Last   Name。这是前5行数据 
ContactName 
------------------------------    
Maria   Anders 
Ana   Trujillo 
Antonio   Moreno

Thomas   Hardy 
Christina   Berglund 
   
你可以看到，CustomName包含客户的First   Name和Last   Name，它们之间被一个空格隔开。我用CHARINDX函数确定两个名字中间空格的位置。通过这个方法，我们可以分析ContactName列的空格位置，这样我们可以只显示这个列的last   name部分。这是显示Northwind的Customer表前5行last   name的记录！ 
   
select   top   5   substring(ContactName,charindex('   ',ContactName)+1   , 
len(ContactName))   as   [Last   Name]   from   Northwind.dbo.customers 
   
下面是这个命令输出的结果。 
Last   Name 
------------------------------    
Anders 
Trujillo 
Moreno 
Hardy 
Berglund 
   
          CHARINDEX函数找到First   Name和Last   Name之间的空格，所以SUBSTRING函数可以分开ContactName列，这样就只有Last   Name被选出。我在CHARINDEX函数返回的整数上加1，这样Last   Name不是从空格开始。 
   
          在第二个例子中，即如说你要计算记录中，某一个字段包含特定字符的所有记录数。CHARINDEX函数可以方便的解决你的问题。计算 Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数，选择语句类似这样： 
   
                SELECT   count(*)   from   Northwind.dbo.Customers    
                WHERE   CHARINDEX('Rd',Address)   >   0   or   CHARINDEX('Road',Address)>   1    
   
如何使用PATINDEX函数 
   
PATINDEX函数返回字符或者字符串在另一个字符串或者表达式中的起始位置，，PATINDEX函数支持搜索字符串中使用通配符，这使PATINDEX函数对于变化的搜索字符串很有价值。PATINDEX函数的命令如下：

PATINDEX   (   '%pattern%'   ,   expression   ) 
   
          Pattern是你要搜索的字符串，expression是被搜索的字符串。一般情况下expression是一个表中的一个字段，pattern的前后需要用“%”标记，除非你搜索的字符串在被收缩的字符串的最前面或者最后面。 
   
          和CHARINDEX函数一样，PATINDEX函数返回搜索字符串在被搜索字符串中的起始位置。假如有这样一个PATINDEX函数： 
   
         PATINDEX('%BC%','ABCD') 
   
          这个PATINDEX函数返回的结果是2，这和CHARINDEX函数一样。这里的%标记告诉PATINDEX函数去找字符串“BC”，不管被搜索的字符串中在“BC”的前后有多少字符！ 
          假如你想知道被搜索字符串是否由特定的字符串开始，你可以省去前面的%标记。PATINDED函数就要这样写： 
   
          PATINDEX('AB%','ABCD') 
   
          这个命令执行的结果返回1，表示搜索的字符串“AB”在被搜索的字符串中“ABCD”被找到。 
   
          使用通配符可以编辑比我以上举得简单例子复杂得多的搜索字符串。假如说你要确定一个字符串是否包含字母A和Z，还有任何数字，这个PARINDEX函数命令可能像这样： 
   
          PATINDEX('%[A,Z,0-9]%[A,Z,0-9]%[A,Z,0-9]%','XYZABC123') 
   
          注意在上面这个例子中的搜索字符部分使用了很多的通陪符。察看SQL   Server联机丛书可以获得更多关于通佩符的信息。接下来，我们用两个例子来看PATINDEX和SELECT怎么联合起来使用。 
     
          假设你想要找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”的所有记录，那么选择语句就可能是这样： 
   
                  SELECT   Description   from   Northwind.dbo.Categories 
                  WHERE   patindex('%[b,B]read%',description)   >   0 
 这里我用通配符来确定大写和小写的“b”。我在Notthwind数据库中执行这个脚本后，得到下面的结果： 
Description 
-------------------------------------------------------- 
Desserts,   candies,   and   sweet   breads 
Breads,   crackers,   pasta,   and   cereal 
   
          这是再用另外一个额外的通配符来查找一些记录的例子。这个例子是如何选出上面的查询结果中，Description字段的第二子字母是“e”的纪录。 
   
                    select   Description   from   Northwind.dbo.Categories            
                    where   patindex('%[b,B]read%',description)   >   0      
              and   patindex('_[^e]%',description)   =   1                    
   
          通过在条件语句中增加一个使用^通配符的PATINDEX函数，我们可以过滤掉“Dessert,   candies,   and   sweet   breads”这条记录。上面的查询结果只有一条记录。 
Description 
-------------------------------------------------------- 
Breads,   crackers,   pasta,   and   cereal      
   
总结 
   
          你现在可以发现CHARINDEX和PATINDEX搜索字符串时的区别了吧。PATINDEX函数支持使用通配符，可以用在很多有变化的查找中。而 CHARINDEX不可以。根据你自己不同的情况，

这两个函数对你在SQL   Server中的字符串的搜索、控制、分析很有帮助。

 

3.妙用PIVOT/UNPIVOT

概念：PIVOT提供将行转换了列的功能，UNPIVOT提供相反的功能．

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合

用处：交叉报表

基本用法：

 SELECT <non-pivoted column> ,
 
     [first pivoted column] AS <column name> ,
 
     [second pivoted column] AS <column name> ,
 
      
 
     [last pivoted column] AS <column name>
 
 FROM
 
     ( <SELECT query that produces the data> ) 
 
     AS <alias for the source query>
 
 PIVOT
 
 (
 
     <aggregation function>( <column being aggregated> )
 
 FOR 
 
 [<column that contains the values that will become column headers>] 
 
     IN ( [first pivoted column] , [second pivoted column] ,
 
       [last pivoted column] )
 
 ) AS <alias for the pivot table>
 
 <optional ORDER BY clause>



 

示例一（查询原始一个两列四行的表）：

 USE AdventureWorks ;
 GO
 SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
 FROM Production.Product
 GROUP BY DaysToManufacture


 
 

示例二（转换行列）：

select 'AverageCost' as AverageCost,* from
 (
     select DaysToManufacture, StandardCost
     from Production.Product
 ) as piv PIVOT
 (
     avg(StandardCost)
     for DaysToManufacture in ([1],[2],[3],[4])
 ) as PivotTable



 

 

示例三（查询指定几位员工在各个厂商中的订单数量）：

USE AdventureWorks;
 GO
 SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
 FROM 
 (SELECT PurchaseOrderID, EmployeeID, VendorID
 FROM Purchasing.PurchaseOrderHeader) p
 PIVOT
 (
 COUNT (PurchaseOrderID)
 FOR EmployeeID IN
 ( [164], [198], [223], [231], [233] )
 ) AS pvt
 ORDER BY VendorID
 
结果
VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5



 

 

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt，并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值（Emp1、Emp2...）的列将被称为 Employee，将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。

 示例四（UNPIVOT）：

 
--Create the table and insert values as portrayed in the previous example.
 CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
 Emp3 int, Emp4 int, Emp5 int)
 GO
 INSERT INTO pvt VALUES (1,4,3,5,4,4)
 INSERT INTO pvt VALUES (2,4,1,5,5,5)
 INSERT INTO pvt VALUES (3,4,3,5,4,4)
 INSERT INTO pvt VALUES (4,4,2,5,5,4)
 INSERT INTO pvt VALUES (5,5,1,5,5,5)
 GO
 --Unpivot the table.
 SELECT VendorID, Employee, Orders
 FROM 
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt) p
 UNPIVOT
    (Orders FOR Employee IN 
       (Emp1, Emp2, Emp3, Emp4, Emp5)
 )AS unpvt
 GO
 

 
结果：
VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5



 

**/

具体解释：

--行转 ¦C
 
drop table pvt
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
--UNPIVOT
--   (行转 ¦C的值的列名称 FOR 行转 ¦C的列名称 ªº列名称 IN 
--      (列名称, 列名称, 列名称, 列名称, 列名称)
--)AS unpvt
 
--列转 ¦æ
create table test(id int,name varchar(20),quarter int,profile int) 
insert into test values(1,'a',1,1000) 
insert into test values(1,'a',2,2000) 
insert into test values(1,'a',3,4000) 
insert into test values(1,'a',4,5000) 
insert into test values(2,'b',1,3000) 
insert into test values(2,'b',2,3500) 
insert into test values(2,'b',3,4200) 
insert into test values(2,'b',4,5500) 
 
select* from test 
 
select id,name, 
[1] as "一季度", 
[2] as "二季度", 
[3] as "三季度", 
[4] as "四季度" 
from 
test 
pivot 
( 
sum(profile) 
for quarter in 
([1],[2],[3],[4]) 
) 
as pvt 
 
--....这个其实 ¬OPIVOT的一个 语法规 ?
--pivot (
--聚合函数(列名) for 被转 ?的列名in(要显 ¥Ü出来 ªº列名)
--) k



 

4.去掉重复列

declare @t table(编号 varchar(6),名称 varchar(6),数量 int)
insert @t select '001',  'AAA',   10
union all select '001',  'AAA',   3
union all select '001',  'AAA',   50
union all select '001',  'AAA',   70
union all select '001',  'AAA',   18
union all select '002',  'BBB',   10
union all select '002',  'BBB',   20
union all select '002',  'BBB',   40
union all select '002',  'BBB',   60
union all select '002',  'BBB',   80
union all select '003',  'CCC',   10
union all select '003',  'CCC',   110
union all select '003',  'CCC',   150
union all select '003',  'CCC',   120
union all select '003',  'CCC',   130
---查看测试数据
select * from @t
---查看结果
select 
  case when not exists (select 1 from @t where 编号=a.编号 and 名称=a.名称 
and 数量<a.数量) then 编号 else '' end as 编号,
  case when not exists (select 1 from @t where 编号=a.编号 and 名称=a.名称 
and 数量<a.数量) then 名称 else '' end as 名称,
  数量     
from @t a
group by 编号,名称,数量
/*
编号     名称     数量          
------ ------ ----------- 
001    AAA    3
              10
              18
              50
              70
002    BBB    10
              20
              40
              60
              80
003    CCC    10
              110
              120
              130
              150
 
（所影响的行数为 15 行）
*/



 

5.行转列小例子

有些时候还是要用到行转列，比如下面的数据：

图一
一般的表结构大多会这么设计，通过关联查询就可以得出上面的数据(客运量就随便123了，非常时期以防恐怖分子)
不用说，大家也明白要得到下面的数据：

图二

列数不多的话一般可以这样，也是网上比较经典的写法
Select 时间,
sum(case when 线路='1号线' then 客运量 end) As '1号线' ,
sum(case when 线路='2号线' then 客运量 end) As '2号线' ,
sum(case when 线路='5号线' then 客运量 end) As '5号线' ,
......
From table Group By 时间
在SQL SERVER2005里可以用Pivot关键字来操作,如下：

declare @Str nvarchar(max)
set @str='select 时间'
select @str=@str+',['+线路+']' from #T group by 线路
set @str=@str+' FROM (
 SELECT 时间, 客运量, 线路
 FROM #T ) AS T
PIVOT ( sum(客运量) FOR 线路 IN
  ('
select @str=@str+'['+线路+'],' from #T group by 线路
set @str=left(@str,Len(@str)-1)
set @str=@str+ ')) AS thePivot
ORDER BY 时间'

declare @T1 table(date datetime,一号线 float,二号线 float, 五号线 float,十号线 float,十三号线 float,八通线 float,奥运支线 float ,机场线 float)
INSERT INTO @T1 exec(@str)
SELECT * FROM @T1
drop table #T

上面的语句如果运行提示不支持pivot关键字的话（一般SQL2000库导入到SQL2005可能出现这问题），执行下这句：EXEC sp_dbcmptlevel  数据库名称 ,90
为了方便看，字段改成中文了，其中#T表的数据就是最上面第一张图的数据(只要基础数据源组织成这样的就行)
把处理后的数据存放在表变量@T1中(当然临时表也行)，因为还要和其他表进行关联，导出一张大表，如下(表的部分列):

图三

大致就这样吧