帮忙写一个sql语句(50分)

L

lhxu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #1

数据库结构
f1 f2 f3
---------------
a 1 5
a 1 6
a 2 6
a 2 7
a 3 9
b 4 5
b 5 9
...
我想得出这样得结果
a 24
b 0
后面得24 和 0是这样得到的
24是所有f1是a 的 ,而且f2字段有重复的记录的f3字段的总和 5+6+6+7

至于f1是b的,由于f2字段并无重复,所以为0

 

C

cAkk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #2

用一条SQL实现恐怕不太可能,主要是"f2字段有重复的记录"不好办.

 

B

bethouvnlue

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #3

正在想

 

L

lhxu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #4

忘了告诉大家,重复次数只有两次
用多条sql实现也可以

 

X

xiao.lit

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #5

不懂

 

C

cytown

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #6

select a.x,isnull(sum(a.y),0) as y from
(select f1 from table1 group by f1) b,
(select f1 as x, count(*) as z, sum(f3) as y
from table1 group by f1,f2 having count(*)>1) a
where b.f1*=a.x
group by b.f1

 

C

cAkk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #7

cytowm: "FROM子句语法错误"..

 

S

silly

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #8

create table tmp
(f1 char(10) null,
f2 int null,
f3 int null)

insert into tmp
select * from table
group by f1,f2
having count(*)>1

insert into tmp
select f1,f2,0 from table
group by f1,f2
having count(*)=1

select f1,sum(f3) from tmp
group by f1

drop table tmp

 

W

wgzhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #9

一个思路:
再添加一个字段f4,其数值全部为1,
select f1,f2,sum(f3),sum(f4) from table1
group by f1,f2
然后把f4不等于2的全部删除,或者filter一下,够简单的吧!

 

C

cytown

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #10

我用sql6.5, 没错呀???

 

C

cytown

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-10

• #11

啊, 是错了, 应该是select b.f1 as x才对

 

L

lhxu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 1999-11-11

• #12

多谢!