交叉表,行列转换,交叉查询经典,分组小计合计报表,SQL,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

https://www.wendangku.net/doc/7713662302.html,/Render/archive/2006/12/19/596485.html

分组小计合计报表的SQL

Table1结构如下:

OrgName,StaffName, PayArea,Pay

要生成如下形式报表：

Org1,A1,100,4

Org1,A2,100,4

Org1,A3,100,4

Org1小计,XX,XX,XX

Org2,B1,100,4

....

合计, XX ,XX

SQL:

select OrgName DD,STAFFNAME,PayArea, Pay,OrgName as EE from Table1 as t1

union

select OrgName || '小计' DD,'',sum(PayArea),sum(Pay),OrgName || '_' EE from Table1 as t2 group by DEPT_ID

union

Select '合计' as DD,'',sum(PayArea),sum(Pay),'ZZZZZZZZ' as EE from Table1

order by EE

其中的OrgName||'_' 是为了取得一个比下一个不同的OraName大一些的值，以便让小计这条数据排在适当的位置，如

Org1小计要排在Org1和Org2之间，所以要选一个在数据库中字符排序号小的字符，这里以"_"表示。'ZZZZZZZZ'则

是为了把合计记录排在最后，所以要选一串在数据库中字符排序最大的字符构成的串，这里只是用'Z'来表示。

用ee 排序，但合计中

'ZZZZZZZZ' EE ，如果OrgName是汉字的话，那么排序后，合计将会变为第一行的，英文字母总是显示在汉字前面，

这样就达不到合计显示在最后一行的目的了，如果将合计中'ZZZZZZZZ' EE 变为'做做做做做做做做' EE 这样就能使

合计排在最后一行，因为'做'字是字典中最后一个字

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

交叉表、行列转换和交叉查询经典

Access 静态叉表行列转换统计语句

SELECT a.客户号,

sum(iif(a.期初五级=1,a.期初余额,null)) as "期初正常",

sum(iif(a.期初五级=2,a.期初余额,null)) as "期初关注",

sum(iif(a.期初五级=3,a.期初余额,null)) as "期初次级",

sum(iif(a.期初五级=4,a.期初余额,null)) as "期初可疑",

sum(iif(a.期初五级=5,a.期初余额,null)) as "期初损失",

sum(a.期初余额) as 期初总计,

sum(iif(a.期未五级=1,a.期未余额,null)) as "期未正常",

sum(iif(a.期未五级=2,a.期未余额,null)) as "期未关注",

sum(iif(a.期未五级=3,a.期未余额,null)) as "期未次级",

sum(iif(a.期未五级=4,a.期未余额,null)) as "期未可疑",

sum(iif(a.期未五级=5,a.期未余额,null)) as "期未损失",

sum(a.期未余额) as 期未总计,

FROM [work-1] as a

GROUP BY a.客户号;

https://www.wendangku.net/doc/7713662302.html,/content/10/0609/23/314324_32230468.shtml

一、什么是交叉表

“交叉表”对象是一个网格，用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别

其趋势。它由三个元素组成：

行

列

摘要字段

“交叉表”中的行沿水平方向延伸（从一侧到另一侧）。在上面的示例中，“手套”(Gloves) 是一行。

“交叉表”中的列沿垂直方向延伸（上下）。在上面的示例中，“美国”(USA) 是一列。

汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总（求和、计数等）。

在上面的示例中，“手套”和“美国”交叉处的值是四，这是在美国销售的手套的数量。“交叉表”还可以包括若干总计：

每行的结尾是该行的总计。在上面的例子中，该总计代表一个产品在所有国家/地区的销售量。“手套”行结尾处的

值是8，这就是手套在所有国家/地区销售的总数。

注意：总计列可以出现在每一行的开头。

每列的底部是该列的总计。在上面的例子中，该总计代表所有产品在一个国家/地区的销售量。“美国”一列底部的

值是四，这是所有产品（手套、腰带和鞋子）在美国销售的总数。

注意：总计列可以出现在每一行的顶部。

“总计”(Total) 列（产品总计）和“总计”(Total) 行（国家/地区总计）的交叉处是总计。在上面的例子中，“

总计”列和“总计”行交叉处的值是12，这是所有产品在所有国家/地区销售的总数。二、行列转换和交叉查询：

1: 列转为行:

eg1:

假设有张学生成绩表(CJ)如下

name subject result

张三语文80

张三数学90

张三物理85

李四语文85

李四数学92

李四物理82

相关sql语句：

Create table CJ(name char(10),subject char(10),result int);

insert into CJ(name,subject,result) values('张三','语文',99);

insert into CJ(name,subject,result) values('张三','数学',86);

insert into CJ(name,subject,result) values('张三','英语',75);

insert into CJ(name,subject,result) values('李四','语文',78);

insert into CJ(name,subject,result) values('李四','数学',85);

insert into CJ(name,subject,result) values('李四','英语',78)

select * from CJ

想变成如下的交叉表

姓名语文数学物理

张三99 90 85

李四85 92 82

我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的Select查询就

可以搞定：

select name,sum(case when a.subject='语文' then result else null end) as "语文",

sum(case when a.subject='数学' then result else null end) as "数学",

sum(case when a.subject='英语' then result else null end) as "英语"

from CJ a

group by name;

当要增加“总计”列："合计总分"时，如下表所示：

姓名合计总分语文数学物理

张三260 99 90 85

李四241 85 92 82

只需增加sum(a.result) as "合计总分"，sql如下：

select name,sum(a.result) as "合计总分",

sum(case when a.subject='语文' then result else null end) as "语文",

sum(case when a.subject='数学' then result else null end) as "数学",

sum(case when a.subject='英语' then result else null end) as "英语"

from CJ a

group by name;

其中利用了CASE语句判断，如果是相应的列，则取需要统计的cj数值，否则取NULL，然后再合计。

其中有两个常见问题说明一下：

a、用NULL而不用0是有道理的，假如用0，虽然求和函数SUM可以取到正确的数，但类似COUNT函数（取记录个数），

结果就不对了，因为Null不算一条记录，而0要算，同理空字串（""）也是这样，总之在这里应该用NULL,这样任何函数都没问题。

b、假如在视图的设计界面保存以上的查询，则会报错“没有输出列”，从而无法保存，其实只要在查询前面加上一

段：Create View ViewName AS ...，ViewName是你准备给查询起的名称，...就是我们的查询，然后运行一下，就可

以生成视图了，对于其他一些设计器不支持的语法，也可以这样保存。

以上查询作用也很大，对于很多情况，比如产品销售表中按照季度统计、按照月份统计等列头内容固定的情况，这样

就行了，但往往大多数情况下列头内容是不固定的，象City，用户随时可能删除、添加一些城市，这种情况就是我们

所说的动态交叉表，在SQLServer中我们可以用存储过程来解决。下面我们补充一些知识：相关子查询

相关子查询和普通子查询区别在于：相关子查询引用了外部查询的列。这种引用外部查询的能力意味着相关子查询不

能自己独立运行，其中对于外部查询引用会使会使其无法正常执行。因此相关子查询的执行顺序如下：

1.首先执行一遍外部查询

2.对于外部查询的每一行分别执行一遍子查询，而且每次执行子查询时候都会引用外部的当前行的值。使用子查询的

结果来确定外部查询的结果集。

举个例子；

SELECT t1.type

FROM titles t1

GROUP BY t1.type

HA VING MAX(t1.advance) >=ALL

(SELECT 2 * A VG(t2.advance)

FROM titles t2

WHERE t1.type = t2.type)

这个结果返回最高预付款超过给定组中平均预付款两倍的书籍类型。

再举个例子：

要求返回每一个编号的最大值(列出id,name,score)

ID Name（编号）Score（分数）

1 a 88

2 b 76

3 c 66

4 c 90

5 b 77

6 a 56

7 b 77

8 c 67

9 a 44

select * from t a where score=

(select Max(Score) from t b where https://www.wendangku.net/doc/7713662302.html,=https://www.wendangku.net/doc/7713662302.html,)

再给一个排位的sql语句

SELECT (

SELECT count(*) 1 as dd

FROM [Test ] as a where a.[F2]

FROM [Test ] as b

order by b.[F2];

好了关于sql的相关子查询先讲到这里。

SQLServer中局部变量赋值方法

有两种:

一种: set @变量名= 值

二种: select @变量名= 值

第二种可以从某个表中得到数据再赋值给变量

例: 从用户信息表中查询中cid为20 的用户姓名将他赋值给变量name

declare @name varchar(10) --用户名

select @name=userName from userInfo where cid = 20

print 'cid为20的用户姓名:' + @name

递归的select变量

递归的select变量是指使用select语句和子查询将一个变量与其自身拼接起来。语法形式如下：select @variable = @variable + table.column from table---见《sql server2000宝典》：P354,这是一种很优美的查询方法.从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。动态的交叉表这样就代替了传统的游标。

SQL语句解决方法：

写法一：

declare @sql varchar(4000)

set @sql = 'select name'

select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject

from (select distinct subject from CJ) as a

select @sql = @sql+' from CJ group by name'

exec(@sql)

写法二：

declare @sql varchar(4000)

set @sql = 'select name'

select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) as '+subject +' from CJ group by subject

select @sql = @sql+' from CJ 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

相关链接：

把列变成行的sql语句: https://www.wendangku.net/doc/7713662302.html,/liaoxiaohua1981/archive/2006/05/30/763721.aspx

应用SQL交叉表实现行列转换: https://www.wendangku.net/doc/7713662302.html,/sivee/archive/2007/05/06/1598039.aspx oracle 行列转换: https://www.wendangku.net/doc/7713662302.html,/gogogo520/archive/2005/10/10/498779.aspx

行列转换例子: https://www.wendangku.net/doc/7713662302.html,/zsl5305256/archive/2006/12/05/1430422.aspx

动态SQL的使用例子, 行列转换: https://www.wendangku.net/doc/7713662302.html,/hertcloud/archive/2007/04/05/1552626.aspx

SqlServer如何生成动态交叉表查询: https://www.wendangku.net/doc/7713662302.html,/article/12/12618.shtm

SQL语句精典收藏https://www.wendangku.net/doc/7713662302.html,/suofang/blog/item/35de9d23af3e5945ad34de8a.html

SqlServer如何生成动态交叉表查询

VB+MS SqlServer，是我们目前开发数据库应用系统最常用的模式，翻翻以前的老帖子，有一些SqlServer的问题经常被提出来，但正确解答甚少，现把我对这些问题的见解贴出来，这次先讲讲动态交叉表的问题

为了说明问题，我们用SqlServer自带的事例数据库(Northwind)来进行验证，所有的例子请放到Northwind中运行，我可能会省略Use语句，所引用的表，都是Northwind中的，下面我就不再说明了

我这里指的交叉表，就是象Access的交叉表查询一样的效果，比如Employees表中City 字段代表了城市的名称，TitleOfCourtesy代表称呼，我们希望按照City和TitleOfCourtesy 的情况来统计ReportsTo字段的合计数（本统计没有任何实际意义，只是挑选一些记录包含重复内容的字段来说明情况），并显示成以下格式：（TitleOfCourtesy作为行，City作为列）

探讨这个问题之前，我们首先来看一下如何建立静态的交叉表，也就是说列数固定的交叉表，这种情况其实只要一句简单的Select查询就可以搞定：

SELECT TitleOfCourtesy,

SUM(CASE City WHEN 'London' THEN ReportsTo ELSE NULL END) AS [London City], SUM(CASE City WHEN 'Redmond' THEN ReportsTo ELSE NULL END) AS [Redmond City],

SUM(CASE City WHEN 'Seattle' THEN ReportsTo ELSE NULL END) AS [Seattle City] FROM Employees GROUP BY TitleOfCourtesy

其中利用了CASE语句判断，如果是相应的列，则取需要统计的ReportsTo数值，否则取NULL，然后再合计

其中有两个常见问题说明一下：

a、用NULL而不用0是有道理的，假如用0，虽然求和函数SUM可以取到正确的数，但

类似COUNT函数（取记录个数），结果就不对了，因为Null不算一条记录，而0要算，同理空字串（""）也是这样，总之在这里应该用NULL,这样任何函数都没问题。

b、假如在视图的设计界面保存以上的查询，则会报错“没有输出列”，从而无法保存，其实只要在查询前面加上一段：Create View ViewName AS ...，ViewName是你准备给查询起的名称，...就是我们的查询，然后运行一下，就可以生成视图了，对于其他一些设计器不支持的语法，也可以这样保存。

以上查询作用也很大，对于很多情况，比如按照季度统计、按照月份统计等列头内容固定的情况，这样就行了，但往往大多数情况下列头内容是不固定的，象City，用户随时可能删除、添加一些城市，这种情况，我们就需要用存储过程来解决：

总体思路其实很简单，首先检索列头信息，形成一个游标，然后遍历游标，将上面查询语句里Case判断的内容用游标里的值替代，形成一条新的Sql查询，然后执行，返回结果，就可以了，以下是我写的一个存储过程，供大家参考：

CREATE procedure CorssTab

@strTabName as varchar(50) = 'Employees', --此处放表名

@strCol as varchar(50) = 'City', --表头分组依据字段

@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段

@strNumber as varchar(50) = 'ReportsTo', --被统计的字段

@strSum as varchar(10) = 'Sum' --运算方式

AS

DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)

EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标

begin

SET nocount ON

SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' +

@strSum + ' of ' + @strNumber + ']' --查询的前半段

OPEN corss_cursor

while (0=0)

BEGIN

FETCH NEXT FROM corss_cursor --遍历游标，将列头信息放入变量@strTmpCol INTO @strTmpCol

if (@@fetch_status<>0) break

SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' +

@strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' +

@strCol + ']' --构造查询

END

SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾

EXECUTE(@strsql) --执行

IF @@error <>0 RETURN @@error --如果出错，返回错误代码

CLOSE corss_cursor

DEALLOCATE corss_cursor RETURN 0 --释放游标，返回0表示成功

end

GO

几点说明：

a、这是一个通用存储过程，使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上，其中结果集的第二列我加了个合计列

b、为了测试方便，我在存储过程中设置了默认值，就是前面提到的Employees表，这样直接运行时就可以出来我上面提到的结果。

c、使用时，可以把上面的代码复制到企业管理器的查询设计界面Sql窗格，或者查询分析器里运行一下（注意正确选择NorthWind数据库），就可以生成一个存储过程：CorssTab，然后直接运行CorssTab，如果出现本文前面类似的窗格，就表示运行成功了。

d、假如用于其它表，首先需要在你的用户数据库里生成此存储过程（当然也可以放到Master 里，然后再加个变量：@DataBase，赋值为数据库名称，然后在上面代码打开指定数据库，这样所有的数据库都可以调用它），当你调用时，采取以下格式：

CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'

上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量，大家可以运行试一下效果，虽然列头显示的名称不恰当，但基本效果出来了，相信大家通过对我的代码再作简单修改，可以达到满意的交叉表效果。

下次我再讲讲，如何给查询的记录集自动加行号

动态SQL的使用例子, 行列转换

drop table #test

create table #test(name nvarchar(20),type nvarchar(20),category nvarchar(20)) select*from #test

insert into #test(name,type,category) values ('n1','t1','c1');

insert into #test(name,type,category) values ('n2','t1','c2');

insert into #test(name,type,category) values ('n3','t2','c1');

insert into #test(name,type,category) values ('n4','t3','c3');

insert into #test(name,type,category) values ('n5','t2','c4');

insert into #test(name,type,category) values ('n6','t3','c5');

insert into #test(name,type,category) values ('n1','t1','c1');

--select category,name,sum(case when type='t1' then 1 else 0 end),sum(case when type ='t2' then 1 else 0 end),sum(case when type='t3' then 1 else 0 end) from #test group by n ame,category

--如果type不固定

--使用動態SQL語句

Declare@S Varchar(8000)

Select@S='Select category, name'

Select@S=@S+', SUM(Case type When '''+ type +''' Then 1 Else 0 End) As '+ type From #TEST Group By type

Select@S=@S+' From #TEST Group By category, name Order By category, name' print@S

EXEC(@S)

GO

--测试数据行转列

Create table test (name char(10),km char(10),cj int)

insert test values('张三','语文',80)

insert test values('张三','数学',86)

insert test values('张三','英语',75)

insert test values('李四','语文',78)

insert test values('李四','数学',85)

insert test values('李四','英语',77)

--查询

declare@sql varchar(8000),@s1varchar(8000)

select@sql='',@s1=''

select@sql=@sql+',['+km+']=sum(case km when '''+km+''' then cj els e 0 end)'

,@s1=@s1+',sum(case km when '''+km+''' then cj else 0 end)/sum(case k m when '''+km+''' then 1 else 0 end)'

from test

group by km

exec('select name=case grouping(name) when 1 then ''全班总分'' else nam e end'+@sql+',小计=sum(cj)

from test

group by name with rollup

union all

select ''全班平均分'''+@s1+',sum(cj)/count(distinct name)

from test')

go

--删除测试

drop table test

--MS SQL2000下月份不固定的動態寫法

Create Table TEST

(class Nvarchar(10),

name Nvarchar(10),

年份Int,

[1月]Varchar(10),

[2月]Varchar(10),

[3月]Varchar(10))

Insert TEST Select N'一班',N'张三',2007,'5元','2元','5元'

Union All Select N'一班',N'李四',2006,'3元','0元','1元'

Union All Select N'二班',N'王五',2007,'0元','0元','1元'

GO

Declare@S Nvarchar(4000)

Select@S=''

Select@S=@S+' Union Select class, name, 年份, '''+ Name +''' As 月份, ['+ Nam e +'] As 元 From TEST '

From SysColumns Where ID =OBJECT_ID('TEST') And Name Like'%月'Order By Nam e

Select@S=Stuff(@S, 1, 7, '')

Print@S

EXEC(@S)

GO

Drop Table TEST

--动态月份2005 处理如下:

--测试环境

create table tb_tb(class varchar(10),name varchar(10),年份varchar(10),[1月]varchar (10),[2月]varchar(10),[3月]varchar(10))

insert into tb_tb select'一班','张三','2007','5元','2元','5元'

union all select'一班','李四','2006','3元','0元','1元'

union all select'二班','王五','2007','0元','0元','1元'

--计算月份:

declare@月份varchar(100)

set@月份='';

select@月份=@月份+',['+name+']'from sys.columns where object_id=object_id('tb_tb') and name like'%月'

set@月份=stuff(@月份,1,1,'')

--交叉表处理

exec('

select * from tb_tb

unpivot

( 金额 for 月份 in ('+@月份+')

) unpt

where 金额<>''0元''

')

--删除测试环境

drop table tb_tb

--建立測試環境

Create Table表1

([id]Int,

[名称]Nvarchar(20))

Insert表1 Select1, N'名称1'

Union All Select2, N'名称2'

Union All Select3, N'名称3'

Create Table表2

([id]Int,

[时间]Nvarchar(10),

[地点]Nvarchar(10))

Insert表2 Select1, N'5日', N'上海'

Union All Select1, N'9日', N'北京'

Union All Select1, N'20日', N'天津'

Union All Select2, N'8日', N'杭州'

Union All Select2, N'19日', N'广州'

Union All Select3, N'8日', N'深圳'

GO

--創建函數

Create Function F_TEST(@id Int)

ReturnS Nvarchar(4000)

As

Begin

Declare@S Nvarchar(4000)

Select@S=''

Select@S=@S+';'+时间+'-'+地点From表2 Where id =@id

Select@S=Stuff(@S, 1, 1, '')

Return@S

End

GO

--測試

Select

id,

dbo.F_TEST(id) As[时间、地点]

From

表1

GO

--刪除測試環境

Drop Table表1, 表2

Drop Function F_TEST

把列变成行的sql语句

线有如下表：

科目分数姓名

语文88 董兆

数学95 董兆

英语89 董兆

语文69 婵娟

数学95 婵娟

英语89 婵娟

语文69 李慧

数学95 李慧

英语89 李慧

一条sql语句，查询结果是

李慧婵娟董兆

语文69 69 88

数学95 95 95

英语89 89 89

sql语句如下:

create table k(科目varchar(50),分数int,姓名varchar(50))

insert k select '语文',88,'董兆'

union all select '数学',95,'董兆'

union all select '英语',89,'董兆'

union all select '语文',69,'婵娟'

union all select '数学',95,'婵娟'

union all select '英语',89,'婵娟'

union all select '语文',69,'李慧'

union all select '数学',95,'李慧'

union all select '英语',89,'李慧'

declare @s varchar(8000)

set @s='select 科目'

select @s=@s+',['+姓名+']=sum(case 姓名when '''+姓名+''' then 分数else 0 end)' from k group by 姓名

exec(@s+'from k group by 科目')

应用SQL交叉表实现行列转换

--数据结构

--数据体

--SQL语句

Declare@SqlStrnvarchar(2000)

--构建Sql语句，生成2004级2班2004年的考虑成绩表

Select@SqlStr='selectGrade,ExamYear,Name,'

--生成条件选择语句，使用Distinct把所有科目全部列出，当然，可以加上条件，如：仅某一年度的考试科目

Select@SqlStr=@SqlStr+'SUM(CASESubjectWHEN'''+Subject+'''THENScoreELSE0END )AS'''+Subject+''','from(SelectDistinctSubjectfromExamResultwhereExamYear=2004)AsT emTable

--补全Sql语句，并使用GroupBy对重复的记录进行汇总

Select@SqlStr=left(@SqlStr,Len(@SqlStr)-1)+'fromExamResultwhereExamYear=2004G roupByName,Grade,ExamYear'

--执行Sql

Exec(@SqlStr)

Go

--执行结果

Grade ExamYear Name 化学数学语文

1 2004级2班2004 付超89 85 56

2 2004级2班2004 李明45 100 75

3 2004级2班200

4 王强78 90 89

行列转换例子

一、現有一個表TB1，其字段如下：(Name：代表姓名，Province：代表省份，Score：代表業務顧客數量)

Name Province Score

李三四川 5

小王四川 3

小張廣州 3

李三廣州 2

小張湖南 3

李三湖南 4

得到下面的結果：

姓名四川廣州湖南總計

李三 5 2 4 11

小王 3 0 0 3

小張0 3 3 6

注意：省份必須根據TB1表中所有出現的省份進行統計

create table A

(

Name varchar(10),

Province varchar(20),

Score int

)

insert A

select '李三','四川',5 union

select '小王','四川',3 union

select '小張','廣州',3 union

select '李三','廣州',2 union

select '小張','湖南',3 union

select '李三','湖南',4

--测试

declare @s varchar(8000)

set @s = ''

select @s = @s +','+Province+'= sum(case province when'''+province+'''then score else 0 end)'

from A group by Province order by Province

exec ('select name '+@s+',sum(Score) As Total from A group by Name order by Name')

--测试结果

-- Name 广州湖南四川Total

-- 1 李三24511

-- 2 小王0033

-- 3 小張3306

--测试结束

drop table A

二、这是邹建写的一个交叉表的实例

--测试数据

CREATE TABLE #XS_REP_CLIENTPROD_CHX

(

C_PTID varchar (16) NULL ,

C_PTNAME varchar(80) NULL,

c_PCID varchar(50) NULL,

C_CUSTID varchar (15) NULL ,

c_CustName varchar (100) NULL ,

-- N_PRICE numeric(18, 2) NULL ,

-- N_PRENOINVOICE numeric(18, 3) NULL ,

N_SALEWEIGHT numeric(18, 3) NULL --,

-- N_INVOICEWEIGHT numeric(18, 3) NULL ,

-- N_NOINVOICE numeric(18, 3) NULL,

-- N_PRENOINVOICE_m numeric(18, 3) NULL,

2021年SQL语句大全实例

SQL语句实例 欧阳光明(2021.03. 07) 表操作 例1对于表的教学管理数据库中的表STUDENTS ,可以定义如下： CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO)) 例2对于表的教学管理数据库中的表ENROLLS ,可以定义如下：CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL GRADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO)

*欧阳光明*创编2021.03.07 CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))) 例3根据表的STUDENTS表，建立一个只包含学号.姓名.年龄的女学生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX=女?; 例4删除教师表TEACHER。 DROP TABLE TEACHER 例5在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50)) 例6把STUDENTS表中的BPLACE列删除，并且把引用 BPLACE列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7补充定义ENROLLS表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO);

excel表格怎样行变列

竭诚为您提供优质文档/双击可除excel表格怎样行变列 篇一：excel中的表格,行变列,列变行 如何让其行变列，列变行。并且其相关数据也能自动调整 1、启动excel，打开需要的，在当前中，选中需要进行行列转换的单元格区域，如a1:x6。 2、单击“编辑”菜单中的“复制”命令。 3、单击要存放转置表区域的左上角单元格，如a7。 4、单击“编辑”菜单中的“选择性粘贴”命令，系统“选择性粘贴”对话框，选中“转置”复选框。 5、单击[确定]按钮，则行列转置后的表格出现在a7:F30单元格区域。现在把a1:x6单元格区域删除，则完成了表格的行列转置。 怎样把woRd、excel中的表格，行变列，列变行？ 在excel中是可以做到的，你将要转变的数据区域复制，在目标单元格处右击，选择“选择性粘贴”，将里面的“转置”选中，确定即可。 excel如何有规律列变行

我有份excel表有一列名字共4000个,我想转换成每行 8个名字,共500行.请问有没有什么简单的方法.请高手帮忙. 在这个工作表后面新建一个工作表 a1=indiRect("sheet1!a"&((Row(a1)-1)*8+column(a1))) 然后复制到a1:h500的区域里就行了 注意sheet1!a表示你原来数据的工作表名为sheet1， 数据在a列，不是的话相应改一下。 在excel中如何有规律的将列变行 比如 1 2 3 4 5 6 7 8 9 相隔处为空白行 变成 123

456 789 请大虾帮忙哈!!!数据非常多,如果用复制转置的话真要命1!谢谢!!问题补充： 若连接处的空行不好处理,能将连续的数列按规律变行更好! 比如 1 2 3 4 5 6 变成 123 456 先万分感谢!! abcdeF 原始数据最终结果行需要合并的单元格 11232a2a3a4 24566a6a7a8

SQL中的case-when,if-else实例

create database EXAM go create table student (stuName varchar(10)not null, stuNO int primary key not null, stuSex char(2)check(stuSex='男'or stuSex='女'), stuAge int, stuSeat int, stuAddress varchar(40) ) GO insert into student values('张秋丽','25301','女','21','1','北京海淀'), ('李文才','25302','男','25','2','天津'), ('张三','25303','男','22','3','北京海淀'), ('红尘','25304','女','21','4','湖南长沙'), ('段林希','25305','女','20','5','江西赣州'), ('魏晨','25306','男','23','6','河北石家庄'), ('郑爽','25307','女','20','7',''), ('张杰','25308','男','21','8',''), ('王洁','25309','女','23','9','湖南怀化'), ('刘欣','253010','女','21','10','北京') create table exam (ExamNO int primary key, stuNO int not null, WrittenExam int, LabExam int ) GO insert into exam values(01,250301,86,89), (02,250302,67,78), (03,250303,76,80), (04,250304,79,56), (05,250305,56,63), (06,250306,67,60), (07,250307,90,83), (08,250308,80,79), (09,250309,92,90), (10,250310,55,58)

数据库_经典SQL语句大全

一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1

Oracle列转行和行转列的几种用法

Oracle列转行和行转列的几种用法 栏到 栏主要讨论sys_connect_by_path的用法 1，具有分层关系 SQL > createtabledept(deptnononumber，deptname varchar2 (20)，mgrnononumber)； table created . SQL >插入deptvalues (1，“总部”，空)； 1 row created . SQL >插入deptvalues (2，’浙江分公司’，1)； 1 row created . SQL > insert into dept values(3，’杭州分公司’，2)；已创建 1行。 SQL >提交； 提交完成。 SQL >从部门连接中选择最大值(子串(sys_connect_by_path(deptname，’，’)，2))由先前部门连接= mgrno 最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(DEPTNAME)，’)，2) -总部，浙江分行，杭州分行 2，行-列转换 如果一个表的所有列都连接到一行，用逗号分隔:

SQL >选择最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(column _ name，’，’)，2)) MAX(SUBSTRA(SYS _ CONNECT _ BY _ PATH(COLUMN _ NAME，’，’)，2)) - DEPTNO，DEPTNAME，MGRNO 3，ListAgg(Oracle 11g) SQL >选择DEPTNO， 2 ListAgg(NAME，’；’) 3在组 4内(由搪瓷订购)搪瓷 5来自emp 6组由deptno 7由deptno 8 / DEPTNO搪瓷 - - 10 CLARK。国王；米勒 20亚当斯；福特。琼斯； SCOTT。史密斯 30艾伦；布莱克； JAMES；马丁； TURNER；下面的W ARD

EXCEL中的表格,行变列,列变行

如何让其行变列，列变行。并且其相关数据也能自动调整 1、启动Excel，打开需要的工作簿，在当前工作表中，选中需要进行行列转换的单元格区域，如A1:X6。 2、单击“编辑”菜单中的“复制”命令。 3、单击要存放转置表区域的左上角单元格，如A7。 4、单击“编辑”菜单中的“选择性粘贴”命令，系统“选择性粘贴”对话框，选中“转置”复选框。 5、单击[确定]按钮，则行列转置后的表格出现在A7:F30单元格区域。现在把A1:X6单元格区域删除，则完成了表格的行列转置。 怎样把WORD、EXCEL中的表格，行变列，列变行？ 在EXCEL中是可以做到的，你将要转变的数据区域复制，在目标单元格处右击，选择“选择性粘贴”，将里面的“转置”选中，确定即可。 EXCEL如何有规律列变行? 我有份EXCEL表有一列名字共4000个,我想转换成每行8个名字,共500行.请问有没有什么简单的方法.请高手帮忙. 在这个工作表后面新建一个工作表 A1=INDIRECT("Sheet1!A"&((ROW(A1)-1)*8+COLUMN(A1))) 然后复制到A1:H500的区域里就行了 注意sheet1!A表示你原来数据的工作表名为sheet1，数据在A列，不是的话相应改一下。 在EXCEL中如何有规律的将列变行? 比如 1 2 3 4 5 6 7

8 9 相隔处为空白行 变成 123 456 789 请大虾帮忙哈!!! 数据非常多,如果用复制转置的话真要命1!谢谢!! 问题补充： 若连接处的空行不好处理,能将连续的数列按规律变行更好! 比如 1 2 3 4 5 6 变成 123 456 先万分感谢!! A B C D E F 原始数据最终结果行需要合并的单元格 1 123 2 A2 A 3 A4 2 456 6 A6 A7 A8 3 789 10 A10 A11 A12 101112 14 A14 A15 A16 4 13141 5 18 A18 A19 A20 5 161718 22 A22 A23 A24 6 192021 26 A26 A2 7 A28 30 A30 A31 A32 7 34 A34 A35 A36 8 38 A38 A39 A40 9 42 A42 A43 A44 46 A46 A47 A48 10 50 A50 A51 A52 11 54 A54 A55 A56 12 58 A58 A59 A60 62 A62 A63 A64

最新常用经典SQL语句大全完整版

常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份 BACKUP DATABASE pubs TO testBack

4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname

sqlserver到oracle数据无损迁移

sqlserver到oracle数据无损迁移 编者：liuli10@https://www.wendangku.net/doc/7713662302.html, 版本：V1.7 最后修订日期：2015-11-21

第一章简介 1.1数据迁移 随着时代发展数据越来越被重视，而很多时候，当系统需要更新换代的时候，升级后系统所是有的数据库与当前系统的数据库并不一致，此时不仅需要数据割接，最重要的是：如何能将老系统中的数据无损的割接到新系统、新数据库中。因此，结合项目实战经验，针对从windows平台下数据库sqlserver到linux平台下oracle数据库的数据无损迁移进行总结。 1.2数据库简介 一般此处会有很多数据库以及出品公司的历史以及发展历程，在编者看来然而并没有什么大用途，百度百科都可以搜索的到，因此本章结束，直接进入实战总结环节。

第二章sqlserver数据导出 2.1sqlserver数据导出命令 当然不可否认windows为sqlserver提供了强大的图形化平台，导出数据变得只需要点一点就能完成，然而这样的数据导出对于大批量有要求的操作，是极其劳神伤财的，因此，必须要通过命令行进行格式化导出，因此，这里介绍sqlserver 本机数据库导出命令。 2.1.1bcp命令以及参数介绍 https://www.wendangku.net/doc/7713662302.html,/liyanmingkong/article/details/6087674 https://www.wendangku.net/doc/7713662302.html,/uid-25472509-id-4304562.html https://www.wendangku.net/doc/7713662302.html,/link?url=WV2JJM4JHxR7Qct8rr_-499zPc3aP_7E5rOt5l yEnG_Mj_tE9_-ZN1JPE2Vc2wRpkO8QkNGNLVznDfMgniCOnxXhK5jQppNpZk8 Jo1x8o23 为了将文档尽可能精简，bcp命令的参数以及介绍请自行去以上任意网址查询。或者自行baidu或者google搜索。 2.2实战语句解析 实战语句为： bcp"select*from gwbnboss.dbo.ACCOUNT_BUSINESS"queryout "C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt"-c -r"{#$&}"-t"{@#$}"-S"127.0.0.1"-U"数据库用户名"-P"密码" 最终导出的结果存在于 C:\Users\liuli9\Desktop\sqlserverdata_mov\textfile\ACCOUNT_BUSINESS.txt 文件中，当出现“{#$&}”时表示接下来是下一行数据，出现“{@#$}”时表示接下来是下一列数据。将查询结果集完整导出，不对数据做任何格式化或者修改操作，保证数据的原生无损。

word表格行列互换

竭诚为您提供优质文档/双击可除 word表格行列互换 篇一：巧在woRd表格中行列轻松对调 巧在woRd表格中行列轻松对调 在日常工作中，我们经常会遇到将word表格行列对调的情况，但这个看似简单的要求在word中却很难实现，因为word本身并不提供这样的功能。不过，要解决这个难题也不是没有办法，今天笔者就给大家介绍一个利用excel轻松互换word表格的小技巧，希望对大家能有所帮助。 【注】本文所述技巧已于word20xx+excel20xx环境下测试通过 1.打开word软件，选中需要进行行列对调的表格，右击执行“复制”命令 2.再打开excel软件，新建一个工作簿，用鼠标在空白单元格处右击并选择“粘贴”命令 3.在excel软件中，将刚刚粘贴下来的表格再次选中，同样是右击执行“复制”命令 4.在空白单元格处再次右击并执行“选择性粘贴”命令，同时勾选上“转置”复选框后点击“确定”按钮

5.此时，表格中的行列内容已经互换，将完成后的excel 表格选中，并粘贴到word中，基本操作即告完成 6.最后，别忘了删除掉word中的原有表格。至此，所有工作正式完成 篇二：word表格中数据纵横转换的方法和技巧 word表格中数据纵横转换的方法和技巧在实际工作中，有时需要将表格中的行、列数据互换，有时需要将表格数据快速转换成指定列数的新表格，或者对表格作简单编辑后快速得到新表格，这些都是表格内数据纵横转换问题，如何实现呢？ 一、实现表格内行列数据互换 对表格的行列数据做转置处理，就能实现表格内行列数据互换。在word中做转置处理困难，但在excel中实现数据转置比较容易。所以，可借助excel实现word表格内行列数据互换。具体操作步骤是：第1步：在word编辑环境下，选定欲操作的表格，执行剪切操作，将表格数据粘贴到剪贴板。第2步：启动excel，执行粘贴操作，将剪贴板内的数据粘贴到当前数据表中;然后，选中全部粘贴来的数据，执行复制操作，并用鼠标单击数据区外的某一单元格，执行“粘贴|转置”命令，得到转置后的数据;(word表格行列互换)再选中转置后的数据，执行复制操作。 第3步：切换到word编辑中，执行“选择性粘贴”命

常用经典SQL语句大全完整版教学文案

常用经典S Q L语句大 全完整版

常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份

BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….)

oracle列转行sql详细讲解

--当期时间贷款时间 SELECT DK_ID, max(substr(activeDate, 2)) activeDate FROM (SELECT DK_ID, sys_connect_by_path(activeDate, ',') activeDate FROM (SELECT DK_ID, activeDate, DK_ID || rn rchild, DK_ID || (rn - 1) rfather FROM (SELECT TEMP.DK_ID, --查询项目所在地树形结构全名 SELECT t.area_id, substr(sys_connect_by_path(t.area_name, '-'), 2) as allname , connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否 level as lel --是伪列，显示当前节点层所处的层数 FROM dk_project_area_info t START WITH t.area_name = '项目所在地' CONNECT BY PRIOR t.area_id = t.area_pid SYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT ename FROM scott.emp START WITH ename = 'KING' CONNECT BY PRIOR empno = mgr; 得到结果为：KING JONES SCOTT ADAMS FORD SMITH BLAKE ALLEN WARD MARTIN TURNER JAMES

项目开发中常用到的SQL语句

项目开发中常用到的SQL语句1、循环示例 循环示例代码： ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 DECLARE @i int DECLARE @name varchar(10) DECLARE @password varchar(10) Set @i = 1000 WHILE @i < 1200 BEGIN Set @i =@i +1 SET @name = RIGHT('0000' + CAST(@i AS varchar(10)),4) set @password = @name select @name insert into dbo.LocomotiveTeminalBase (li_ID,t_ID,lt_IDNumber,lt_MiM,lt_FuWQIP,lt_FuWQDKH,lt_CreatedBy) values('d82575c0-2d21-4c47-a406-7771d7d2c80a','fb5d9a7b-9cd6-4a55-9e90-881706eaf @name,@password,'192.168.1.187','2000','9015c234-e185-4e15-96c6-f53426dd6690') END 2、数据库缓存依赖中用到的SQL语句代码示例： ? 1 2 3 4 5 6 7 8 --查看状态 Select DATABASEpRoPERTYEX('soft_LocomotiveRM_DB','IsBrokerEnabled') --启用broker ALTER DATABASE soft_LocomotiveRM_DB SET NEW_BROKER WITH ROLLBACK IMMEDIATE ALTER DATABASE soft_LocomotiveRM_DB SET ENABLE_BROKER --添加用户

oracle 行转列sql

表结构和数据如下（表名Test）： NO V ALUE NAME 1 a 测试1 1 b 测试2 1 c 测试3 1 d 测试4 2 e 测试5 4 f 测试6 4 g 测试7 Sql语句： select No, ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value, ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name from (select No, Value, Name, rnFirst, lead(rnFirst) over(partition by No order by rnFirst) rnNext from (select a.No, a.Value, https://www.wendangku.net/doc/7713662302.html,, row_number() over(order by a.No, a.V alue desc) rnFirst from Test a) tmpTable1) tmpTable2 start with rnNext is null connect by rnNext = prior rnFirst group by No; 检索结果如下： NO V ALUE NAME 1 a;b;c;d 测试1;测试2;测试3;测试4 2 e 测试5 4 f;g 测试6;测试7 简单解释一下那个Sql吧： 1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：select a.No, a.Value, https://www.wendangku.net/doc/7713662302.html,, row_number() over(order by a.No, a.V alue desc) rnFirst

ORACLE关于动态SQL的使用

关于动态SQL的使用-----摘录 内容摘要：在PL/SQL开发过程中，使用SQL，PL/SQL可以实现大部份的需求，但是在某些特殊的情况下，在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求，比如需要动态建表或某个不确定的操作需要动态执行。这就需要使用动态SQL来实现。本文通过几个实例来详细的讲解动态SQL的使用。 本文适宜读者范围：Oracle初级，中级 系统环境： OS：windows2000Professional(英文版) Oracle：8.1.7.1.0 正文： 一般的PL/SQL程序设计中，在DML和事务控制的语句中可以直接使用SQL，但是DDL语句及系统控制语句却不能在PL/SQL中直接使用，要想实现在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现。 首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。 Oracle中动态SQL可以通过本地动态SQL来执行，也可以通过DBMS_SQL包来执行。下面就这两种情况分别进行说明： 一、本地动态SQL 本地动态SQL是使用EXECUTE IMMEDIATE语句来实现的。 1、本地动态SQL执行DDL语句： 需求：根据用户输入的表名及字段名等参数动态建表。 create or replace procedure proc_test ( table_name in varchar2,--表名 field1in varchar2,--字段名 datatype1in varchar2,--字段类型 field2in varchar2,--字段名 datatype2in varchar2--字段类型 )as str_sql varchar2(500); begin str_sql:=create table||table_name||(||field1||||datatype1||,||field2|| ||datatype2||); execute immediate str_sql;--动态执行DDL语句 exception when others then null; end; 以上是编译通过的存储过程代码。下面执行存储过程动态建表。

DB2常用SQL语句集

DB2常用SQL语句集 1、查看表结构： describe table tablename describe select * from tablename 2、列出系统数据库目录的内容： list database directory 3、查看数据库配置文件的内容： get database configuration for DBNAME 4、启动数据库： restart database DBNAME 5、关闭表的日志 alter table TBLNAME active not logged inially 6、重命名表 rename TBLNAME1 to TBLNAME2 7、取当前时间 select current time stamp from sysibm.sysdummy1 8、创建别名 create alias ALIASNAME for PRONAME(table、view、alias、nickname) 9、查询前几条记录 select * from TBLNAME fetch first N rows 10、联接数据库 db2 connect to DB user db2 using PWD 11、绑定存储过程命令 db2 bind BND.bnd 12、整理优化表 db2 reorgchk on table TBLNAME db2 reorg table TBLNAME db2 runstats on table TBNAME with distribution and indexes all 13、导出表 db2 export to TBL.txt of del select * from TBLNAME db2 export to TBL.ixf of ixf select * from TBLNAME 以指定分隔符‘|’下载数据： db2 "export to cmmcode.txt of del modified by coldel| select * from cmmcode”14、导入表 db2 import from TBL.txt of del insert into TBLNAME db2 import from TBL.txt of del commitcount 5000 insert into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 insert into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 insert_update into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 replace into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 create into TBLNAME (仅IXF) db2 import from TBL.ixf of ixf commitcount 5000 replace_create into TBLNAME (仅 IXF) 以指定分隔符“|”加载：

数据库经典SQL语句大全

数据库经典SQL语句大全 篇一：经典SQL语句大全 下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先，简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk'，'testBack'， 'c:mssql7backupMyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key]，col2 type2 [not null]，..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1，col2? from tab_old definition only 5、说明： 删除新表： tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col?.) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。

Oracle行转列,列转行

先来个简单的用法 列转行 Create table test (name char(10),km char(10),cj int) insert test values('张三','语文',80) insert test values('张三','数学',86) insert test values('张三','英语',75) insert test values('李四','语文',78) insert test values('李四','数学',85) insert test values('李四','英语',78) select name, sum(decode(km,'语文',CJ,0)) 语文, sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 from test1 group by name 姓名语文数学英语 张三80 86 75 李四78 85 78 行转列 with x as( selectname, sum(decode(km,'语文',CJ,0)) 语文 , sum(decode(km,'数学',cj,0)) 数学, sum(decode(km,'英语',cj,0)) 英语 fromtest groupbyname) selectname,decode(rn,1, '语文', 2, '数学', 3,'英语') 课程, decode(rn, 1, 语文, 2, 数学, 3,英语) 分数 from x, (selectlevel rn from dual connectby1=1andlevel<=3) (from 后面接两个表,是笛卡尔积)

sql循环语句的写法

sql循环语句的写法 SQL循环语句 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end --------------- while 条件 begin 执行操作 set @i=@i+1 end WHILE 设置重复执行SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。可以使用BREAK 和CONTINUE 关键字在循环内部控制WHILE 循环中语句的执行。语法WHILE Boolean_expression { sql_statement | statement_block } [ BREAK ] { sql_statement | statement_block } [ CONTINUE ] 参数

Boolean_expression 返回TRUE 或FALSE 的表达式。如果布尔表达式中含有SELECT 语句，必须用圆括号将SELECT 语句括起来。{sql_statement | statement_block} Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块，请使用控制流关键字BEGIN 和END。BREAK 导致从最内层的WHILE 循环中退出。将执行出现在END 关键字后面的任何语句，END 关键字为循环结束标记。CONTINUE 使WHILE 循环重新开始执行，忽略CONTINUE 关键字后的任何语句。注释 如果嵌套了两个或多个WHILE 循环，内层的BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。示例 A. 在嵌套的IF...ELSE 和WHILE 中使用BREAK 和CONTINUE 在下例中，如果平均价格少于$30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于$50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过$50，然后退出WHILE 循环并打印一条消息。USE pubs GO WHILE (SELECT A VG(price) FROM titles) < $30 BEGIN

精妙SQL语句收集

精妙SQL语句收集 摘要:一、基础 二、提升 三、技巧 正文: SQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作。 一、基础 1、说明：创建数据库 Create DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2... from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加var char类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col)