查询二个表中的相同字段的值并计数每个值对应表的记录数(求助)

[h20010826t]

a表数据
id sessionid
1 a
2 a
3 b
4 b

b表 数据
id sessionid
1 a
2 b
3 c

要输出
sessionid count_a count_b
a 2 1
b 2 1
c 0 1

[wwwwb]

select * from (
select sessionid from a
union
select sessionid from b) c
left join
(
select sessionid,sum(if(bz='1',1,0)) as ac,sum(if(bz='2',1,0)) as bc from (
select '1' as bz,* from a
union
select '2',* from a) group by sessionid) d
on c.sessionid=d.sessionid

[h20010826t]

select '1' as bz,* from a 这句是什么意思? 有错的吧?

[h20010826t]

[Quote=引用 1 楼 wwwwb 的回复:]

select * from (
select sessionid from a
union
select sessionid from b) c
left join
(
select sessionid,sum(if(bz='1',1,0)) as ac,sum(if(bz='2',1,0)) as bc from (
select '1' as bz,* from a
uni……
[/Quote]
红色的那句话好像有错...

[wwwwb]

[Quote=引用 2 楼 h20010826t 的回复:]
select '1' as bz,* from a 这句是什么意思? 有错的吧?
[/Quote]
测试一下不就知道了，将A、B两表的记录区别开

[h20010826t]

[Quote=引用 4 楼 wwwwb 的回复:]

引用 2 楼 h20010826t 的回复:
select '1' as bz,* from a 这句是什么意思? 有错的吧?

测试一下不就知道了，将A、B两表的记录区别开
[/Quote]
bz=2好像永远不可能

[wwwwb]

select '2',* from b
表名写错了

[h20010826t]

[Quote=引用 6 楼 wwwwb 的回复:]
select '2',* from b
表名写错了
[/Quote]

OK 对了 谢谢哈