(1)分页：（性能效果最好的是top/max 和Row_number()两种方法）
[1]写法1，not in/top
select top pagesize * from pagetest where id not in (select top (pagesize*(pageindex-1)) id from pagetest order by id) order by id
[2]写法2，not exists（引导的子句无结果集返回）
select top pagesize * from pagetest where not exists (select 1 from (select top (pagesize*(pageindex-1)) id from pagetest order by id) a where a.id = pagetest.id) order by id
[3]写法3，max/top
select top pagesize * from pagetest where id> (select max(id) from (select top (pagesize*(pageindex-1)) id from pagetest order by id) a ) order by id
[4]写法4，row_number()
select top pagesize * from (select row_number()over(order by id)rownumber,* from pagetest) a where rownumber between startIndex and endIndex
[5]分页函数（先循环插入100000000条数据然后再分页）
create table #FYTable (id int identity(1,1),title nvarchar(20),con nvarchar(20))
declare @title nvarchar(300),
@con nvarchar(300)
declare @start int,@end int
set @start=0
set @end=1000000
set @title='Olive'
set @con='I love'
while @start<@end
begin
insert into #FYTable select @title,@con
set @start=@start+1
end
select * from #FYTable
create procedure dbo.FenYe
@count int,@yeshu int
as
select top(@yeshu) * from #FYTable where id not in (select top(@count*@yeshu) id from #FYTable)
(2)排序：排序函数Rank()
select SName,Sage,SSex from (select SName,Sage,Ssex,Rank() over(order by sage) as rank from Student) as a where a.rank > 1 and a.rank < 9;
(3)表行列转换：
数据表行列转换（case when then）
create table Factory( FID int, FDepartment nvarchar(20), FMeterial nvarchar(20), FNumber int)
insert into Factory values(1,'工厂1','材料1',1);
insert into Factory values(1,'工厂2','材料1',1);
insert into Factory values(1,'工厂2','材料2',2);
insert into Factory values(1,'工厂2','材料3',1);
insert into Factory values(1,'工厂3','材料3',1);
insert into Factory values(1,'工厂2','材料1',2);
insert into Factory values(1,'工厂3','材料2',1);
select * from Factory
select FDepartment,
SUM(case FMeterial when '材料1' then FNumber else 0 end) as 材料1,
SUM(case FMeterial when '材料2' then FNumber else 0 end) as 材料2,
SUM(case FMeterial when '材料3' then FNumber else 0 end) as 材料3
from Factory group by FDepartment
--行列转换函数Pivot
create table Sell
(
[Year] int , [Quarter] nvarchar(2) , Quantity int
)
insert into Sell values(2011,'Q1',12)
insert into Sell values(2011,'Q2',13)
insert into Sell values(2011,'Q2',14)
insert into Sell values(2011,'Q3',15)
insert into Sell values(2011,'Q2',12)
insert into Sell values(2011,'Q3',13)
insert into Sell values(2011,'Q4',15)
insert into Sell values(2011,'Q4',17)
insert into Sell values(2011,'Q4',11)
insert into Sell values(2012,'Q3',13)
insert into Sell values(2012,'Q4',15)
insert into Sell values(2012,'Q2',17)
insert into Sell values(2012,'Q4',11)
insert into Sell values(2012,'Q3',13)
insert into Sell values(2012,'Q1',15)
insert into Sell values(2012,'Q1',17)
insert into Sell values(2012,'Q4',11)
select * from Sell
select * from Sell Pivot(sum(Quantity) for [Quarter] in (Q1,Q2,Q3,Q4) )as p
select [Year], SUM(case when [Quarter]='Q1' then Quantity end) as Q1,
SUM(case when [Quarter]='Q2' then Quantity end) as Q2,
SUM(case when [Quarter]='Q3' then Quantity end) as Q3,
SUM(case when [Quarter]='Q4' then Quantity end) as Q4
from Sell group by [Year]
--case when then 用例
drop table T
go
create table T (TID int identity(1,1) not null primary key,Quantity int not null,ProductID int not null,[Type] int not null)
go
insert into T select 2,12,1
insert into T select 6,12,2
insert into T select 3,10,2
insert into T select 8,10,1
insert into T select 2,12,1
select * from T
go
--统计每种商品有多少件
select ProductID,SUM (case when [Type]=1 then Quantity else -Quantity end) from T group by ProductID
--使用case when 为表的新列填充不同的数据
alter table T add Descriptions nvarchar(20) null
go
update T set Descriptions= case when [Type]=1 then '现存'
when [Type]=2 then '售出'
else '未知'
end
go
--怎么把这样一个表
--year month amount
--1991 1 1.1
--1991 2 1.2
--1991 3 1.3
--1991 4 1.4
--1992 1 2.1
--1992 2 2.2
--1992 3 2.3
--1992 4 2.4
--查成这样一个结果
--year m1 m2 m3 m4
--1991 1.1 1.2 1.3 1.4
--1992 2.1 2.2 2.3 2.4
create table tb_Test1
([year] int,[month] int,amount float)
insert into tb_Test1
select 1991,1,1.1
union select 1991,2,1.2
union select 1991,3,1.3
union select 1991,4,1.4
union select 1992,1,1.1
union select 1992,2,1.2
union select 1992,3,1.3
union select 1992,4,1.4
select * from tb_Test1t
--根据月份的不同分为不同的字段，然后再按照年份分组
select [year],sum(case [MONTH] when 1 then amount else 0 end) as m1,sum(case [MONTH] when 2 then amount else 0 end) as m2,sum(case [MONTH] when 3 then amount else 0 end) as m3,sum(case [MONTH] when 4 then amount else 0 end) as m4 from tb_Test1 group by [year]
--有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value。这道题的SQL语句怎么写？
create table tb_A(keys int,value varchar(20))
create table tb_B(keys int,value varchar(20))
insert into tb_A values(1,'aa'),(2,'ab'),(3,'ac')
insert into tb_B values(1,'aa'),(2,'a'),(3,'a')
方法一--先将更新B中value的值更新为A中value的值，同时限定B中的keys=A.Keys 同时B.value<>A.value（不等于）
update tb_B set value=(select a.value from tb_A a where a.keys=tb_B.keys)
where tb_B.keys in(select tb_B.keys from tb_B,tb_A where tb_A.keys=tb_B.keys and tb_A.value<>tb_B.value)
select * from tb_B
select * from tb_A
delete from tb_A
delete from tb_B
方法二--先删除A，B一样的部分，然后再根据两表中Keys 的相等进行更新
update tb_B set tb_B.value=s.value from (select * from tb_A except select * from tb_B) as s where tb_B.keys=s.Keys
--原表:
--courseid coursename score
--1 java 70
--2 oracle 90
--3 xml 40
--4 jsp 30
--5 servlet 80
--为了便于阅读,查询此表后的结果显式如下(及格分数为60):
--courseid coursename score mark
--1 java 70 pass
--2 oracle 90 pass
--3 xml 40 fail
--4 jsp 30 fail
--5 servlet 80 pass
create table tb_B(id int identity(1,1),coursename varchar(30),score int )
insert into tb_B values('java',70),('oracle',90),('xml',40),('jsp',30),('servlet',80)
select * from tb_B
--case when 的使用
select id,coursename,score, case when score > 60 then 'pass' else 'fail' end as mark from tb_B
--原表:
--id proid proname
--1 1 M
--1 2 F
--2 1 N
--2 2 G
--3 1 B
--3 2 A
--查询后的表:
--id pro1 pro2
--1 M F
--2 N G
--3 B A
create table C(id int,proid int,proname varchar(2))
insert into C
values(1,1,'M'),(1,2,'F'),(2,1,'N'),(2,2,'G'),(3,1,'B'),(3,2,'A')
select * from C
select id,(select proname
from C c1
where proid=1 and c.id=c1.id ) as pro1,
(select proname from C c2 where proid=2 and c2.id=c.id ) as pro2
from C c group by id
--如下表a
--列 a1 a2
--记录 1 a
-- 1 b
-- 2 x
-- 2 y
-- 2 z
--用select能选成以下结果吗？
--1 ab
--2 xyz
create table D (a1 varchar(2),a2 varchar(2))
insert into D values ('1','a'),('1','b'),('2','x'),('2','y'),('2','z')
Create table D1 (id varchar(2),value varchar(2))
Alter table D1 alter column value varchar(20)
insert into D1 select Distinct(a1),'' from D
declare @id varchar(2),@value varchar(20)
declare LianJie cursor for select * from D
open LianJie
fetch next from LianJie into @id,@value
while @@FETCH_STATUS=0
begin
update D1 set value=value+@value where id=@id
fetch next from LianJie into @id,@value
end
close LianJie
deallocate LianJie
select * from D
select * from D1
——3. 表内容：
--2005-05-09 胜
--2005-05-09 胜
--2005-05-09 负
--2005-05-09 负
--2005-05-10 胜
--2005-05-10 负
--2005-05-10 负
--如果要生成下列结果, 该如何写sql语句?
-- 胜 负
--2005-05-09 2 2
--2005-05-10 1 2
create table E (dt varchar(20),shengfu nvarchar(20))
insert into E values('2012-9-15','胜'),('2012-9-15','胜'),('2012-9-15','负'),('2012-9-15','负'),('2012-9-16','胜'),('2012-9-16','胜'),('2012-9-16','负')
select * from E
select dt, SUM(case when shengfu='胜' then 1 else 0 end) as 胜,SUM(case when shengfu='负' then 1 else 0 end) as 负 from E group by dt
--表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
create table E1 (A int ,B int,C int)
insert into E1 values(1,3,5),(6,4,2),(8,7,9)
select * from E1
select case when A>B then A else B end as AB,case when B>C then B else C end as BC from E1
table1
月份 部门业绩
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门 部门名称
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门部门名称 一月份 二月份 三月份
01 国内业务一部 10 null null
02 国内业务二部 10 8 null
03 国内业务三部 null 5 8
04 国际业务部 null null 9
create table #F([month] nvarchar(20), bumen nvarchar(20), yeji int)
insert into #F values('一月份','01',10),('一月份','02',10),('一月份','03',5),('二月份','02',8),('二月份','04',9),('三月份','03',8)
create table #F1(bumen nvarchar(20),bumenmingcheng nvarchar(20))
insert into #F1 values('01','国内业务一部'),('02','国内业务二部'),('03','国内业务三部'),('04','国际业务部')
select * from #F1
select f.bumen ,f1.bumenmingcheng,sum(case when f.[month]='一月份' then f.yeji else null end) as 一月份,
sum(case when f.[month]='二月份' then f.yeji else null end) as 二月份,
sum(case when f.[month]='三月份' then f.yeji else null end) as 三月份 from #F f,#F1 f1 where f.bumen=f1.bumen group by f.bumen,f1.bumenmingcheng
drop table #Str
create table #Str(id int identity(1,1),str11 varchar(20), [type] int )
insert into #Str values('how',1),('are',1),('you',1),('fine',2),('thank',2),('you',2)
select * from #Str
create table #Str1(ss nvarchar(100),id int )
insert into #Str1 select Distinct '',([type]) from #Str
select * from #Str1
declare @id1 int,@str1 nvarchar(100)
declare StrCursor cursor for select str11,[type] from #Str
open StrCursor
fetch next from StrCursor into @str1,@id1
while @@FETCH_STATUS=0
begin
update #Str1 set ss=ss+@str1 whereid=@id1
fetch next from StrCursor into @str1,@id1
end
close StrCursor
deallocate StrCursor
select * from #Str1
--Post
create Table Post(id int identity(1,1) primary key,title nvarchar(200),content text,CreateDate nvarchar(30))
--Comments
create Table Comments(Cid int identity(1,1) primary key,id int,content text,CreateDate nvarchar(30))
insert into Post select '我从远方赶来','我为她而狂野，我为你来看我不顾一切',cast(GETDATE() as nvarchar)
insert into Comments values
(1,'不虚此行呀',CAST(getdate() as nvarchar)),
(1,'这一个不能停留太久的世界',cast(getdate() as nvarchar)),
(1,'无可取代',cast(getdate() as nvarchar))
select * from Comments
create table #Ts (id int,com nvarchar(max))
insert into #Ts select Distinct(id),'' from Comments
declare @ii int,@com nvarchar(max)
declare TsCursor cursor for select id ,content from Comments
open TsCursor
fetch next from TsCursor into @ii,@com
while @@FETCH_STATUS=0
begin
update #Ts set com=com+@com whereid=@ii
fetch next from TsCursor into @ii,@com
end
close TsCursor
deallocate TsCursor