总结-"求一句SQL"

[1]已知表A中有ID,NAME两列.其它列不知
请输出其它列值数据,不包含ID和NAME

解:
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+name from syscolumns where object_id('A')=ID and name not in ('ID','NAME')
set @sql='select '+@sql+' from [A]'
exec(@sql)
由于@sql 申明时没有值,为NULL,所以会在以后累加列名时为NULL,故用isnull(@sql+',','')来判断,第一次时@sql 是NULL,则用空字符返回.
以后就不为NULL,返回相加以后加","的字符串;

[2]查找数据库表中第一,三,五,七...行的记录:
select px=identity(int,1,1),* into tmp from ID_NAME
/*identity用于SELECT INTO语句中,用以将标识列插入新表中*/
select * from tmp where px%2=1
drop table tmp

[3]行列转换

create table T(
ID int,
[key] nvarchar(128),
Code nvarchar(10)
)
insert into T values('1','NAME','good boy')
insert into T values('2','AGE','25')
insert into T values('3','SEX','boy')
insert into T values('4','JOB','ITER')

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when [key]='''+[key]+''' then Code else '''' end) as '+[key]
from T group by [key]
select @sql='select ID'+@sql+' from T group by ID'
exec(@sql)
drop table T



ID          AGE        JOB        NAME       SEX
----------- ---------- ---------- ---------- ----------
1                                 good boy   
2           25                               
3                                            boy
4                      ITER                  

(4 行受影响)

[4]下面是一张三十万多条用户表:

ID       姓名   省份      生日             相片      性别
1      郑某    福建     1980-01-05      true     男
2      李某    广东     1981-02-10      false    男
3      张某    辽宁     1960-01-08      true     女
4      陈某    上海     1990-09-10      false    女
5      王某    福建     1978-11-15      true     男
6      曾某    广东     1978-08-18      true     女
7      陈某    福建     1979-10-09      false    男

要求统计出如下的报表格式:

省份   男(有相片)  女(有相片)  18-20岁  21-23岁 24-30岁 31岁以上 合计
福建     3(2)      0(0)              0          0                3       0       3
广东     1(0)      1(1)              0          0                2       0       2
辽宁     0(0)      1(1)              0          0                0       1       1
上海     0(0)      1(0)              1          0                0       0       1
合计    4(2)       3(2)              1          0                5        1      7

create table ta(ID int, 姓名 varchar(10),   省份  varchar(10),生日 datetime,相片 varchar(10),性别 varchar(2))
insert ta select
1      ,'郑某','福建','1980-01-05','true','男' union select 
2      ,'李某','广东','1981-02-10','false','男' union select 
3      ,'张某','辽宁','1960-01-08','true','女' union select 
4      ,'陈某','上海','1990-09-10','false','女' union select 
5      ,'王某','福建','1978-11-15','true','男' union select 
6      ,'曾某','广东','1978-08-18','true','女' union select 
7      ,'陈某','福建','1979-10-09','false','男'
go

/**//*
省份   男(有相片)  女(有相片)  18-20岁  21-23岁 24-30岁 31岁以上 合计 
福建     3(2)      0(0)       0          0     3       0       3 
广东     1(0)      1(1)       0          0     2       0       2 
辽宁     0(0)      1(1)       0          0     0       1       1 
上海     0(0)      1(0)       1          0     0       0       1 
合计    4(2)       3(2)       1          0    5        1      7 
*/
select isnull(省份,'合计')  省份,
       [男(有相片)] = ltrim(sum(case when 性别 = '男' then 1 else 0 end))+'('+ltrim(sum(case when 相片 ='true' and 性别 = '男' then 1 else 0 end))+')',
       [女(有相片)] = ltrim(sum(case when 性别 = '女' then 1 else 0 end))+'('+ltrim(sum(case when 相片 ='true' and 性别 = '女' then 1 else 0 end))+')',
       [18-20岁] = sum(case when datediff(d,生日,getdate()) between 18*365 and 20*365 then 1 else 0 end),
       [21-23岁] = sum(case when datediff(d,生日,getdate()) between 21*365 and 23*365 then 1 else 0 end),
       [24-30岁] = sum(case when datediff(d,生日,getdate()) between 24*365 and 30*365 then 1 else 0 end),
       [31岁以上] =sum(case when datediff(d,生日,getdate()) >= 31*365 then 1 else 0 end),
        合计 = sum(1),ltrim(cast(sum(1) *100.00/(select count(1) from ta) as numeric(12,2))) +'%'

from ta 
group by 省份 with rollup

drop table ta 
/**//*
省份         男(有相片)                     女(有相片)                     18-20岁      21-23岁      24-30岁      31岁以上       合计                                                    
---------- -------------------------- -------------------------- ----------- ----------- ----------- ----------- ----------- ----------------------------------------- 
福建         3(2)                       0(0)                       0           0           3           0           3           42.86%
广东         1(0)                       1(1)                       0           0           2           0           2           28.57%
辽宁         0(0)                       1(1)                       0           0           0           1           1           14.29%
上海         0(0)                       1(0)                       0           0           0           0           1           14.29%
合计         4(2)                       3(2)                       0           0           5           1           7           100.00%

（所影响的行数为 5 行）
*/

[5]商品信息表
编码      基本单位     小包装    小包装比例      中包装      中包装比例     大包装      大包装比例
001        个          包          5           盒            10          箱            100
002        支          盒          10          板            100
003        瓶          捆          10          箱            100

单位表
编码    单位
01      个 
02      包
03      盒
04      支
05      板
06      瓶
07      捆
08      箱

库存表
编码      库存
001      208
002      160
003      186
问题：用Select语句 得到如下结果

编码    数量
001    1箱,10盒,1包,3个
002    1板,6盒
003    1箱,8捆,6瓶

说明:其中基本单位是每个商品必须有的，相当于最小单位 ,比例为 1

create table 商品信息表(编码 varchar(10), 基本单位 varchar(10), 小包装 varchar(10), 小包装比例 int, 中包装 varchar(10), 中包装比例 int, 大包装 varchar(10), 大包装比例 int)
insert into 商品信息表 values('001', '01', '02', 5 , '03', 10 , '08', 100) 
insert into 商品信息表 values('002', '04', '03', 10, '05', 100, null, null)
insert into 商品信息表 values('003', '06', '07', 10, '08', 100, null, null)
create table 库存表(编码 varchar(10), 库存 int)
insert into 库存表 values('001', 208 )
insert into 库存表 values('002', 160 )
insert into 库存表 values('003', 186 )
create table 单位表(编码 varchar(10), 单位 varchar(10))
insert into 单位表 values('01' ,     '个')  
insert into 单位表 values('02' ,     '包') 
insert into 单位表 values('03' ,     '盒') 
insert into 单位表 values('04' ,     '支') 
insert into 单位表 values('05' ,     '板') 
insert into 单位表 values('06' ,     '瓶') 
insert into 单位表 values('07' ,     '捆') 
insert into 单位表 values('08' ,     '箱')
go

select m.编码,
       case when m.大包装 is not null and n.库存/m.大包装比例 >= 1 then cast(n.库存/m.大包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.大包装) else '' end +
       case when m.大包装 is not null and (n.库存%m.大包装比例)/m.中包装比例 >= 1 then cast((n.库存%m.大包装比例)/m.中包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.中包装)
            when m.大包装 is null and n.库存/m.中包装比例 >= 1 then cast(n.库存/m.中包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.中包装)
            else '' end + 
       case when m.大包装 is not null and ((n.库存%m.大包装比例)%m.中包装比例)/小包装比例 >= 1 then cast(((n.库存%m.大包装比例)%m.中包装比例)/小包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.小包装) 
            when m.大包装 is null and (n.库存%m.中包装比例)/m.小包装比例 >= 1 then cast((n.库存%m.中包装比例)/m.小包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.小包装) 
            else '' end + 
       case when m.大包装 is not null and ((n.库存%m.大包装比例)%m.中包装比例)%小包装比例 >= 1 then cast(((n.库存%m.大包装比例)%m.中包装比例)%小包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.基本单位)
            when m.大包装 is null and (n.库存%m.中包装比例)%m.小包装比例 >= 1 then cast((n.库存%m.中包装比例)%m.小包装比例 as varchar) + (select 单位 from 单位表 where 编码 = m.基本单位)
            else '' end as 数量
from 商品信息表 m, 库存表 n where m.编码 = n.编码

drop table 商品信息表,库存表,单位表

/**//*
编码         数量    
---------- ---------
001        2箱1包3个
002        1板6盒
003        1箱8捆6瓶

（所影响的行数为 3 行）
*/

[6]用SQL语句写某一月份的日历

Use Test
go
Set Nocount On
Declare 
    @Date datetime,
    @StartDate datetime,
    @EndDate datetime,
    @FirstIndex int

Set @Date ='20080709' --输入一个日期，即可算出当月的日历

Select 
    @StartDate=Convert(char(6),@Date,112)+'01', 
    @EndDate=Dateadd(month,1,@StartDate)-1,
    @FirstIndex=Datediff(day,-1,@StartDate)%7

;With t As
(
    Select Date=Convert(int,1),Row=(@FirstIndex)/7,Col=@FirstIndex
    Union All
    Select Date=Date+1,Row=(@FirstIndex+Date)/7,Col=(Date+@FirstIndex)%7
    From t
    Where Date<=Datediff(day,@StartDate,@EndDate)
)
Select 
    [日]=Isnull(Convert(char(2),[0]),''),
    [一]=Isnull(Convert(char(2),[1]),''),
    [二]=Isnull(Convert(char(2),[2]),''),
    [三]=Isnull(Convert(char(2),[3]),''),
    [四]=Isnull(Convert(char(2),[4]),''),
    [五]=Isnull(Convert(char(2),[5]),''),
    [六]=Isnull(Convert(char(2),[6]),'')
From t
Pivot (Max(Date) For col In([0],[1],[2],[3],[4],[5],[6])) b

执行结果:

 

[7]在执行添加操作时,如果库里的ID值是由SQL语句添加的,那么需要注意并发;

一般操作是

 

INSERT INTO JSZH_MM_News (ID, Title, ArticleContent, Source, ImageUrl, Hits, InsertTime, UpdateTime, Deletetime, IsDelete)
SELECT    max( ID)+1, 'Title', 'ArticleContent', 'Source', 'ImageUrl', 0, getDate(), getDate(), getDate(), 0
FROM  JSZH_MM_News

 

这样很容易产生并发.由于操作是先获取最大ID值,再加1后才执行插入操作;

解决方案是:

 

INSERT INTO JSZH_MM_News(ID, Title, ArticleContent, Source, ImageUrl, Hits, InsertTime, UpdateTime, Deletetime, IsDelete)
SELECT    max( ID)+1, 'Title', 'ArticleContent', 'Source', 'ImageUrl', 0, getDate(), getDate(), getDate(), 0
FROM   JSZH_MM_News  with(tablockx)

 

通过tablockx加锁防止并发.如果是采用数据库主键自增就不会出现这样的情况.

 

[8]SQL之行列互变问题。

     SQL行变列: 示例数据：

Employee_Id   Zmonth   Performance_Cd    Value      

10001         200806    A                 2.00     

10001         200806    B                 2.00      

10001         200806    C                 2.00     

10001         200806    D                 2.00     

10001         200806    E                 2.00     

10001         200806    K                 0.25     

10002         200806    A                 1.25     

10002         200806    B                 2.00      

10002         200806    C                 2.00     

10002         200806    D                 2.00     

10002         200806    K                 1.00     

10017         200806    C                 0.00     

10017         200806    D                 0.00     

10017         200806    E                 0.00     

10017         200806    K                 0.00      

10024         200806    A                 -1.00      

10024         200806    B                 0.84     

10024         200806    C                 2.00      

10024         200806    D                 2.00     

10024         200806    E                 2.00     

说明：该表存储的是每个雇员在200806月度绩效考核的分数，其中 Performance_Cd表示绩效考核的不同项

目标：要将上表“旋转”成如下传统形式：

Employee_Id   Zmonth   A     B     C     D     E     K

10001            200806    2     2     2     2     2     0.25

10002            200806    1.25 2     2     2     null 1

10017            200806    null null 0     0     0     0    

 

方法：

     

Select Employee_Id,Zmonth,

          Max (Case When Performance_Cd=’A’ Then Zvalue) As A,

          Max (Case When Performance_Cd=’B’ Then Zvalue) As B,

          Max (Case When Performance_Cd=’C’ Then Zvalue) As C,

          Max (Case When Performance_Cd=’D’ Then Zvalue) As D,

          Max (Case When Performance_Cd=’E’ Then Zvalue) As E,

          Max (Case When Performance_Cd=’F’ Then Zvalue) As F

      From Performance_Zmonth_Value

      Group by Employee_Id,Zmonth

 

代码简单，逻辑简单，代码性能十分高效。如果你仍然觉得代码略显复杂，在SQL2005中有更简单的解决方案。

但这个简单，只是代码上的简单，经过查看两种写法的SQL执行计划，发现两种代码一模一样，性能上没有任何区别。

     

 SELECT Employee_Id,Zmonth,A,B,C,D,E,F,K

FROM Performance_Zmonth_Value

PIVOT ( MAX(ZVALUE) FOR Performance_Cd

               IN (A,B,C,D,E,F,K)

              )AS P;  

   

个人感觉，上面的代码不太好理解，因为没有明确的Group By

所以，如果基表中有不在Select 列表中出现的列，语句在执行时会自动把这些列加到Group By中去。

当然，你可以使用派生表以达到你想要的效果。

 

行变列:(以下数据表存储各个门店岗位编制情况,[102],[3645]等即是列名，又是分店ID)

Duty_Id [102]      [3645] [4374] [4375] [4508] [4668] [5074]

1026       5            4            4            4            4            4            4    

1043       2            1            1            1            1            0            1    

1065       1            1            1            1            1            0            1    

1074       2            2            2            2            2            2            2    

1077       1            1            1            1            1            1            1    

1079       1            1            1            1            1            1            1    

1080       27           26           24           23           20           22           22   

1081       30           28           26           25           22           22           24   

     

要将上表旋转成一下形式

Duty_Id         Store_Id         Qty

1026              102                5

1026              3645              4

1026              4374              4

1026              4375              4

 

运行以下代码即可（该代码只适合SQL2005）

Select Duty_id,Store_Id,Qty

from Employee_Counts

Unpivot(Qty for Store_Id in (

[102],[3645],[4374],[4375],[4508],[4668],[5074]

)

) as p