SQL例子

转：http://topic.youkuaiyun.com/u/20081225/11/83ae3d98-13db-424d-bf00-23521ea38fbd.html

 

 /*************************************************/

 

--按某一字段分组取最大(小)值所在行的数据 --(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州) /* 数据如下： name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --创建表并插入数据： create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values('a', 2, 'a2(a的第二个值)') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('b', 1, 'b1--b的第一个值') insert into tb values('b', 3, 'b3:b的第三个值') insert into tb values('b', 2, 'b2b2b2b2') insert into tb values('b', 4, 'b4b4') insert into tb values('b', 5, 'b5b5b5b5b5') go --一、按name分组取val最大的值所在行的数据。 --方法1： select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name --方法2： select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val) --方法3： select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4： select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name /* name val memo ---------- ----------- -------------------- a 3 a3:a的第三个值 b 5 b5b5b5b5b5 */ --二、按name分组取val最小的值所在行的数据。 --方法1： select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name --方法2： select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val) --方法3： select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4： select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 */ --三、按name分组取第一次出现的行所在的数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name /* name val memo ---------- ----------- -------------------- a 2 a2(a的第二个值) b 1 b1--b的第一个值 */ --四、按name分组随机取一条数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 5 b5b5b5b5b5 */ --五、按name分组取最小的两个(N个)val select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name,a.val /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 a 2 a2(a的第二个值) b 1 b1--b的第一个值 b 2 b2b2b2b2 */ --六、按name分组取最大的两个(N个)val select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name , a.val /* name val memo ---------- ----------- -------------------- a 2 a2(a的第二个值) a 3 a3:a的第三个值 b 4 b4b4 b 5 b5b5b5b5b5 */ --七，如果整行数据有重复，所有的列都相同。 /* 数据如下： name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 1 a1--a的第一个值 a 3 a3:a的第三个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。 --创建表并插入数据： create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values('a', 2, 'a2(a的第二个值)') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('b', 1, 'b1--b的第一个值') insert into tb values('b', 3, 'b3:b的第三个值') insert into tb values('b', 2, 'b2b2b2b2') insert into tb values('b', 4, 'b4b4') insert into tb values('b', 5, 'b5b5b5b5b5') go select * , px = identity(int,1,1) into tmp from tb select m.name,m.val,m.memo from ( select t.* from tmp t where val = (select min(val) from tmp where name = t.name) ) m where px = (select min(px) from ( select t.* from tmp t where val = (select min(val) from tmp where name = t.name) ) n where n.name = m.name) drop table tb,tmp /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 (2 行受影响) */ --在sql server 2005中可以使用row_number函数，不需要使用临时表。 --创建表并插入数据： create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values('a', 2, 'a2(a的第二个值)') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('b', 1, 'b1--b的第一个值') insert into tb values('b', 3, 'b3:b的第三个值') insert into tb values('b', 2, 'b2b2b2b2') insert into tb values('b', 4, 'b4b4') insert into tb values('b', 5, 'b5b5b5b5b5') go select m.name,m.val,m.memo from ( select * , px = row_number() over(order by name , val) from tb ) m where px = (select min(px) from ( select * , px = row_number() over(order by name , val) from tb ) n where n.name = m.name) drop table tb /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 (2 行受影响) */

 

 /*************************************************/

 

--时间最大化、最小化 create table tb(A int, B datetime) insert into tb values(1 , '2008-1-10 ') insert into tb values(1 , '2008-2-9') insert into tb values(1 , '2008-12-1') insert into tb values(2 , '2008-5-1') insert into tb values(2 , '2008-5-2') insert into tb values(3 , '2008-5-2') go select a , min(b) MinDate , max(b) MinDate from tb group by a drop table tb /* a MinDate MinDate ----------- ---------------------------------------------- 时间最大值、最小值 -------- ------------------------------------------------------ 1 2008-01-10 00:00:00.000 2008-12-01 00:00:00.000 2 2008-05-01 00:00:00.000 2008-05-02 00:00:00.000 3 2008-05-02 00:00:00.000 2008-05-02 00:00:00.000 （所影响的行数为 3 行） */

 

  /*************************************************/

 

--处理表重复记录(查询和删除) /****************************************************************************************************************************************************** 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 where Num=@Num and Name=@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 行受影响) */

 

  /*************************************************/

 

普通行列转换 问题：假设有张学生成绩表(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 课程 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