求一条可行语句（急）

gobelove 2008-10-22 10:45:26
create table product(pid int primary key identity(1,1),店名nvarchar(20)not null,产品名称nvarchar(20)not null,进货int)
insert into product(店名,产品名称,进货)values('连锁店一','商品一',200)
insert into product(店名,产品名称,进货)values('连锁店一','商品二',100)
insert into product(店名,产品名称,进货)values('连锁店一','商品三',300)
insert into product(店名,产品名称,进货)values('连锁店一','商品四',400)
insert into product(店名,产品名称,进货)values('连锁店一','商品五',200)
insert into product(店名,产品名称,进货)values('连锁店二','商品一',100)
insert into product(店名,产品名称,进货)values('连锁店二','商品二',200)
insert into product(店名,产品名称,进货)values('连锁店二','商品三',300)
insert into product(店名,产品名称,进货)values('连锁店二','商品四',600)
insert into product(店名,产品名称,进货)values('连锁店二','商品五',700)

想要的结果如下：

产品一,产品二,产品三,产品四,产品五
连锁店一 200 100 300 400 200
连锁店二 100 200 300 600 700
...全文
65 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2008-10-22
  • 打赏
  • 举报
 回复
在精华贴有例子
--行列互转

/******************************************************************************************************************************************************

以学生成绩为例子，比较形象易懂



整理人：中国风(Roy)



日期:2008.06.06

******************************************************************************************************************************************************/



--1、行互列

--> --> (Roy)生成測試數據

 

if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)

Insert Class

select N'张三',N'语文',78 union all

select N'张三',N'数学',87 union all

select N'张三',N'英语',82 union all

select N'张三',N'物理',90 union all

select N'李四',N'语文',65 union all

select N'李四',N'数学',77 union all

select N'李四',N'英语',65 union all

select N'李四',N'物理',85 

Go

--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+' from Class group by [Student]')





生成静态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 

from 

    Class 

group by [Student]



GO

动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]

exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')



生成静态:

select * 

from 

    Class 

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student 数学          物理          英语          语文

------- ----------- ----------- ----------- -----------

李四      77          85          65          65

张三      87          90          82          78



(2 行受影响)

*/



------------------------------------------------------------------------------------------

go

--加上总成绩(学科平均分)



--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))



生成动态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end),

    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))

from 

    Class 

group by [Student]



go



--2005方法:



动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号

exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 

pivot (max([Score]) for [Course] in('+@s+'))b ')



生成静态:



select 

    [Student],[数学],[物理],[英语],[语文],[总成绩] 

from 

    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 



生成格式:



/*

Student 数学          物理          英语          语文          总成绩

------- ----------- ----------- ----------- ----------- -----------

李四      77          85          65          65          292

张三      87          90          82          78          337



(2 行受影响)

*/



go



--2、列转行

--> --> (Roy)生成測試數據

 

if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)

Insert Class

select N'李四',77,85,65,65 union all

select N'张三',87,90,82,78

Go



--2000:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all

+',[Score]='+quotename(Name)+' from Class'

from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列

order by Colid

exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序



生成静态:

select * 

from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 

select [Student],[Course]='物理',[Score]=[物理] from Class union all 

select [Student],[Course]='英语',[Score]=[英语] from Class union all 

select [Student],[Course]='语文',[Score]=[语文] from Class)t 

order by [Student],[Course]



go

--2005:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+',','')+quotename(Name)

from syscolumns where ID=object_id('Class') and Name not in('Student') 

order by Colid

exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')



go

select 

    Student,[Course],[Score] 

from 

    Class 

unpivot 

    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student Course Score

------- ------- -----------

李四      数学      77

李四      物理      85

李四      英语      65

李四      语文      65

张三      数学      87

张三      物理      90

张三      英语      82

张三      语文      78



(8 行受影响)

*/
appleller 2008-10-22
  • 打赏
  • 举报
 回复
mark
gobelove 2008-10-22
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 wufeng4552 的回复:]
SQL code普通行列转换
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)…
[/Quote]


非常感谢。。。。。一下子学了点实用的东西
gobelove 2008-10-22
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 happyflystone 的回复:]
SQL codecreate table product(pid int primary key identity(1,1),店名 nvarchar(20)not null,产品名称 nvarchar(20)not null,进货 int)
insert into product(店名,产品名称,进货)values('连锁店一','商品一',200)
insert into product(店名,产品名称,进货)values('连锁店一','商品二',100)
insert into product(店名,产品名称,进货)values('连锁店一','商品三',300)
insert into product(店名,产品名称,进货)values('连…
[/Quote]


谢谢。
水族杰纶 2008-10-22
  • 打赏
  • 举报
 回复 
普通行列转换

问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

---- ---- ---- ----

李四 74   84   94

张三 74   83   93

-------------------

*/



create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)

go



--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名



--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')



---------------------------------



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

---- ---- ---- ---- ------ ----

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理,

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

from tb

group by 姓名



--SQL SERVER 2000 动态SQL。

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb    



------------------

------------------



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

---- ---- ----

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

--------------

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

union all

select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

union all

select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



--------------------

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

---- ------ ------

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

------------------

*/



select * from

(

select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

union all

select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

union all

select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

union all

select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

union all

select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
-狙击手- 2008-10-22
  • 打赏
  • 举报
 回复 
create table product(pid int primary key identity(1,1),店名 nvarchar(20)not null,产品名称 nvarchar(20)not null,进货 int) 

insert into product(店名,产品名称,进货)values('连锁店一','商品一',200) 

insert into product(店名,产品名称,进货)values('连锁店一','商品二',100) 

insert into product(店名,产品名称,进货)values('连锁店一','商品三',300) 

insert into product(店名,产品名称,进货)values('连锁店一','商品四',400) 

insert into product(店名,产品名称,进货)values('连锁店一','商品五',200) 

insert into product(店名,产品名称,进货)values('连锁店二','商品一',100) 

insert into product(店名,产品名称,进货)values('连锁店二','商品二',200) 

insert into product(店名,产品名称,进货)values('连锁店二','商品三',300) 

insert into product(店名,产品名称,进货)values('连锁店二','商品四',600) 

insert into product(店名,产品名称,进货)values('连锁店二','商品五',700) 

go

declare @s varchar(8000)



select @s = isnull(@s+',','')+'['+产品名称+'] = sum(case when 产品名称 = '''+产品名称+''' then 进货 else 0 end)'

from (select distinct 产品名称 from product) a



exec('select 店名,'+@s + ' from product group by 店名')

/*

店名                   商品二         商品三         商品四         商品五         商品一

-------------------- ----------- ----------- ----------- ----------- -----------

连锁店二                 200         300         600         700         100

连锁店一                 100         300         400         200         200





(2 行受影响)

*/

drop table product
mathsfan 2008-10-22
  • 打赏
  • 举报
 回复
行列转换？呵呵，LS回答：）
数据库执行SQL语句跟踪
不管你是刚学习软件开发的初学者，还是在IT职场打拼多年的职场老鸟，都免不了要同各种数据库打交道，你可能会碰到以下几个问题：1、感觉自己写的sql语句没有错，但是执行结果总是不对；2、某个功能执行的语句知道有...
MySQL一条SQL语句查询多条统计结果
 今天在写H5端接口时，我想换种方式查，也就是通过一条SQL查询出多个状态的订单数量。在网上搜了搜，方法可行，所以就尝试了下，果不其然成功了。  示例如下（数据只为掩饰今天的问题，表设计并不严谨。勿怪）： ...
为什么this（）和super（）必须是构造函数中的第一条语句？
Java要求，如果您在构造函数中调用this（）或super（），则它必须是第一条语句。 为什么？ 例如： public class MyClass { public MyCla
python(3)将·一个·语句分成多行
语法上可行的备注将一个语句分成多行，后面加上"\"符号， Python 解释器会将下一行的语句视为这一行的语句。特别注意：在"\"符号右边不可加上任何符号或文字，即使是注释符号也不允许。 2.运行结果 这里我不仅...
mysql语句统计总数_SQL一条语句统计记录总数及各状态数
SQL一条语句统计记录总数及各状态数方法一、代码如下:SELECT SUM(正确数)+SUM(错误数) AS 总记录数,SUM(正确数),SUM(错误数)FROM (SELECT COUNT(1) 正确数,0 错误数FROM TBWHERE STATUS=1UNION ALLSELECT 0 正确数,...
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章