SQL语句精典收藏 [转]

转载 2006年05月27日 16:13:00
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语句由作者收藏整理,谢谢网上提供的朋友

【收藏】经典shell十三问

shell 十三问: 1) 为何叫做 shell ?  2) shell prompt(PS1) 与 Carriage Return(CR) 的关系?  3) 别人 echo、你也 ...
  • apache0554
  • apache0554
  • 2015年02月11日 15:41
  • 13857

[转]SQL语句收藏

以下内容来自CSDN论坛的chaihuoniu,非常感谢!SQL操作全集 下列语句部分是Mssql语句,不可以在access中使用。 SQL分类: DDL—数据定义语言(CREATE,...
  • u012085815
  • u012085815
  • 2013年09月12日 19:27
  • 389

读《程序员的SQL金典》

【程序员与DBA】 DBA需要花很多时间在数据库的备份、授权、调优、修复、监控等内容上,而程序员则应该去掌握SQL语句的使用,而且为了简化系统开发难度,应该学会使用数据库函数、子查询、表联接、开窗函数...
  • liminwang0311
  • liminwang0311
  • 2013年08月27日 11:50
  • 791

Oracle中面试题的查询语句

01. 查询员工表所有数据,并说明使用*的缺点      select * from emp;      01.查询职(job)为'PRESIDENT'的员工的工资      select...
  • ailiandeziwei
  • ailiandeziwei
  • 2013年04月22日 14:24
  • 2780

1000行SQL整理(收藏价值灰常高)

/* 启动MySQL */ net start mysql/* 连接与断开服务器 */ mysql -h 地址 -P 端口 -u 用户名 -p 密码/* 跳过权限验证登录MySQL */ mys...
  • lyflyyvip
  • lyflyyvip
  • 2016年11月16日 16:17
  • 277

java代码实现oracle sql语句转mysql sql语句

package com; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; imp...
  • a364726306
  • a364726306
  • 2017年06月05日 10:40
  • 722

SHELL编程之常用技巧

前言本文是shell编程系列的第六篇,集中介绍了bash编程中部分高级编程方法和技巧。通过学习本文内容,可以帮你解决以下问题: bash可以网络编程么? .(){ .|.& };. 据说执行这些符号可...
  • renwotao2009
  • renwotao2009
  • 2016年06月21日 07:58
  • 1016

sql语句纵向转横向

mysql中的case when语句查询结果问题 数据库中的测试数据 现在我想让他显示的结果是 :姓名 语文 数学 物理 这种形式的(就是行转列) sql语句1: 这个结果是不...
  • akzwakzw
  • akzwakzw
  • 2017年11月21日 10:12
  • 397

Hibernate将HQL语句转换成SQL语句

Hibernate已经是不单单是ORM框架了,从Hibernate的社区就能看到其发展前景了。其在Java开发中很是常见. 其特有的HQL语句还是Java开发者必备技能。但是有时间HQL不是很好用或...
  • KuangYeYaZi
  • KuangYeYaZi
  • 2016年09月03日 00:45
  • 3324

将PDM文件生成sql执行语句

一、pdm生成sql:  1.Database ---》Database Generation或者 CTRL+G快捷键 打开生成对话框,做下面的相关设置后,点击ok即可生成sql脚本。   一、...
  • kunkun378263
  • kunkun378263
  • 2015年02月10日 11:43
  • 6969
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL语句精典收藏 [转]
举报原因:
原因补充:

(最多只允许输入30个字)