mysql多表查询的问题！

公子月

有三张表结构完全一样，我想在三张表的内容和里面查看排序的记录。我写语句是
select count(*) as num,mailto,INET_NTOA(ip)  from mail_log_200705_1 group by mailto having count(*) >1 order by num) union  (select count(*) as num,mailto,INET_NTOA(ip) from mail_log_200705_2 group by mailto having count(*) >1 ) union (select count(*) as num,mailto,INET_NTOA(ip) from mail_log_200705_3 group by mailto having count(*) >1) order by num desc limit 100

这条语句能够显示mailto中地址最多的前100名。但是只能把三张表的结果放在一起显示，里面会有重复的mailto地址。我想要三张表的结果中group by 和order by 的结果。
不知道有没有好的方法。

cooljia

select sum(num), mailto from (
(select count(*) as num,mailto from mail_log_200705_1 group by mailto having count(*) >1 order by num) union  (select count(*) as num,mailto from mail_log_200705_2 group by mailto having count(*) >1 ) union (select count(*) as num,mailto from mail_log_200705_3 group by mailto having count(*) >1)
) group by mailto order by num desc limit 100

公子月

深水老怪，好像不行啊。
ERROR 1248 (42000): Every derived table must have its own alias

公子月

没有答案吗？

cooljia

select sum(num), mailto from (
(select count(*) as num,mailto from mail_log_200705_1 group by mailto having count(*) >1 order by num) union  (select count(*) as num,mailto from mail_log_200705_2 group by mailto having count(*) >1 ) union (select count(*) as num,mailto from mail_log_200705_3 group by mailto having count(*) >1)
) as tt group by mailto order by num desc limit 100

as tt还是就只有tt,忘记了, 你自己试试.

公子月

还是不行啊。报错同上。from后那个大括号里能当作一个表查询吗？加不加as都报那个错。

公子月

和mysql版本有关系吗？我的是version: 4.1.7和4.0.20

cooljia

不清楚是否是mysql的版本问题, 我这里是5.1.11-beta, for Win32 (ia32)


CREATE TABLE `xx` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `xx` (`id`, `name`) VALUES
(1, 'mike'),
(2, 'paul'),
(2, 'mike'),
(2, 'mike'),
(2, 'mike');



CREATE TABLE `yy` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `yy` (`id`, `name`) VALUES
(1, 'john'),
(2, 'samson'),
(2, 'mike'),
(2, 'mike'),
(2, 'mike'),
(2, 'john'),
(2, 'john'),
(2, 'john');

select sum(num) as snum, name from (select count(*) as num, name from xx group by name union select count(*) as num, name from yy group by name) as tt group by name order by snum asc limit 100;

youxi_2006

？？？

是想三张表数据混在一起，然后再排个TOP出来？

公子月

是和版本有关系，我自己安装的mysql测试没有问题。是客户的mysql版本的问题啊。唉。真是无奈啊。还没哟查到version: 4.0.20到底不支持哪个语法呢。多谢老怪了。