面试时的三道纯数据库题目，请问怎么回答，谢谢！

[milunhailili]

1. 一个表A，有一字段B，请写出A中重复的字段，并计算出有多少个重复的？

2.有一个表A，主键ID为累加字段并升序排列，请写出第76到第95条记录？

3. 以下ID为数据表A主键，B为另一字段，要求根据1得出2的结果(前几项的累加和)：

1：

ID B
1 1.5
2 3
3 4

2:

ID sum
1 1.5
2 4.5
3 8.5


请问应该怎么用SQL语言写出来，谢谢了！

[SarahCla]

1. select count(*),b
from A
group by b
having count(*)>1

[SarahCla]

2. select c.id,c.B
from A as c
Where (select count(*) from A where ID<=c.ID)>=76 and
(select count(*) from A where ID<=c.ID)<=95

[houfuzhu]

有点意思，好像题目不太明确
关注

[milunhailili]

谢谢 ，自己再顶一下，看还有没有别人的意见！

[qiujsh]

前两个不想看了，试了一下第三了，我建了一个表叫test,有两个字段，一个ID int,一个BB float,
用下边的语句:

SELECT ID2, SUM(BB) AS BB
FROM (SELECT A.ID AS ID1, B.ID AS ID2, A.BB
FROM Test A CROSS JOIN
Test B) DERIVEDTBL
WHERE (ID1 <= ID2)
GROUP BY ID2

[lw549]

表中怎么可能有重复的字段?

[chenchangfu]

1. select Sum(b)
from (select count(*)as b
from A
group by b
having count(*)>1)
2.用fancylee(小恐龙) 的
3
select ID, sum(B)
from (select A.ID, C.B
from A, a as C
where A.ID >= C.ID)
Group by ID

[人鱼传说]

小恐龙好像没看清题目吧,应该是
select * from A where id between 76 and 95
第三题应该是没例表其他字段了
select ID,b1,b2..bn,b1+b2+..+bn as 结果 from A

[ghs79]

第三个问题
select a.id id, (select sum(sum) from 表a where id <= a.id)
from 表a a
group by id

[qzxyd]

二：
select top 20 *
from a
where id not in (select top 75 id from a )

[hhzqf1980]

2 select top 10 * from (select top 95 * from a order by id) order by id desc

[guojunhao1983]

????????????

[chenzunshi2]

http://www.somade.com/是个很专业的技术社区,去那里找找吧,或许有你要的答案~

[sdqhlyf]

bz

[Teng_s2000]

chenzunshi2(陈)
不要到处发广告啊！

第三提(access中调试的)
SELECT ID, (select sum(B) from test a where a.ID<=b.ID) AS [sum]
FROM test AS b;

ID sum
1 1.5
2 4.5
3 8.5

[dengyueguang]

帮你顶下！

[liufuyahong]

1.select count(B) from A group by B having count(B)>1
2.select top 20 * from A
where ID not in(select top 75 ID from A order by ID)
and ID in(select top 95 ID from A order by ID)
3.select ID,
(select sum(B) from A where ID<=O.ID) sum
from A O
order by ID

[liufuyahong]

1.第二份
select count(1) TotalCount from(
select count(B) Repeat from A group by B having count(B)>1
)C

[liufuyahong]

1.第二份应是
select sumt(1) TotalCount from(
select count(B) Repeat from A group by B having count(B)>1
)C