普通行列转换假设有张学生成绩表(t)如下Name Subject Result 张三 语文 73 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94想变成 姓名 语文 数学 物理 张三 73 83 93 李四 74 84 94create table #t ( Name varchar(10) , Subject varchar(10) , Result int )insert into #t(Name , Subject , Result) values('张三','语文','73') insert into #t(Name , Subject , Result) values('张三','数学','83') insert into #t(Name , Subject , Result) values('张三','物理','93') insert into #t(Name , Subject , Result) values('李四','语文','74') insert into #t(Name , Subject , Result) values('李四','数学','83') insert into #t(Name , Subject , Result) values('李四','物理','93')declare @sql varchar(8000) set @sql = 'select Name as ' + '姓名' select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']' from (select distinct Subject from #t) as a set @sql = @sql + ' from #t group by name' exec(@sql) drop table #t--结果 姓名 数学 物理 语文 ---------- ----------- ----------- ----------- 李四 83 93 74 张三 83 93 73
---------------------------------------------------- 如果上述两表互相换一下:即姓名 语文 数学 物理 张三 73 83 93 李四 74 84 94想变成 Name Subject Result 张三 语文 73 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94create table #t ( 姓名 varchar(10) , 语文 int , 数学 int , 物理 int )insert into #t(姓名 , 语文 , 数学 , 物理) values('张三',73,83,93) insert into #t(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select 姓名 as Name,'语文' as Subject,语文 as Result from #t union select 姓名 as Name,'数学' as Subject,数学 as Result from #t union select 姓名 as Name,'物理' as Subject,物理 as Result from #t order by 姓名 desc drop table #t
create table T(姓名 varchar(10), 学科 varchar(10), 分数 int) insert T select '小王', '英语', 65 union all select '小王', '数学', 68 union all select '小张', '英语', 78 union all select '小张', '数学', 98 declare @sql varchar(8000) set @sql='select 姓名,' select @sql=@sql+quotename(学科)+'=max(case when 学科='+quotename(学科, '''')+' then 分数 end),' from T group by 学科select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from T group by 姓名' exec(@sql)--result 姓名 数学 英语 ---------- ----------- ----------- 小王 68 65 小张 98 78
select 姓名, sum(case 学科 when '英语' then 分数 end) as '英语', sum(case 学科 when '数学' then 分数 end) as '数学' from "源表" group by 姓名
常用sql语句假设有张学生成绩表(CJ)如下 Name Subject Result 张三 语文 80 张三 数学 90 张三 物理 85 李四 语文 85 李四 数学 92 李四 物理 82想变成 姓名 语文 数学 物理 张三 80 90 85 李四 85 92 82declare @sql varchar(4000) set @sql = ''select Name'' select @sql = @sql + '',sum(case Subject when ''''''+Subject+'''''' then Result end) [''+Subject+'']'' from (select distinct Subject from CJ) as a select @sql = @sql+'' from test group by name'' exec(@sql)2. 行列转换--合并有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表B: id pid 1 1,2,3 2 1,2 3 1创建一个合并的函数 create function fmerg(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str='''' select @str=@str+'',''+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1) return(@str) End go--调用自定义函数得到结果 select distinct id,dbo.fmerg(id) from 表A3. 如何取得一个数据表的所有列名方法如下:先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。 SQL语句如下: declare @objid int,@objname char(40) set @objname = ''tablename'' select @objid = id from sysobjects where id = object_id(@objname) select ''Column_name'' = name from syscolumns where id = @objid order by colid是不是太简单了? 呵呵 不过经常用阿.4. 通过SQL语句来更改用户的密码修改别人的,需要sysadmin role EXEC sp_password NULL, ''newpassword'', ''User''如果帐号为SA执行EXEC sp_password NULL, ''newpassword'', sa 5. 怎么判断出一个表的哪些字段不允许为空?select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表? a. 查已知列名的情况 SELECT b.name as TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type=''U'' AND a.name=''你的字段名字'' b. 未知列名查所有在不同表出现过的列名 Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = ''U'' And Exists ( Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id <> s2.id )7. 查询第xxx行数据假设id是主键: select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游标也是可以的 fetch absolute [number] from [cursor_name] 行数为绝对行数8. SQL Server日期计算 a. 一个月的第一天 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) b. 本周的星期一 SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) c. 一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) d. 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) e. 上个月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) f. 去年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) g. 本月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) h. 本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0) i. 本年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 ----------------------------------------------------------------------- 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.数据库加密: select encrypt(''原始密码'') select pwdencrypt(''原始密码'') select pwdcompare(''原始密码'',''加密后密码'') = 1--相同;否则不相同 encrypt(''原始密码'') select pwdencrypt(''原始密码'') select pwdcompare(''原始密码'',''加密后密码'') = 1--相同;否则不相同3.取回表中字段: declare @list varchar(1000),@sql nvarchar(1000) select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A'' set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A'' exec (@sql)4.查看硬盘分区: EXEC master..xp_fixeddrives5.比较A,B表是否相等: if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B) print ''相等'' else print ''不相等''6.杀掉所有的事件探察器进程: DECLARE hcforeach CURSOR GLOBAL FOR SELECT ''kill ''+RTRIM(spid) FROM master.dbo.sysprocesses WHERE program_name IN(''SQL profiler'',N''SQL 事件探查器'') EXEC sp_msforeach_worker ''?''7.记录搜索: 开头到N条记录 Select Top N * From 表 ------------------------------- N到M条记录(要有主索引ID) Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc ---------------------------------- N到结尾记录 Select Top N * From 表 Order by ID Desc8.如何修改数据库的名称: sp_renamedb ''old_name'', ''new_name'' 9:获取当前数据库中的所有用户表 select Name from sysobjects where xtype=''u'' and status>=010:获取某一个表的所有字段 select name from syscolumns where id=object_id(''表名'')11:查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ''%表名%''12:查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype=''P''13:查询用户创建的所有数据库 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=''sa'') 或者 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x0114:查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = ''表名'' [n].[标题]: Select * From TableName Order By CustomerName [n].[标题]: Select * From TableName Order By CustomerName 按首字母查询 declare @t table(name varchar(10)) insert into @t select '张三' insert into @t select '李四' insert into @t select '王二' insert into @t select '王五' insert into @t select '吴三' insert into @t select '安妮'select a.name from @t a, (select '吖' chr,'A' letter union all select '八','B' union all select '嚓','C' union all select '咑','D' union all select '妸','E' union all select '发','F' union all select '旮','G' union all select '铪','H' union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M' union all select '拏','N' union all select '噢','O' union all select '妑','P' union all select '七','Q' union all select '呥','R' union all select '仨','S' union all select '他','T' union all select '屲','W' union all select '夕','X' union all select '丫','Y' union all select '帀','Z') b where b.chr<=left(a.name,1) group by a.name having max(letter)='W'
--use sql2k5--扩展兼容性 EXEC sp_dbcmptlevel 'dbname', '90'; GO declare @t table(name varchar(4),lesson varchar(4),score int) insert @t select '小王','英语',65 union all select '小王','数学',68 union all select '小张','英语',78 union all select '小张','数学',98select * from @t ------------------------- name lesson score ------------------------- 小王 英语 65 小王 数学 68 小张 英语 78 小张 数学 98 select 姓名=name,英语,数学 from @t a pivot ( sum(score) for lesson in(英语,数学) )p ---------------------- 姓名 英语 分数 ---------------------- 小王 65 68 小张 78 98
需要建临时表那么复杂吗?直接用以下方法就可以搞定 select a.姓名,a.分数 as 语文,b.分数 as 数学 from table1 a inner join table1 b on a.姓名 = b.姓名
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94create table #t
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into #t(Name , Subject , Result) values('张三','语文','73')
insert into #t(Name , Subject , Result) values('张三','数学','83')
insert into #t(Name , Subject , Result) values('张三','物理','93')
insert into #t(Name , Subject , Result) values('李四','语文','74')
insert into #t(Name , Subject , Result) values('李四','数学','83')
insert into #t(Name , Subject , Result) values('李四','物理','93')declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result end) [' + Subject + ']'
from (select distinct Subject from #t) as a
set @sql = @sql + ' from #t group by name'
exec(@sql) drop table #t--结果
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 83 93 74
张三 83 93 73
----------------------------------------------------
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94想变成
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94create table #t
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into #t(姓名 , 语文 , 数学 , 物理) values('张三',73,83,93)
insert into #t(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select 姓名 as Name,'语文' as Subject,语文 as Result from #t union
select 姓名 as Name,'数学' as Subject,数学 as Result from #t union
select 姓名 as Name,'物理' as Subject,物理 as Result from #t
order by 姓名 desc drop table #t
--结果
Name Subject Result
---------- ------- -----------
张三 数学 83
张三 物理 93
张三 语文 73
李四 数学 84
李四 物理 94
李四 语文 74(所影响的行数为 6 行)
create table T(姓名 varchar(10), 学科 varchar(10), 分数 int)
insert T select '小王', '英语', 65
union all select '小王', '数学', 68
union all select '小张', '英语', 78
union all select '小张', '数学', 98 declare @sql varchar(8000)
set @sql='select 姓名,'
select @sql=@sql+quotename(学科)+'=max(case when 学科='+quotename(学科, '''')+' then 分数 end),'
from T
group by 学科select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from T group by 姓名'
exec(@sql)--result
姓名 数学 英语
---------- ----------- -----------
小王 68 65
小张 98 78
sum(case 学科 when '英语' then 分数 end) as '英语',
sum(case 学科 when '数学' then 分数 end) as '数学'
from "源表"
group by 姓名
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82declare @sql varchar(4000)
set @sql = ''select Name''
select @sql = @sql + '',sum(case Subject when ''''''+Subject+'''''' then Result end) [''+Subject+'']''
from (select distinct Subject from CJ) as a
select @sql = @sql+'' from test group by name''
exec(@sql)2. 行列转换--合并有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''''
select @str=@str+'',''+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A3. 如何取得一个数据表的所有列名方法如下:先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下:
declare @objid int,@objname char(40)
set @objname = ''tablename''
select @objid = id from sysobjects where id = object_id(@objname)
select ''Column_name'' = name from syscolumns where id = @objid order by colid是不是太简单了? 呵呵 不过经常用阿.4. 通过SQL语句来更改用户的密码修改别人的,需要sysadmin role
EXEC sp_password NULL, ''newpassword'', ''User''如果帐号为SA执行EXEC sp_password NULL, ''newpassword'', sa 5. 怎么判断出一个表的哪些字段不允许为空?select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表?
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type=''U''
AND a.name=''你的字段名字'' b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = ''U''
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)7. 查询第xxx行数据假设id是主键:
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
-----------------------------------------------------------------------
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.数据库加密:
select encrypt(''原始密码'')
select pwdencrypt(''原始密码'')
select pwdcompare(''原始密码'',''加密后密码'') = 1--相同;否则不相同 encrypt(''原始密码'')
select pwdencrypt(''原始密码'')
select pwdcompare(''原始密码'',''加密后密码'') = 1--相同;否则不相同3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A''
set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A''
exec (@sql)4.查看硬盘分区:
EXEC master..xp_fixeddrives5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print ''相等''
else
print ''不相等''6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ''kill ''+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(''SQL profiler'',N''SQL 事件探查器'')
EXEC sp_msforeach_worker ''?''7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc8.如何修改数据库的名称:
sp_renamedb ''old_name'', ''new_name'' 9:获取当前数据库中的所有用户表
select Name from sysobjects where xtype=''u'' and status>=010:获取某一个表的所有字段
select name from syscolumns where id=object_id(''表名'')11:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ''%表名%''12:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype=''P''13:查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=''sa'')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x0114:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ''表名'' [n].[标题]:
Select * From TableName Order By CustomerName [n].[标题]:
Select * From TableName Order By CustomerName 按首字母查询
declare @t table(name varchar(10))
insert into @t select '张三'
insert into @t select '李四'
insert into @t select '王二'
insert into @t select '王五'
insert into @t select '吴三'
insert into @t select '安妮'select
a.name
from
@t a,
(select '吖' chr,'A' letter union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z') b
where
b.chr<=left(a.name,1)
group by
a.name
having max(letter)='W'
EXEC sp_dbcmptlevel 'dbname', '90';
GO
declare @t table(name varchar(4),lesson varchar(4),score int)
insert @t
select '小王','英语',65
union all select '小王','数学',68
union all select '小张','英语',78
union all select '小张','数学',98select * from @t
-------------------------
name lesson score
-------------------------
小王 英语 65
小王 数学 68
小张 英语 78
小张 数学 98
select 姓名=name,英语,数学
from @t a
pivot
(
sum(score)
for lesson in(英语,数学)
)p
----------------------
姓名 英语 分数
----------------------
小王 65 68
小张 78 98
select a.姓名,a.分数 as 语文,b.分数 as 数学
from table1 a inner join table1 b
on a.姓名 = b.姓名