按日期分类统计的SQL语句问提，在线等候。(50分)

P

pcw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #1

begin
ad.close;
ad.SQL.clear;
ad.SQL.add('select rq2,(select count(*) from bmry where bmry.cz=''A'') as ta,'); //datevalue(bmry.rq2) like '''+'%'+t1+'-'+t2+'%'' and
ad.SQL.add('(select count(*) from bmry where bmry.cz=''B'') as tb,');
ad.SQL.add('(select count(*) from bmry where bmry.cz=''C'') as tc,');
ad.SQL.add('(select count(*) from bmry where bmry.cz=''D'') as td,');
ad.SQL.add('(select count(*) from bmry where bmry.cz=''E'') as te,');
ad.SQL.add('(select count(*) from bmry where bmry.cz=''G'') as tg ');
ad.SQL.add('from bmry group by bmry.rq2');
ad.open;
end;

 

P

pcw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #2

以上A、B、C、D、E、G结果一样，是不正确的，请高手指正。

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #3

select rq2,sum(case when cz='A' then 1 else 0 end) as ta,
sum(case when cz='b' then 1 else 0 end),
....
from bmry
group by rq2

 

B

blackbook

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #4

同意 ugvanxk 的

 

P

pcw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #5

运行出现‘未指定错误’。不正常编译。

 

B

blackbook

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #6

你用的什么数据库？ 你把生成的sql语句单独用数据库的客户端工具调试一下 看 到底
提示什么问题才好给你回答的阿。

 

P

pcw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #7

用ACCESS2000数据库。

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #8

select rq2,sum(iif(cz='a',1,0)) as ta,
sum(iif(cz='b' ,1 0)),
....
from bmry
group by rq2

 

B

blackbook

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #9

ACCESS2000 桌面型数据库 不支持case ; 而且左连接不能超过两层，这就是桌面数据库的
缺点。sql语句如下，你自己修改一下 ：）

select a.*,b.tc from
(select a.*,b.tb from
(select a.*,b.ta from (select rq2 from bmry group by rp2 ) as a left join
(select rp2 count(*) as Ta from bmry where cz='A' group by rp2 ) as b on a.rp2=b.rp2 ) as a left join
(select rp2 count(*) as Tb from bmry where cz='B' group by rp2 ) as b on a.rp2=b.rp2 ) as a left join
(select rp2 count(*) as Tc from bmry where cz='C' group by rp2 ) as b on a.rp2=b.rp2

其余的你一层 一层加 我没有测试 你自己多试一试 思路是这样的。那位大哥 能不能 优化一下就好了。

 

B

blackbook

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #10

to ugvanxk:
厉害 iif这个函数 你也能查到。看来 我以前编的access的数据库程序终于可以优化了
：）

 

P

pcw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-07

• #11

ugvanxk的方法是可行的，在此多谢了。