精典的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')