求一条查询语句的写法。
表abc1 字段 id vlue1 表 abc2 字段 id value2
aaa 3 aaa 7
aaa 4 aaa 8
aaa 5 aaa 5
bbb 2 bbb 1
ccc 1 bbb 4
ccc 2 ccc 5
现在我需要查询结果 这样的 id count(abc1) count(abc2)
aaa 3 3
目的是：返回id在abc1和abc2中都有2条以上数据的项，并且把数据项多少统计出来。看结果只有 aaa满足，但我不知道存储怎么去写！求高人解决！
[解决办法]
select x.id,x.count1,y.count2
from (
select id,count(*) as count1 from a group by id having count*)> 2
) as x,(
select id,count(*) as count1 from b group by id having count*)> 2
) as y
where x.id=y.id
[解决办法]
Select
A.id,
A.abc1count,
B.abc2count
From
(Select id, Count(id) As abc1count From abc1 Group By id Having Count(id) > 2) A
Inner Join
(Select id, Count(id) As abc2count From abc2 Group By id Having Count(id) > 2) B
On A.id = B.id
[解决办法]
select a.id,count(a.vlue1),count(b.value2) from abc1 a inner join abc2 on a.id = b.id
where count(a.vlue1)> 2 and count(b.value2) > 2
group by a.id
[解决办法]
create table abc1
(
id varchar(10),
vlue1 int
)
create table abc2
(
id varchar(10),
vlue2 int
)
delete abc1
insert into abc1
select
'aaa ' , 3 union all select
'aaa ' , 4 union all select
'aaa ' , 5 union all select
'bbb ' , 2 union all select
'bbb ' , 2 union all select
'bbb ' , 2 union all select
'ccc ' ,1 union all select
'ccc ' ,2
insert into abc2
select
'aaa ' , 7 union all select
'aaa ' , 8 union all select
'aaa ' , 5 union all select
'bbb ' , 1 union all select
'bbb ' , 4 union all select
'ccc ' , 5
select A.id ,A.cnt1,B.cnt2 from
(select id ,count(1) as cnt1 from abc1 group by id) A
inner join
(select id ,count(1) as cnt2 from abc2 group by id) B
on A.id = B.id and A.cnt1> =2 and B.cnt2> =2
id cnt1 cnt2
aaa33
bbb32
[解决办法]
我是想再多问一下,如果不存在,能否将不存在的那张表也打印为 "0 "件出来呢?
[解决办法]
....
又跑來個 "海闊天空 "
