经典sql小代码

Posted on 2011-07-28 17:44 大瓜佬阅读(293) 评论(0) 收藏举报

生成10000笔资料,可以是任意一数字

 1 --SQL2005
 2 
 3 CREATE TABLE #t (id int IDENTITY(1,1) PRIMARY KEY,nums int)
 4 GO
 5 
 6 INSERT INTO #t
 7 SELECT abs(CHECKSUM(NEWID())%100000)
 8 GO 10000   ----生成10000笔资料,可以是任意一数字
 9 
10 SELECT * FROM #t
11 GO
12 DROP TABLE #t
13 GO

日期转换参数

 1 select CONVERT(varchar, getdate(), 120 )
 2 2004-09-12 11:06:08
 3 
 4 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
 5 20040912110608
 6 
 7 select CONVERT(varchar(12) , getdate(), 111 )
 8 2004/09/12
 9 
10 select CONVERT(varchar(12) , getdate(), 112 )
11 20040912
12 
13 select CONVERT(varchar(12) , getdate(), 102 )
14 2004.09.12
15 
16 其它我不常用的日期格式转换方法:
17 
18 select CONVERT(varchar(12) , getdate(), 101 )
19 09/12/2004
20 
21 select CONVERT(varchar(12) , getdate(), 103 )
22 12/09/2004
23 
24 select CONVERT(varchar(12) , getdate(), 104 )
25 12.09.2004
26 
27 select CONVERT(varchar(12) , getdate(), 105 )
28 12-09-2004
29 
30 select CONVERT(varchar(12) , getdate(), 106 )
31 12 09 2004
32 
33 select CONVERT(varchar(12) , getdate(), 107 )
34 09 12, 2004
35 
36 select CONVERT(varchar(12) , getdate(), 108 )
37 11:06:08
38 
39 select CONVERT(varchar(12) , getdate(), 109 )
40 09 12 2004 1
41 
42 select CONVERT(varchar(12) , getdate(), 110 )
43 09-12-2004
44 
45 select CONVERT(varchar(12) , getdate(), 113 )
46 12 09 2004 1
47 
48 select CONVERT(varchar(12) , getdate(), 114 )
49 11:06:08.177

行转列

  1 /*
  2 问题：假设有张学生成绩表(tb)如下:
  3 姓名 课程 分数
  4 张三 语文 74
  5 张三 数学 83
  6 张三 物理 93
  7 李四 语文 74
  8 李四 数学 84
  9 李四 物理 94
 10 想变成(得到如下结果)： 
 11 姓名 语文 数学 物理 
 12 ---- ---- ---- ----
 13 李四 74   84   94
 14 张三 74   83   93
 15 -------------------
 16 */
 17 
 18 create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
 19 insert into tb values('张三' , '语文' , 74)
 20 insert into tb values('张三' , '数学' , 83)
 21 insert into tb values('张三' , '物理' , 93)
 22 insert into tb values('李四' , '语文' , 74)
 23 insert into tb values('李四' , '数学' , 84)
 24 insert into tb values('李四' , '物理' , 94)
 25 go
 26 
 27 --SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
 28 select 姓名 as 姓名 ,
 29   max(case 课程 when '语文' then 分数 else 0 end) 语文,
 30   max(case 课程 when '数学' then 分数 else 0 end) 数学,
 31   max(case 课程 when '物理' then 分数 else 0 end) 物理
 32 from tb
 33 group by 姓名
 34 
 35 --SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
 36 declare @sql varchar(8000)
 37 set @sql = 'select 姓名 '
 38 select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
 39 from (select distinct 课程 from tb) as a
 40 set @sql = @sql + ' from tb group by 姓名'
 41 exec(@sql) 
 42 
 43 --SQL SERVER 2005 静态SQL。
 44 select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
 45 
 46 --SQL SERVER 2005 动态SQL。
 47 declare @sql varchar(8000)
 48 select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
 49 set @sql = '[' + @sql + ']'
 50 exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
 51 
 52 ---------------------------------
 53 
 54 /*
 55 问题：在上述结果的基础上加平均分，总分，得到如下结果：
 56 姓名 语文 数学 物理 平均分 总分 
 57 ---- ---- ---- ---- ------ ----
 58 李四 74   84   94   84.00  252
 59 张三 74   83   93   83.33  250
 60 */
 61 
 62 --SQL SERVER 2000 静态SQL。
 63 select 姓名 姓名,
 64   max(case 课程 when '语文' then 分数 else 0 end) 语文,
 65   max(case 课程 when '数学' then 分数 else 0 end) 数学,
 66   max(case 课程 when '物理' then 分数 else 0 end) 物理,
 67   cast(avg(分数*1.0) as decimal(18,2)) 平均分,
 68   sum(分数) 总分
 69 from tb
 70 group by 姓名
 71 
 72 --SQL SERVER 2000 动态SQL。
 73 declare @sql varchar(8000)
 74 set @sql = 'select 姓名 '
 75 select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
 76 from (select distinct 课程 from tb) as a
 77 set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
 78 exec(@sql) 
 79 
 80 --SQL SERVER 2005 静态SQL。
 81 select m.* , n.平均分 , n.总分 from
 82 (select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
 83 (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
 84 where m.姓名 = n.姓名
 85 
 86 --SQL SERVER 2005 动态SQL。
 87 declare @sql varchar(8000)
 88 select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
 89 exec ('select m.* , n.平均分 , n.总分 from
 90 (select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 
 91 (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
 92 where m.姓名 = n.姓名')
 93 
 94 drop table tb    
 95 
 96 ------------------
 97 ------------------
 98 
 99 /*
100 问题：如果上述两表互相换一下：即表结构和数据为：
101 姓名 语文 数学 物理
102 张三 74　　83　　93
103 李四 74　　84　　94
104 想变成(得到如下结果)： 
105 姓名 课程 分数 
106 ---- ---- ----
107 李四 语文 74
108 李四 数学 84
109 李四 物理 94
110 张三 语文 74
111 张三 数学 83
112 张三 物理 93
113 --------------
114 */
115 
116 create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
117 insert into tb values('张三',74,83,93)
118 insert into tb values('李四',74,84,94)
119 go
120 
121 --SQL SERVER 2000 静态SQL。
122 select * from
123 (
124  select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
125  union all
126  select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
127  union all
128  select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
129 ) t
130 order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
131 
132 --SQL SERVER 2000 动态SQL。
133 --调用系统表动态生态。
134 declare @sql varchar(8000)
135 select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
136 from syscolumns 
137 where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
138 order by colid asc
139 exec(@sql + ' order by 姓名 ')
140 
141 --SQL SERVER 2005 动态SQL。
142 select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
143 
144 --SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
145 
146 --------------------
147 /*
148 问题：在上述的结果上加个平均分，总分，得到如下结果：
149 姓名 课程   分数
150 ---- ------ ------
151 李四 语文   74.00
152 李四 数学   84.00
153 李四 物理   94.00
154 李四 平均分 84.00
155 李四 总分   252.00
156 张三 语文   74.00
157 张三 数学   83.00
158 张三 物理   93.00
159 张三 平均分 83.33
160 张三 总分   250.00
161 ------------------
162 */
163 
164 select * from
165 (
166  select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 
167  union all
168  select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
169  union all
170  select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
171  union all
172  select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
173  union all
174  select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
175 ) t
176 order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
177 
178 drop table tb

刷新页面返回顶部