请教一个求职面试题：如何写一个SQL query求表中对角线的和

szwind 2008-04-26 01:41:00

...全文

249 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

szwind 2008-04-27

打赏
举报

高，太高了。

能麻烦您把其中的几个高难动作解说一下吗？有几个地方看不明白。

dawugui 2008-04-27

打赏
举报

[Quote=引用 13 楼 Limpire 的回复:]
还来个动态哈，如果我去面试：

create table a (a int)
insert a select 1
select sum(a) from a

如果考官要我搞什么动态：

drop table a

select '886'
[/Quote]
这个更NB.

建议还可采用其他语言.

如:select 'sa you la la'

Limpire 2008-04-27

打赏
举报

还来个动态哈，如果我去面试：

create table a (a int)
insert a select 1
select sum(a) from a

如果考官要我搞什么动态：

drop table a

select '886'

liushengpiaoxu 2008-04-27

打赏
举报

kakajya 2008-04-27

打赏
举报

[Quote=引用 8 楼 perfectaction 的回复:]
SQL code--查询右上角至左下角对角线数字之和
declare @sql varchar(max)
set @sql='select '
select @sql=@sql+'(select isnull('+name+','''') from T where id='+cast((select max(column_id) from sys.columns where object_id=object_id('T'))-column_id+1 as varchar(20))+
case when column_id=(select max(column_id) from sys.columns where object_id=object_id('T')) then ')' else ')+' end
from sys.columns w…
[/Quote]

真猛啊.
学习学习.

pest1999 2008-04-27

打赏
举报

数列求和吗
球第n行的第n个值的和
当然首先要n行n列存在
他就是这个意思

nzperfect 2008-04-26

打赏
举报

--查询右上角至左下角对角线数字之和

declare @sql varchar(max)

set @sql='select '

select @sql=@sql+'(select isnull('+name+','''') from T where id='+cast((select max(column_id) from sys.columns where object_id=object_id('T'))-column_id+1 as varchar(20))+

case when column_id=(select max(column_id) from sys.columns where object_id=object_id('T')) then ')' else ')+' end

from sys.columns where object_id=object_id('T') and column_id>1 order by column_id asc

select @sql

exec(@sql)

---

34

nzperfect 2008-04-26

打赏
举报

use tempdb

go

if object_id('tempdb.dbo.T') is not null drop table T

create table T (id int identity(1,1) primary key,a int,b int,c int,d int)

insert into T

select  1, 2, 3, 4 union all

select  5, 6, 7, 8 union all

select  9,10,11,12 union all

select 13,14,15,16



--查询左上角至右下角对角线数字之和

declare @sql varchar(max)

set @sql='select '

select @sql=@sql+'(select isnull('+name+','''') from T where id='+cast(column_id-1 as varchar(20))+

case when column_id=(select max(column_id) from sys.columns where object_id=object_id('T')) then ')' else ')+' end

from sys.columns where object_id=object_id('T') and column_id>1 order by column_id asc

exec(@sql)

----

34

tim_spac 2008-04-26

打赏
举报

1. 先从sysobjects中按顺序得到该表的各个字段
2. 拼动态脚本，并执行

Limpire 2008-04-26

打赏
举报

--> 测试数据: #T

if object_id('tempdb.dbo.#T') is not null drop table #T

create table #T (id int identity, a int,b int,c int,d int)

insert into #T

select  1, 2, 3, 4 union all

select  5, 6, 7, 8 union all

select  9,10,11,12 union all

select 13,14,15,16



select sum(case id when 1 then a when 2 then b when 3 then c when 4 then d end) from #T