这样的sql语句？？？(100分)

C

coffeeffee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #1

有这样一个sql语句，需按分组统计几个字段的总和及字段不为0的数目，如下所示：
select sum(a3),sum(a4),count(a3),count(a4) from t1 group by a1,a2 ;
t1表有a1,a2,a3,a4四个字段，现在以a1,a2为条件进行分组，并以此为条件
统计a3,a4的总和及a3,a4中不为0的记录数,似乎用一条语句无法实现，请指教。

 

J

jqw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #2

select sum(a3),sum(a4) from t1
group by a1,a2
having count(a3)>0 and count(a4)>0;

 

G

g622

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #3

??假设a3,a4的默认值default=0;
select sum(a3),sum(a4),count(a3),count(a4) from t1
where a3<>0 and a4<>0 group by a1,a2

 

S

soho

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #4

确实用一条语句无法实现，因为，条件“a3,a4不为0”，使得sum(a3),sum(a4)
得出的结果不正确

 

F

fireocean

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #5

如果要对一个字段取SUM，且有ORDER BY 字句，则必须在ORDER BY 字句中包含该字段。正确
的SQL语句应该是：
select sum(a3),sum(a4),count(a3),count(a4) from t1 group by a1,a2，a3,a4;

 

X

xixi12359

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-10

• #6

一条SQL好象不行吧

 

W

wumeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-10

• #7

用两条语句实现。

 

S

sonie

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-10

• #8

怕是难，也许有办法，我回去想想

 

S

sonie

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-11

• #9

搞定:
select a.*,b.*,c.* from (select sum(a3) as sum_a3,sum(a4) as sum_a4 from
t1 group by a1,a2,a3,a4) a ,
(select count(a3) as count_a3 from t1 where a3<>0) b,
(select count(a4) as count_a from t1 where a3<>0) c
在sql server中已测试成功

 

L

lazy_cat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-11

• #10

cool!

 

C

coffeeffee

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-11-13

• #11

接受答案了.