联通笔试题,写给大家参考一下 表中内容 查询结果
a 9 10 a 9 45
b 9 15 b 9 40
c 9 20 c 9 35
d 9 10 d 9 45
a 9.5 30 a 9.5 45
b 9.5 20 b 9.5 55
c 9.5 10 c 9.5 65
d 9.5 15 d 9.5 60
a 9.8 15 a 9.8 75
b 9.8 20 b 9.8 70
c 9.8 30 c 9.8 60
d 9.8 25 d 9.8 65
a 9 10 a 9 45
b 9 15 b 9 40
c 9 20 c 9 35
d 9 10 d 9 45
a 9.5 30 a 9.5 45
b 9.5 20 b 9.5 55
c 9.5 10 c 9.5 65
d 9.5 15 d 9.5 60
a 9.8 15 a 9.8 75
b 9.8 20 b 9.8 70
c 9.8 30 c 9.8 60
d 9.8 25 d 9.8 65
解决方案 »
- sql server sum()
- 紧急!!!手误执行了DROP Table。表中有500万的数据怎么恢复???
- select * into from 记录顺序怎么不一致? 万分火急!定重谢!
- 大家好,请教一下学习SQL Server看什么书比较合适呀?那位能推荐一下。
- sql server2000 事务复制
- 怎样把数据库中的表转成文件存放在硬盘上???
- 大家好,大数据量数据记录条数的查询, 邹建老师请留步
- 本机可以远程虚拟机的sql server2008,但虚拟机无法远程本机的sql server2008
- Help!!!
- 如何用语句导入导出数据
- sql数据导出表时,字段的默认时会改变!
- 急!利用sp_attach_db还原数据库问题
from tablename
(a Char(1),
b Numeric(10,1),
c Int)
Insert TEST Select 'a', 9, 10
Union All Select 'b', 9, 15
Union All Select 'c', 9, 20
Union All Select 'd', 9, 10
Union All Select 'a', 9.5, 30
Union All Select 'b', 9.5, 20
Union All Select 'c', 9.5, 10
Union All Select 'd', 9.5, 15
Union All Select 'a', 9.8, 15
Union All Select 'b', 9.8, 20
Union All Select 'c', 9.8, 30
Union All Select 'd', 9.8, 25
GO
Select
a,
b,
(Case b When 9.0 Then 55-c When 9.5 Then 75-c When 9.8 Then 90-c End) As c
From TEST
GO
Drop Table TEST
--Result
/*
a b c
a 9.0 45
b 9.0 40
c 9.0 35
d 9.0 45
a 9.5 45
b 9.5 55
c 9.5 65
d 9.5 60
a 9.8 75
b 9.8 70
c 9.8 60
d 9.8 65
*/
查询结果第3列是一个和,是表中第1列不同而第2列相同的行的第3列的和SELECT a.c1, a.c2, b.c3 - a.c3 AS c3
FROM TEST a INNER JOIN
(SELECT c2, SUM(c3) AS c3
FROM test
GROUP BY c2) b ON b.c2 = a.c2
Insert @a Select 'a', 9, 10
Union All Select 'b', 9, 15
Union All Select 'c', 9, 20
Union All Select 'd', 9, 10
Union All Select 'a', 9.5, 30
Union All Select 'b', 9.5, 20
Union All Select 'c', 9.5, 10
Union All Select 'd', 9.5, 15
Union All Select 'a', 9.8, 15
Union All Select 'b', 9.8, 20
Union All Select 'c', 9.8, 30
Union All Select 'd', 9.8, 25
select a,b,c=(select sum(c) from @a where a<>b.a and b=b.b) from @a b
create table yyy(co1 char(1),co2 numeric(10,1) ,co3 int)
insert into yyy select 'a', 9 , 10
union select 'b', 9 , 15
union select 'c' , 9 , 20
union select 'd' , 9 , 10
union select 'a' , 9.5, 30
union select 'b' , 9.5, 20
union select 'c' , 9.5 , 10
union select 'd' , 9.5, 15
union select 'a' , 9.8 , 15
union select 'b' , 9.8 , 20
union select 'c' , 9.8, 30
union select 'd' , 9.8 , 25 select co1,co2,co3=(select sum(co3) from yyy where co2=a.co2 and co1<>a.co1) from yyy a order by co2
abcd和系数作为一组,然后检索出除自己本身以外的数值的和。
a 9 (b+c+d的第三列)
create table tt
(
id1 varchar(1) ,
id2 numeric(10,2),
id3 int
)insert into tt
select 'a', 9, 10 union
select 'b', 9, 15 union
select 'c', 9, 20 union
select 'd', 9, 10 union
select 'a', 9.5, 30 union
select 'b', 9.5, 20 union
select 'c', 9.5, 10 union
select 'd', 9.5, 15 union
select 'a', 9.8, 15 union
select 'b', 9.8, 20 union
select 'c', 9.8, 30 union
select 'd', 9.8, 25
select b.id1 , b.id2 ,
(select sum(isnull(a.id3,0)) from tt a where a.id2 = b.id2 and a.id3 <> b.id3) as id3
from tt b
order by 2drop table tt