帮忙写一个SQL语句(100)

Q

qumingfei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #1

select a_class,count(*) from a 结果: 甲 12 乙 10select a_class,count(*) from a where a_ok=1结果: 甲 5 乙 7 问：我想显示 结果: 甲 5 12 乙 7 10 该怎样写SQL？

 

仙

仙界蚂蚁

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #2

嵌套sql语句就可以了

 

Q

qumingfei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #3

能不能大概帮我写写啊。

 

仙

仙界蚂蚁

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #4

select a1.a_class,count(a1.*),(select count(b.*) from a b where b.a_class=a1.a_class) from a a1大致就是这样的

 

仙

仙界蚂蚁

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #5

select a1.a_class,count(a1.*),(select count(b.*) from a b where b.a_class=a1.a_class and b.a_ok=1) from a a1大致就是这样的

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-07

• #6

楼主你写的SQL语句根本就不正确吧?没有GROUP BY 阿!

 

Z

zdwwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-08

• #7

select a_class,count(*) ,sum(case when a_ok=1then 1 else 0 end) from a group by a_class

 

K

killbill2010

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-09

• #8

看问题之前没有看其他楼select t1.a_class,t2.cnt2,t1.cnt1from (select a_class,count(*) cnt1 from a) t1, (select a_class,count(*) cnt2 from a where a_ok=1) t2where t1.a_class=t2.a_class切记a_class必须是唯一的，不然这个连接就是错误的

 

A

antonyzhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2010-10-14

• #9

select a_class,count(*),count(a_class1) from ( select *,case when a_ok = 1 then a_class else '' end a_class1 from a) bgroup by a_class