SQL语句精典收藏［转］

来源：互联网发布：淘宝批量发布宝贝编辑：程序博客网时间：2024/04/30 04:26

SQL语句精典收藏

作者：Beyondbill 　来源于：delphibbs.com 　发布时间：2005-12-14 14:16:00

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)

另外在Access中还提供了TransForm来实现行列转换
TRANSFORM count(Result) AS number
SELECT 姓名
FROM 学生成绩表
GROUP BY 姓名
PIVOT Subject;

TransForm 用法如下:
=========================================================
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM 语句可分为以下几个部分：

部分描述
aggfunction 在选定数据上运作的 SQL 合计函数。
selectstatement SELECT 语句。
pivotfield 在查询的结果集中创建列标题时用的字段或表达式。
value1, value2 用来创建列标题的固定值。

说明
使用交叉表查询来摘要数据时，从指定的字段或表达式中选定值作为列标题，
这样，可以用比选定查询更紧凑的格式来观察数据。
TRANSFORM 是可选的，但在使用它时，要作为 SQL 字符串中的第一个语句。
它出现在 SELECT 语句（指定作为行标题的字段的）之前，还出现在 GROUP BY 子句
（指定行分组的）之前。可以有选择地包含其它子句，例如 WHERE 子句，它指定附
加的选择或排序条件。也可以将子查询当作谓词，特别是在叉表查询的 WHERE 子句中。

pivotfield 返回的值被用作查询结果集中的列标题。
例如，在交叉表查询中，将根据销售图表按销售月份创建 12 个列。
可以限制 pivotfield 用列在可选的 IN 子句中的固定值（value1, value2）来创建标题。
也可以用没有数据存在的固定值来创建附加的列。
==================================================================================

2. 列行转换
暂时保留

3. 行列转换--加合并
有表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

4. 如何取得一个数据表的所有列名
方法如下：先从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

是不是太简单了？呵呵不过经常用阿.

5. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa

6.怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

7.如何在数据库里找到含有相同字段的表？
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
)
8.查询第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]
行数为绝对行数

9.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语句由作者收藏整理，谢谢网上提供的朋友

SQL语句精典收藏 ［转］

SQL语句精典收藏［转］