4、表A定义如下:
属性 类型
Year Integer
Quarter Varchar(30)
Amount floatYear Quarter Amount
2000 1 1.1
2000 2 1.2
2000 3 1.3
2000 4 1.4
2001 1 2.1
2001 2 2.2
2001 3 2.3
2001 4 2.4
其中每行表表示一个季度的数据。如果处理表A中的数据,得到如下的结果。
Year Quarter1 Quarter2 Quarter3 Quarter4
2000 1.1 1.2 1.3 1.4
2001 2.1 2.2 2.3 2.4
请用SQL写一段代码实现。create table tab(Year1 int,Quarte varchar(30),Amountfloat decimal(18,1))
go
insert tab values(2000,'1',1.1)
insert tab values(2000,'2',1.2)
insert tab values(2000,'3',1.3)
insert tab values(2000,'4',1.4)
insert tab values(2001,'1',2.1)
insert tab values(2001,'2',2.2)
insert tab values(2001,'3',2.3)
insert tab values(2001,'4',2.4)
go--查看结果
declare @sql varchar(2000)
set @sql='select Year1'
select @sql=@sql+',[Quarte'+convert(varchar(20),Quarte)+']=isnull(sum(case when Quarte='+convert(varchar(20),Quarte)+' then Amountfloat end),0)'
from tab
group by Quarteselect @sql=@sql+' from tab group by Year1'
print @sqlexec(@sql)
go--删除
drop table tab
解决方案 »
- 代码如下:我错在哪里,该怎么改!请高手指点
- 怎么样改变表自动增加值的字段的初始值啊?
- 表更新问题:
- 我需要根据一表中的某个值的记录是否为0(或存在),进行下一步具体工作,请问哪个更适合.为什么?
- 连接查询写成这种形式好不好呢select tbl1.aaa,tbl2.bbb from tbl1,tbl2 where tbl1.id=tbl2.id
- sql server2000怎么在xp home上安装提示说内部错误呀
- excel用sql语句查询的简单问题,请教!
- 关于SQL2000联接问题
- Microsoft® SQL Server™ 2000 中文版,支持几个并发链接?????
- SQL SERVER是用7.0好呢还是用2000?现在公司一般用什么?
- access能远程连接SQL Server数据库吗?
- 求一sql语句,哪位大哥帮忙啊
--没测试
update a set a.charge=sum(charge) from paymen a,bill b
where a.pay_id=b.pay_id
--group by a.pay_id2.搜索“树”3.建议使用分页存储过程,请搜索“分页存储过程”
(st varchar(20),ed varchar(20),km int)insert t values ('A','B',1000)
insert t values ('A','C',1100)
insert t values ('A','D',900)
insert t values ('A','E',400)
insert t values ('B','D',300)
insert t values ('D','F',600)
insert t values ('E','A',400)
insert t values ('F','G',1000)
insert t values ('C','B',600)
go
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
declare @i int
set @i=1
insert @t select st+'-'+ed,*,@i from t where st=@col
while exists (select * from t a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
return
end
goselect col,km from dbo.f_go('A')drop function f_go
drop table tcol km
------------------------------ -----------
A-B 1000
A-C 1100
A-D 900
A-E 400
A-B-D 1300
A-C-B 1700
A-D-F 1500
A-E-A 800
A-C-B-D 2000
A-B-D-F 1900
A-D-F-G 2500
A-C-B-D-F 2600
A-B-D-F-G 2900
A-C-B-D-F-G 3600(所影响的行数为 14 行)
属性 类型 备注
Id Integer PK
Name Varchar(30)
State char(3)
表A中现有1000万条记录,如果客户端要通过分页的方式从表A中取数据,其中每页20条
记录,有几种方法?描述每种方法如何处理,有什么优缺点?用SQL写出实现代码。------用老大的方法
查询 X页第y行记录:
1.select top y * from 表a where id not in(select top (x-1)*y id from 表a)
2.select id2=identity(int,1,1),* into #tb from 表a
select * from #tb where id2 between (x-1)*y and (x*y-1)
create table #bill(bill_id integer,charge integer,pay_id integer)
create table #payment(pay_id integer,charge integer)
insert into #bill
select 001,580,801 union
select 001,630,801 union
select 002,760,802 union
select 003,430,803
insert into #payment
select 801,580 union all
select 802,760 union all
select 803,430 select * from #bill
select * from #paymentupdate #payment
set charge= a.ab from #payment,
(select sum(charge)ab,pay_id from #bill group by pay_id)a
where #payment.pay_id=a.pay_id
select * from #paymentdrop table #bill
drop table #payment
insert @a values(2000,'2',1.2)
insert @a values(2000,'3',1.3)
insert @a values(2000,'4',1.4)
insert @a values(2001,'1',2.1)
insert @a values(2001,'2',2.2)
insert @a values(2001,'3',2.3)
insert @a values(2001,'4',2.4)
select*from @aselect year1,sum(case quarte when '1' then amountfloat else 0 end),
sum(case quarte when '2' then amountfloat else 0 end),
sum(case quarte when '3' then amountfloat else 0 end),
sum(case quarte when '4' then amountfloat else 0 end)
from @a
group by year1
declare @bill table(bill_id integer,charge integer,pay_id integer)
declare @payment table(pay_id integer,charge integer)
insert into @bill
select 001,580,801 union
select 001,630,801 union
select 002,760,802 union
select 003,430,803
insert into @payment
select 801,580 union all
select 802,760 union all
select 803,430
select * from @bill
select * from @payment
update a
set a.charge=b.c
from @payment a,(select pay_id,sum(x.charge) c from @bill x
group by pay_id) b
where a.pay_id=b.pay_id
select * from @payment
第四题
drop table tab
create table tab(Year int,Quarte varchar(30),Amountfloat decimal(18,1))
insert tab values(2000,'1',1.1)
insert tab values(2000,'2',1.2)
insert tab values(2000,'3',1.3)
insert tab values(2000,'4',1.4)
insert tab values(2001,'1',2.1)
insert tab values(2001,'2',2.2)
insert tab values(2001,'3',2.3)
insert tab values(2001,'4',2.4)
select * from tab
select year,sum(case when quarte='1' then amountfloat else 0 end) 'Quarter1',
sum(case when quarte='2' then amountfloat else 0 end) 'Quarter2',
sum(case when quarte='3' then amountfloat else 0 end) 'Quarter3',
sum(case when quarte='4' then amountfloat else 0 end) 'Quarter4'
from tab
group by year
update payment
set charge=(select sum(charge) from bill where bill.pay_id=payment.pay_id)
create table #a([year] int,[quarter] int,amount float)
insert #a select 2000,1,1.1
insert #a select 2000,2,1.2
insert #a select 2000,3,1.3
insert #a select 2000,4,1.4
insert #a select 2001,1,2.1
insert #a select 2001,2,2.2
insert #a select 2001,3,2.3
insert #a select 2001,4,2.4select [year],
max((case [quarter] when 1 then amount end)) as 'amount1',
max((case [quarter] when 2 then amount end)) as 'amount2',
max((case [quarter] when 3 then amount end)) as 'amount3',
max((case [quarter] when 4 then amount end)) as 'amount4'
from #a
group by [year]