一个困扰我很久的SQL2005语句

2010-09-25 05:14:04 176 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

如何设置远程访问SQL Server2005 SQL Server2005不能进行远程连接问题困扰了我好几天，今天终于把这个问题给解决了！再这个过程中在网上我查找了很多资料，发现有很多方法都不能很好的解决问题，我将其整理如下：首先查看端口，看有没1433(SQL Server的缺省端口)1、运行->cmd->netstat –ano看是否有TCP 0.0.0.0:1433 0.0.0.0:0 阅读详情

表
create table textInfo
(
id int primary key identity(1,1),
gId int FOREIGN KEY REFERENCES gradeInfo(id),
cId int FOREIGN KEY REFERENCES classInfo(id),
sno varchar(50),
testName varchar(50),
score varchar(50),
textTime varchar(50),
subjectName varchar(50),
bz varchar(50)
)

语句：
select
[sno],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [sno]) from textinfo) a
pivot
(max([Score]) for [subjectName] in([数学],[物理],[英语],[语文]))b

显示的是
sno 数学物理英语语文总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 null null null 292
李四 null 85 null null 292
李四 null null 65 null 292
李四 null null null 65 292

怎么不显示

sno 数学物理英语语文总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292

...全文

20220601超简单百度地图街景图片爬取+绿视率计算真的不难咯。阅读详情

zhujunyanging

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

hxy77142490 2010-12-15

打赏
举报

这个问题也困扰我很久了.郁闷... 当时我只能修改页面的属性来适应数据库的,谢谢2楼高人

zhujunyanging 2010-09-26

打赏
举报

怎么总是不对啊，结果还是显示那个

「已注销」 2010-09-25

打赏
举报

[总成绩]=sum([Score])over(partition by [sno]) from textinfo
=====================
over(partition by)是不分组的，注意跟group by的区别！！！！！

「已注销」 2010-09-25

打赏
举报

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

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

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

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

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

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

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

go



select 姓名 , [语文] , [数学] , [物理] ,总成绩=[语文]+[数学]+ [物理] from tb  

pivot(max(分数) for 课程 in([语文] , [数学] , [物理])) pvt



姓名         语文          数学          物理          总成绩

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

张三         74          83          93          250

李四         74          84          94          252



(2 row(s) affected)

zhujunyanging 2010-09-25

打赏
举报

1楼的我试了还是那个情

况哇

ws_hgo 2010-09-25

打赏
举报

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)



select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]

declare @sql varchar(1000)

set @sql='select UserName'

select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a



set @sql = @sql + ' from tb group by UserName'

print @sql

exec(@sql)



--讲解:



--这个是第一次执行

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学],

--这个是第二次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] ,

--这个是第三次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文]

--这个的数量来自于

(select distinct 课程 from tb)--这里只有3们课程



create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go



create function dbo.f_str(@id int) returns varchar(100)

as

begin

    declare @str varchar(1000)

    set @str = ''

    select @str = @str + ',' + cast(value as varchar) from tb where id = @id

    set @str = right(@str , len(@str) - 1)

    return @str

end

go



--调用函数

select id , value = dbo.f_str(id) from tb group by id



drop function dbo.f_str

drop table tb





我只说一个地方

select @str = @str + ',' + cast(value as varchar) from tb where id = @id

你把这个看懂就明白了

例如当@id=1

select @str = @str + ',' + cast(value as varchar) from tb where id = 1

把满足id=1的str值通过','累加

当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后

在查询满足2的直到所有的ID完为止

这样明白了吧

jstoic 2010-09-25

打赏
举报

[Quote=引用 1 楼 dawugui 的回复:]
SQL code

/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
……
[/Quote]

bancxc 2010-09-25

打赏
举报

group by lisi

dawugui 2010-09-25

打赏
举报



/*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。



问题：假设有张学生成绩表(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 tb a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

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

set @sql = '[' + @sql + ']'

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

一个困扰我很久的SQL2005语句

相关推荐