MySQL union order by 超级无敌长查询sql语句

xiaoyu9805119

原文参见：http://hi.baidu.com/smallfish7788/blog/item/94970b25e1f76934c8955919.html

数据库中一共有30个表名类似，结构相同的表，从message_0 到 message_30，字段略为：

messageid int(9) PRI auto_increment
userid int(11)
content text

每个表中都有可能有userid=10000的记录，为了查询所有记录用到union语句，第一次拼出一个sql为：

select * from message_0 where userid=10000 union all select * from message_1 where userid=10000 union all select * from message_2 where userid=10000;

应用到union all，这个跟union区别就是不会去除重复数据，后面的类似添加：

union all select * from message_(数字序列) where senduserid=10000

拼出一个0-30之间超长无比的大sql，记录都可以查询出。现在又有个新问题，如果按照messageid排序呢。重新修改如下：

select * from(select * from message_0 where userid=10000 order by messageid desc) as tmp_0 union all select * from (select * from message_1 where userid=10000 order by messageid desc) as tmp_1 union all select * from (select * from message_2 where userid=10000 order by messageid desc) as tmp_2;

重点就是用as一个表名select * from (select * from table order by id desc) as tmpname，类似添加：

union all select * from (select * from message_(数字序列) where userid=10000 order by messageid desc) as tmp_(数字序列)

现在发现所有记录都可以按照messageid倒序排列了，OK！

这样的语法在MySQL里查询速度竟然还不慢，还可以这样查询。

最后贴出sql全文。绝对是我写的最长的sql语句了。无语，这绝对不是我想这样的。

select * from(select * from message_0 where userid=10000 order by messageid desc) as tmp_0 union all select * from (select * from message_1 where userid=10000 order by messageid desc) as tmp_1 union all select * from (select * from message_2 where userid=10000 order by messageid desc) as tmp_2 union all select * from (select * from message_3 where userid=10000 order by messageid desc) as tmp_3 union all select * from (select * from message_4 where userid=10000 order by messageid desc) as tmp_4 union all select * from (select * from message_5 where userid=10000 order by messageid desc) as tmp_5 union all select * from (select * from message_6 where userid=10000 order by messageid desc) as tmp_6 union all select * from (select * from message_7 where userid=10000 order by messageid desc) as tmp_7 union all select * from (select * from message_8 where userid=10000 order by messageid desc) as tmp_8 union all select * from (select * from message_9 where userid=10000 order by messageid desc) as tmp_9 union all select * from (select * from message_10 where userid=10000 order by messageid desc) as tmp_10 union all select * from (select * from message_11 where userid=10000 order by messageid desc) as tmp_11 union all select * from (select * from message_12 where userid=10000 order by messageid desc) as tmp_12 union all select * from (select * from message_13 where userid=10000 order by messageid desc) as tmp_13 union all select * from (select * from message_14 where userid=10000 order by messageid desc) as tmp_14 union all select * from (select * from message_15 where userid=10000 order by messageid desc) as tmp_15 union all select * from (select * from message_16 where userid=10000 order by messageid desc) as tmp_16 union all select * from (select * from message_17 where userid=10000 order by messageid desc) as tmp_17 union all select * from (select * from message_18 where userid=10000 order by messageid desc) as tmp_18 union all select * from (select * from message_19 where userid=10000 order by messageid desc) as tmp_19 union all select * from (select * from message_20 where userid=10000 order by messageid desc) as tmp_20 union all select * from (select * from message_21 where userid=10000 order by messageid desc) as tmp_21 union all select * from (select * from message_22 where userid=10000 order by messageid desc) as tmp_22 union all select * from (select * from message_23 where userid=10000 order by messageid desc) as tmp_23 union all select * from (select * from message_24 where userid=10000 order by messageid desc) as tmp_24 union all select * from (select * from message_25 where userid=10000 order by messageid desc) as tmp_25 union all select * from (select * from message_26 where userid=10000 order by messageid desc) as tmp_26 union all select * from (select * from message_27 where userid=10000 order by messageid desc) as tmp_27 union all select * from (select * from message_28 where userid=10000 order by messageid desc) as tmp_28 union all select * from (select * from message_29 where userid=10000 order by messageid desc) as tmp_29 union all select * from (select * from message_30 where userid=10000 order by messageid desc) as tmp_30;

ruochen

sql语句追求短而不是长

xiaoyu9805119

原帖由 ruochen 于 2009-1-21 11:57 发表
sql语句追求短而不是长

只是为了显示下mysql还能这般用，没别的意思。

jinmiaobis

谁他妈写程序写成这样我直接第一个开了他.

simeiren

谁他妈写程序写成这样我直接第一个开了他.

不要这样气愤啊！
总是要给人改过自新的机会的

xiaoyu9805119

原帖由 jinmiaobis 于 2009-1-21 13:18 发表
谁他妈写程序写成这样我直接第一个开了他.

哈哈，不是我想写这么长，是设计的太过强悍。

这么搓的语句mysql竟然也能跑出来，很佩服。

ruying

原帖由 jinmiaobis 于 2009-1-21 13:18 发表
谁他妈写程序写成这样我直接第一个开了他.

谁写这么长我发配他到清朝给老太太缠裹脚布去。

rover12421

ms sql 的最多好像可以包含 255 个select子查询，MYSQL当然也不能输啦

zflying2000

:wink: :wink: 4和5执行这类还是有区别的哟。需要特别注意一下

sunnyfun

如果效率真的不低倒也无所谓，不过我看楼主的那句肯定有隐患。
且不说里面有若干无意义的select。
先对messageid排序再union，最后得到的结果并不一定是真正排好序的结果，完全可以把排序放在union后。
另外，sql字符串是有长度限制的，当sql字符串长度超过你系统的设定值，执行就会出错。
另外现在的mysql有内置表分区功能，不必再去费神自己再搞一套了吧。