求 d7+access数据库的sql(交叉表)(100)

H

huxhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-06-28

• #1

求 d7+access数据库的sql编号 名称 区域 a001 aa 1a002 bb 2a003 cc 1a004 dd 3a005 ee 4a006 ff 2a007 gg 1.....如何sql统计出“区域”为1的个数是3“区域”为2的个数是2“区域”为3的个数是1“区域”为4的个数是1“区域”为5的个数是0我的本办法是分别5次查区域=1，2，3，4，5sql.text := 'select count( 区域 ) as qy0_s_0 from LS_ies ' + ' where 名称<>"" and 区域 =1 ';求一次查出的办法。

 

轻

轻舞肥羊

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-06-28

• #2

如果不算为0的,这是最简单的select 区域,count(*) from LS_ies group by 区域如果数量为0的区域也要查出,就这样select 区域,count(*)-1 from (select 区域 from LS_ies where 名称<>''union allselect 1 from (select count(*) from LS_ies)union allselect 2 from (select count(*) from LS_ies)union allselect 3 from (select count(*) from LS_ies)union allselect 4 from (select count(*) from LS_ies)union allselect 5 from (select count(*) from LS_ies)) group by 区域

 

E

easykoala

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-06-29

• #3

access数据库有现成的交叉查询,用access设计器设计一下,然后查看SQL即可

 

帅

帅！你晕

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-06-30

• #4

楼二，：access数据库里不支持此种交叉表哟！

 

H

huxhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-07-01

• #5

二楼的不行，自己搞定了：sql.text := 'select '+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =0 ) as qy0_s_0,'+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =1 ) as qy1_s_0,'+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =2 ) as qy2_s_0,'+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =3 ) as qy3_s_0,'+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =4 ) as qy4_s_0,'+'(select count( 名称 ) from LS_ies where 名称<>"" and 区域 =5 ) as qy5_s_0'+'from LS_ies order by 区域 ';

 

E

easykoala

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-07-02

• #6

搞得这么复杂!加入一张区域表,含0至5的值,外连接分组计数便可

 

X

xnnfan126

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-07-02

• #7

楼主： 你的方法只能解燃眉之急，如果区域个数过多，或不却定的情况下，你又当如何呢？

 

H

huxhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-07-03

• #8

应该如何，请指点

 

M

malaya

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-07-03

• #9

select 区域,count( 区域 ) as qy0_s_0 from LS_ies group by 区域是这样的吗

 

H

huxhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-09-14

• #10

多人接受答案了。