比如: 某表有如下字段: 想得到如下结果集: id name id name count 1 aaa 1 aaa 2 1 aaa 2 bbb 2 2 bbb 3 ccc 3 2 bbb 3 ccc 3 ccc 多谢!多谢!
我太性急了,上面的例子改为: 某表1有如下字段: 表2有如下字段: 想得到如下结果集: id name id address id name address count 1 aaa 1 wuhan 1 aaa wuhan 2 1 aaa 2 beijing 2 bbb beijing 2 2 bbb 3 tianjing 3 ccc tianjing 2 2 bbb 3 ccc 3 ccc
select c.id,c.name,c.address ,count(c.id) as recordcount from (select a.id,a.name,b.address from tb1 a,tb2 b where a.id=b.id) c group by c.id,c.name,c.address
select a.id,a.name,b.address,count(a.id) as recordcount from a,b where a.id=b.id group by a.id,a.name,b.address
使用union all 连接
某表有如下字段: 想得到如下结果集:
id name id name count
1 aaa 1 aaa 2
1 aaa 2 bbb 2
2 bbb 3 ccc 3
2 bbb
3 ccc
3 ccc
多谢!多谢!
某表1有如下字段: 表2有如下字段: 想得到如下结果集:
id name id address id name address count
1 aaa 1 wuhan 1 aaa wuhan 2
1 aaa 2 beijing 2 bbb beijing 2
2 bbb 3 tianjing 3 ccc tianjing 2
2 bbb
3 ccc
3 ccc
from (select a.id,a.name,b.address from tb1 a,tb2 b where a.id=b.id) c
group by c.id,c.name,c.address
where a.id=b.id
group by a.id,a.name,b.address