高分求SQL语句(200分)

F

flai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #1

表结构1：

IDCD : 身份证号码
year : 缴费年
month: 缴费月
money: 缴交金额
......


key=IDCD+year+month(unique)

表结构2：

IDCD : 身份证号码
Village:村名称
......

key=IDCD(unique)


需要的查询结果为：每个村有几个人缴费，按村分组

这样的SQL语句怎么写呢？

谢谢！

 

S

sword_liu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #2

select count(a.*),b.village from tabel1 a,tabel2 b where b.idcd=a.idcd group by b.village

 

Y

yoking

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #3

select count(idcd),Village from table2 where idcd in (select idcd from table1 where year=? and month =?)
group by Village
如果不对
select count(idcd),Village from table2
group by Village
having( idcd in (select idcd from table1 where year=? and month =?))
这两句中的一个应该可以解决问题

 

Z

zhouyjfx

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #4

试试这句话
select b.village,count(distinct a.idcd) from table1 a,table2 b
where a.idcd=b.idcd and a.year=? and a.month=? and a.money>0
group by b.village order by b.village

 

X

xzh2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #5

select b.village,count(a.idcd) from table1 a,table2 b
where a.idcd=b.idcd group by b.village

 

S

sunrainwang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-12-30

• #6

因为表结构1存在可能的重复身份证号，所以因先选出表结构1唯一的身份证列表sql语句如下：

select a.Village,count(b.IDCD) countX
from tb2 a, (select distinct IDCD from tb1 ) b
where a.IDCD = b.IDCD
group by a.Village

 

F

flai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2003-01-02

• #7

多人接受答案了。