精典的SQL语句

1. 行列转换--普通

假设有张学生成绩表(CJ)如下
Name Subject    Result
张三语文       80
张三数学       90
张三物理       85
李四语文       85
李四数学       92
李四物理       82

想变成
姓名语文数学物理
张三 80    90    85
李四 85    92    82

declare @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 表A

3. 如何取得一个数据表的所有列名

方法如下：先从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))。

SQL SERVER 与EXCEL的数据转换
1、在SQL SERVER里查询Excel数据:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

2、将Excel的数据导入SQL server :

SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

3、将SQL SERVER中查询到的数据导成一个Excel文件

T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码：
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

4、在SQL SERVER里往Excel插入数据:

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')