SQL 语句提高篇（一）

--查询出各（某）表字段的属性
[code=SQL]
--sql server 2000
SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid 
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='要查询的表'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder

/*
表名    表说明 字段序号 字段名    标识 主键    类型    占用字节数 长度 小数位数 允许空  默认值      字段说明
------- ----- -------  -------- ---- ------- ------ ------- --------------- ------ ---------- ----------
authors       1        au_id          √     id      11     11      0                         
              2        au_lname              varchar 40     40      0                         
              3        au_fname              varchar 20     20      0                         
              4        phone                 char    12     12      0              ('UNKNOWN')
              5        address               varchar 40     40      0       √                
              6        city                  varchar 20     20      0       √                
              7        state                 char    2      2       0       √                
              8        zip                   char    5      5       0       √                
              9        contract              bit     1      1       0                         
（所影响的行数为 9 行）
*/

 
--修改一个字段里的内容

06财经报/NJ-Q-W-6.2.2-001周前集会管理办法
06财经报/NJ-Q-W-4.2.3-004文印管理制度
06财经报/NJ-Q-W-5.5.1-001组织权责管理办法

通过什么语句达到：这样效果
06财经/NJ-Q-W-6.2.2-001周前集会管理办法
06财经/NJ-Q-W-4.2.3-004文印管理制度
06财经/NJ-Q-W-5.5.1-001组织权责管理办法
update tb set lj=replace(lj,'财经报','财经')

--同一表中二列值互换
update xxx set A=B,B=A

--对2个列同时分组的查询 
rq(日期)    bh(编号)  sl(数量)  dj(单价)
2008/10/17  102030      2          40
2008/10/17  102231      4          5
2009/04/12  102030      6          20
2009/05/05  201020      10          8
 求每年中每种产品销售情况???
( 编号的前2位数代表 产品 类型)
select
  left(rq,4) as 年份,
  bh 产品编号,
  sum(sl) 销售数量,
  sum(sl*dj) as 销售额
from 表
group by bh

select c.cost_center,c.name,b.jibengongzi  from employee as a ,salary_200808 as b,
department as c ,position as d
where
a.cost_center_xref_department_id=c.department_id and a.employee_id=b.employee_id
 and a.position_id=d.position_id
and  d.department_id=c.department_id
select * from employee
update employee set cost_center_xref_department_id=4 where employee_id=16

select * from department

--
表table有3个字段id, type, num分别代表每笔货物的标识，类型，数量；请写出sql语句，
可以得到各种type货物中num值最高的数据；

if not object_id('tb') is null
    drop table tb
Go
Create table tb([bh] nvarchar(3),[name] nvarchar(2),[num] int)
Insert tb
select N'001',N'依利',1000 union all
select N'002',N'三鹿',5000 union all
select N'003',N'依利',7000 union all
select N'004',N'蒙牛',1000 union all
select N'005',N'三鹿',2000 union all
select N'006',N'依利',6000 union all
select N'007',N'蒙牛',2000 union all
select N'008',N'依利',8000
Go
Select * from tb

select bh,name,num
from tb t
where not exists(select 1 from tb where name=t.name and num>t.num)

select *
from @tb a
where num=(select max(num) from @tb where name=a.name)
/**

select * from tb a
where num = (select top 1 num
from tb
where type=a.type
order by num desc)

select type,max(num) from tb group by type

select type as type,max(num) as maximum from testTable1 group by type

--多维数据库分析

http://topic.csdn.net/u/20081127/15/d916bf00-fd27-4d39-9d7b-0d0437efad0c.html
现有5个表,最前面的一列都是自动编号)
一.分类表
  分类ID,  分类名称
  1        一类
  2        二类
  .............
二.物资表
  物资ID,  物资名称,  物资单价,  分类ID
  1        固化剂      20        2
  2        塑料桶      10        1
  3        加热板      35        2
  ............
三.部门表
  部门ID,  部门名称
  1          A部门
  2          B部门
  ........
四.领用表
  领用ID,  领用日期,  物资ID, 领用数量 领用部门ID
  1      2008-10-20    2        20        1
  2      2008-11-2    1        30        3
  3      2008-11-25    2        40        2
  ........
五.计划表
  计划ID,  部门ID,  月计划金额,  计划月份
  1          1        2000      2008-10-1
  2          2        1000      2008-10-1
  3          3        3000      2008-10-1
  .......
  7          1        2100      2008-11-1
  8          2        1100      2008-11-1
  9          3        3100      2008-11-1
  ......

计划表中,每月会输入当月计划金额,以便和实际领用金额比较,计算出差值,做成本控制.请SQL高手帮忙写一下,非常感谢

要求结果1: 某年某月领用额和差额
'------------------------------------------------------------
                xxxx年x月物资领用量
            A部门      B部门  ....
一类          x￥        x￥
二类          x￥        x￥
....          ...        ...
合计金额      x￥        x￥
计划金额      x￥        x￥
差额          x￥        x￥

'要求结果2:  指定日期内的领用量
'-------------------------------------------------
          xxxx年x月x日---xxxx年x月x日物资领用量
            A部门      B部门  ....
一类          x￥        x￥
二类          x￥        x￥
....          ...        ...
合计金额      x￥        x￥
---------------------------------------------------
create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
insert into 分类表 values(3 , '三类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
insert into 物资表 values(3 , '加热板' , 35 , 3 )
create table 部门表(部门ID int, 部门名称 varchar(10))
insert into 部门表 values(1 , 'A部门')
insert into 部门表 values(2 , 'B部门')
insert into 部门表 values(3 , 'C部门')
create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int)
insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1)
insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3)
insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2)
insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3)
insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)
insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1)
create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime)
insert into 计划表 values(1 , 1 , 2000 , '2008-10-1')
insert into 计划表 values(2 , 2 , 1000 , '2008-10-1')
insert into 计划表 values(3 , 3 , 3000 , '2008-10-1')
insert into 计划表 values(7 , 1 , 2100 , '2008-11-1')
insert into 计划表 values(8 , 2 , 1100 , '2008-11-1')
insert into 计划表 values(9 , 3 , 3100 , '2008-11-1')

declare @sql varchar(4000)
set @sql='select b.分类名称'
select @sql=@sql+',max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'
from (select distinct 部门名称 from 部门表) a
set @sql=@sql+' from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称'
print @sql
exec(@sql)

--打印出来的SQL静态语句
select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称

--结果如下：
--分类名称 A部门 B部门 C部门
--二类     1200  0    600
--三类     2100  1400 0
--一类     200   0   

create table 分类表(分类ID int , 分类名称 varchar(10))
insert into 分类表 values(1 , '一类')
insert into 分类表 values(2 , '二类')
insert into 分类表 values(3 , '三类')
create table 物资表(物资ID int, 物资名称 varchar(10), 物资单价 int, 分类ID int)
insert into 物资表 values(1 , '固化剂' , 20 , 2 )
insert into 物资表 values(2 , '塑料桶' , 10 , 1 )
insert into 物资表 values(3 , '加热板' , 35 , 3 )
create table 部门表(部门ID int, 部门名称 varchar(10))
insert into 部门表 values(1 , 'A部门')
insert into 部门表 values(2 , 'B部门')
insert into 部门表 values(3 , 'C部门')
create table 领用表(领用ID int, 领用日期 datetime, 物资ID int, 领用数量 int, 领用部门ID int)
insert into 领用表 values(1 , '2008-10-20', 2 , 20 , 1)
insert into 领用表 values(2 , '2008-11-2' , 1 , 30 , 3)
insert into 领用表 values(3 , '2008-11-25', 3 , 40 , 2)
insert into 领用表 values(4 , '2008-11-26', 2 , 50 , 3)
insert into 领用表 values(5 , '2008-11-27', 1 , 60 , 1)
insert into 领用表 values(6 , '2008-11-27', 3 , 60 , 1)
create table 计划表(计划ID int, 部门ID int, 月计划金额 int, 计划月份 datetime)
insert into 计划表 values(1 , 1 , 2000 , '2008-10-1')
insert into 计划表 values(2 , 2 , 1000 , '2008-10-1')
insert into 计划表 values(3 , 3 , 3000 , '2008-10-1')
insert into 计划表 values(7 , 1 , 2100 , '2008-11-1')
insert into 计划表 values(8 , 2 , 1100 , '2008-11-1')
insert into 计划表 values(9 , 3 , 3100 , '2008-11-1')

declare @sql varchar(4000)
set @sql='if (object_id(''temptd1'')) is not null
drop table temptd1 '
set @sql=@sql+'
if object_id(''temptd2'') is not null
drop table temptd2 '
set @sql=@sql+'
if object_id(''temptd3'') is not null
drop table temptd3 '
set @sql=@sql+'
if object_id(''temptd4'') is not null
drop table temptd4 '
set @sql=@sql+'
select * into temptd1 from'
set @sql=@sql+'
(select b.分类名称'
select @sql=@sql+',
max(case 部门名称 when '''+部门名称+''' then 消耗金额 else 0 end)'+'['+部门名称+']'
from (select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1 '

set @sql=@sql+'
select * into temptd2 from(select ''合计金额'' as 分类名称'
select @sql=@sql+',
sum('+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd1) 表2 '

set @sql=@sql+'
select * into temptd3 from(select ''计划金额'' as 分类名称'
select @sql=@sql+',
sum(case 部门名称 when '''+部门名称+''' then 月计划金额 else 0 end) ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+'
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3 '

set @sql=@sql+'
select * into temptd4 from(select ''差额'' as 分类名称'
select @sql=@sql+',
(temptd3.'+部门名称+'-temptd2.'+部门名称+') ['+部门名称+']'
from(select distinct 部门名称 from 部门表) a
set @sql=@sql+' from temptd2,temptd3) 表4'
set @sql=@sql+'

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)'
print @sql
exec(@sql)

--打印出来的SQL静态语句
if (object_id('temptd1')) is not null
drop table temptd1
if object_id('temptd2') is not null
drop table temptd2
if object_id('temptd3') is not null
drop table temptd3
if object_id('temptd4') is not null
drop table temptd4
select * into temptd1 from
(select b.分类名称,
max(case 部门名称 when 'A部门' then 消耗金额 else 0 end)[A部门],
max(case 部门名称 when 'B部门' then 消耗金额 else 0 end)[B部门],
max(case 部门名称 when 'C部门' then 消耗金额 else 0 end)[C部门]
from (select 分类名称,部门名称,领用数量*物资单价 as 消耗金额
from 领用表,物资表,分类表,部门表
where 物资表.物资ID=领用表.物资ID
and 分类表.分类ID=物资表.分类ID
and 部门表.部门ID=领用表.领用部门ID) b group by b.分类名称) 表1
select * into temptd2 from(select '合计金额' as 分类名称,
sum(A部门) [A部门],
sum(B部门) [B部门],
sum(C部门) [C部门] from temptd1) 表2
select * into temptd3 from(select '计划金额' as 分类名称,
sum(case 部门名称 when 'A部门' then 月计划金额 else 0 end) [A部门],
sum(case 部门名称 when 'B部门' then 月计划金额 else 0 end) [B部门],
sum(case 部门名称 when 'C部门' then 月计划金额 else 0 end) [C部门]
from 部门表,计划表 where 计划表.部门ID=部门表.部门ID) 表3
select * into temptd4 from(select '差额' as 分类名称,
(temptd3.A部门-temptd2.A部门) [A部门],
(temptd3.B部门-temptd2.B部门) [B部门],
(temptd3.C部门-temptd2.C部门) [C部门] from temptd2,temptd3) 表4

select * from temptd1
union all
(select * from temptd2)
union all
(select * from temptd3)
union all
(select * from temptd4)

（所影响的行数为 3 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

（所影响的行数为 1 行）

--结果如下：
--分类名称 A部门 B部门 C部门
--二类     1200  0    600
--三类     2100  1400 0
--一类     200   0    500
--合计金额 3500  1400 1100
--计划金额 4100  2100 6100
--差额     600   700  5000

---------------------------------------------------------------
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74   84   94
张三 74   83   93
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

---------------------------------

/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/

--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程 when '语文' then 分数 else 0 end) 语文,
  max(case 课程 when '数学' then 分数 else 0 end) 数学,
  max(case 课程 when '物理' then 分数 else 0 end) 物理,
  cast(avg(分数*1.0) as decimal(18,2)) 平均分,
  sum(分数) 总分
from tb
group by 姓名

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb   

------------------
------------------

/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select * from
(
 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
 union all
 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程   分数
---- ------ ------
李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33
张三 总分   250.00
------------------
*/

select * from
(
 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
 union all
 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
 union all
 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
 union all
 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
 union all
 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb
---------------------------------------------------------------

有一表t1 保存每次访问网站IP访问数据如
id  FromIP    AddTime
1 
我查查询的结果为 每天的独立IP 总数
--
select count(distinct FromIP)
from t1
where convert(varchar(10),AddTime ,120) ='2008-01-01'--条件
--
select convert(varchar(10),addtime,120) [天] , count(1) [多少独立IP] from tb group by convert(varchar(10),addtime,120)
--
select convert(varchar(10),AddTime ,120) ev_day,count(distinct FromIP)
from t1
group by convert(varchar(10),AddTime ,120)

---------------------------------------------------------------
用SQL语句来备份数据库,如何做?
substring

datediff
select * from table1
where datediff(day,insertdate,getdate()) = 3

-------------------------------------------
表 account(userid,username)
userid  username
1        zhangshan
2        lishi
3        wangwu
4        liliu
4        liqi

查询结果    :
1        zhangshan
2        lishi
3        wangwu
4        liliu

请各位高手帮帮忙     
--> --> (Andy)生成测试数据 2008-10-19
Set Nocount On
declare @accoun table([userid] int,[username] nvarchar(9))
Insert @accoun
select 1,N'zhangshan' union all
select 2,N'lishi' union all
select 3,N'wangwu' union all
select 4,N'liliu' union all
select 4,N'liqi'

Select * From @accoun a Where username=(Select Top 1 username From @accoun Where userid=a.userid)

/*
userid      username
----------- ---------
1           zhangshan
2           lishi
3           wangwu
4           liliu

*/

-----------------------
如何用存储过程实现在表的分页浏览，传入参数pageindex和pagesize,
http://topic.csdn.net/u/20081021/12/f42039c7-fc93-44e4-b96c-92800b997ac0.html
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
        newsid, topic, ntime, hits
      FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC

----------------------------SQL查询列转行(多表查询,并分组)  -----------------------------------------
http://topic.csdn.net/u/20081027/12/4f803ea0-00d2-4bd6-8853-3b9968d95a81.html
问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHEREid=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id

-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT
        id
    FROM @t
)A
OUTER APPLY(
    SELECT
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

------------基础查询统计-------------------------------------------------------------
表a：
-----------
姓名
-----------
张三
李四
王五
马六
林七
朱八
--------------
表B:
--------------
姓名  金额  货币
--------------
张三    1    1
李四    2    1
王五    1    2
马六    1    3
张三    1    3
张三    2    1
--------------
通过数据表得到视图C

-------------------------------------------
姓名      货币1汇总      货币2汇总    货币3汇总
-------------------------------------------
张三        3                        1
李四        2
王五                        1
马六                                      1
林七
朱八
--------------------------------------------
总计        5            1          2
--------------------------------------------

要求说明：
1、表A中的姓名列不管是否有货币收付发生，均需出现在视图C中
2、表B中的货币收付记录按照表A的列进行分币种汇总，每个币种在视图C中为单独一列
3、视图C中增加一行为总计行，并按货币进行小计
--> liangCK小梁 于2008-10-13
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (姓名 NVARCHAR(4))
INSERT INTO #tb1
SELECT '张三' UNION ALL
SELECT '李四' UNION ALL
SELECT '王五' UNION ALL
SELECT '马六' UNION ALL
SELECT '林七' UNION ALL
SELECT '朱八'
--> liangCK小梁 于2008-10-13
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (姓名 NVARCHAR(4),金额 INT,货币 INT)
INSERT INTO #tb2
SELECT '张三','1','1' UNION ALL
SELECT '李四','2','1' UNION ALL
SELECT '王五','1','2' UNION ALL
SELECT '马六','1','3' UNION ALL
SELECT '张三','1','3' UNION ALL
SELECT '张三','2','1'

--SQL查询如下:

SELECT ISNULL(a.姓名,'合计') AS 姓名,
       SUM(CASE WHEN 货币=1 THEN 金额 ELSE 0 END) AS [货币1金额],
       SUM(CASE WHEN 货币=2 THEN 金额 ELSE 0 END) AS [货币2金额],
       SUM(CASE WHEN 货币=3 THEN 金额 ELSE 0 END) AS [货币3金额]
FROM #tb1 AS a
 LEFT OUTER JOIN
   #tb2 AS b
 ON a.姓名=b.姓名
GROUP BY a.姓名
WITH ROLLUP

/*
姓名   货币1金额       货币2金额       货币3金额
---- ----------- ----------- -----------
张三   3           0           1
朱八   0           0           0
李四   2           0           0
林七   0           0           0
王五   0           1           0
马六   0           0           1
合计   5           1           2

(7 行受影响)

*/

---
select * , 工资 = (select max(工资) from tb where 部门ID = t.部门ID) from tb t
select * from tb where 工资=（select max(工资) from tb ）

[b]select 姓名,max(工资) as 工资,部门 from 部门 group by 部门
select * from tb a
where not exists (select 1 from tb where 部门=a.部门 and 员工<>a.员工 and 工资>a.工资)

select 部门,max(工资) as 最高工资
from 工资表
group by 部门

----------------------------------------------------------
用CTE处理表重复数据的更新和删除
http://blog.csdn.net/roy_88/archive/2008/07/26/2715965.aspx
处理表重复记录(查询和删除)_整理贴4
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/

--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/

--2、删除重复记录有大小关系时,保留大或小其中一个记录

--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)

select * from #T

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/

--II、Name相同ID保留最大的一条记录:

方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)

方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)

select * from #T
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/

--3、删除重复记录没有大小关系时，处理重复值

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#

truncate table #T--清空表

insert #T select * from #    --把临时表#插入到表#T中

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin
    set rowcount @con;
    delete #T whereNum=@NumandName=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor

--查看结果
select * from #T
/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

表结构：
Companies(单位表)
------------------
id
companyName

DepartCategories(单位分类)
---------------------------
id
categoryName
companyID (外键--单位表)

DepartMents(部门表)
--------------------
id
departMentName
departCategoryID(外键--单位分类)

car (汽车表)
------------
id
carName
carNumber
departMentID(外键--部门表)

maintenaces(汽车维修记录)
---------------------------
id        主键
carid      外键
repairdate 报修日期
price      金额

目前:select id,carid,repairdate,price from maintenaces
返回结果:

id          carid      repairdate              price
----------- ----------- ----------------------- ---------------------------------------
1          1          2008-07-20 00:00:00.000 250.00
2          2          2008-05-09 00:00:00.000 10.00
3          4          2007-10-09 00:00:00.000 20.00
4          2          2008-10-09 00:00:00.000 20.00
7          1          2008-06-21 00:00:00.000 0.00
9          1          2008-08-02 00:00:00.000 0.00
10          1          2008-06-21 00:00:00.000 800.00
11          2          2008-05-22 00:00:00.000 0.00
12          1          2008-07-23 00:00:00.000 0.00

------------------------------------------------------------------------
1.如何用一个存储过程实现上述功能？
我想统计单车(查询一个车)全部车的年,半年,季,月的维修记录费用汇总报表，结构如下：
http://topic.csdn.net/u/20080923/10/c685d537-b96e-4c42-8ed5-1462ed7b608e.html
序号  汽车名称  汽车牌号 使用部门  总计金额
年,半年,季,月的维修记录费用汇总,在一个存储过程中，这些参数根据条件动态变化，如何实现，
如按季或月或半年，前提年参数一定要有
 2。我用上面的maintenaces表测试如下

declare @year char(4)
set @year='2008'
declare @Month char(1)
set @Month='6'

select c.carName 汽车名称,c.carnumber 汽车牌号,d.departmentName 使用部门,sum(m.price) as 金额总计
from
  Maintenaces m inner join cars c on  m.carid = c.id
inner join departMents d on c.departMentID  =  d.id
inner join DepartCategories dc on d.departCategoryID = dc.id
inner join Companies cp on dc.companyID = cp.id
where YEAR(m.repairDate) = @year and
      Month(m.repairDate) = @Month
group by c.carname,c.carnumber,d.departmentName

显示

汽车名称      汽车牌号    使用部门  金额总计
--------------------------------------------
大轿车        晋C10695    汽车队    800.00

可将上面的参数@Month设置为10，查询为空

汽车名称      汽车牌号    使用部门  金额总计
--------------------------------------------
declare @year char(4)
set @year='2008'
declare @Month char(1)
set @Month='6'

select c.carName 汽车名称,c.carnumber 汽车牌号,d.departmentName 使用部门,sum(m.price) as 金额总计
from
  Maintenaces m inner join cars c on  m.carid = c.id
inner join departMents d on c.departMentID  =  d.id
where YEAR(m.repairDate) = @year and
      Month(m.repairDate) = @Month
group by c.carname,c.carnumber,d.departmentName

---
Companies(单位表) 
DepartCategories(单位分类)
DepartMents(部门表)
car (汽车表)
maintenaces(汽车维修记录)

-----------------------------------------------------
http://topic.csdn.net/u/20090110/22/97c412d0-f90e-4ab2-8aed-4765e3a6ac21.html
SQLSERVEER基础语法知识
having子句是在组上放置条件的
看例子
select   orderid sum(orderMoney) from sales where orderid between 3 and 123 group by orderid  having sum(orderMoney)>5
group by得到了一个记录的集合
然后通过having子句，再在这个集合上做筛选
----------------------------------------------------