免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
12下一页
最近访问板块 发新帖
查看: 10772 | 回复: 15

MySQL union order by 超级无敌长查询sql语句 [复制链接]

论坛徽章:
0
发表于 2009-01-21 11:36 |显示全部楼层
原文参见: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;

论坛徽章:
8
综合交流区版块每周发帖之星
日期:2015-12-02 15:03:53数据库技术版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-09-14 06:20:00金牛座
日期:2014-10-10 11:23:34CU十二周年纪念徽章
日期:2013-10-24 15:41:34酉鸡
日期:2013-10-19 10:17:1315-16赛季CBA联赛之北京
日期:2017-03-06 15:12:44
发表于 2009-01-21 11:57 |显示全部楼层
sql语句追求短而不是长

论坛徽章:
0
发表于 2009-01-21 12:20 |显示全部楼层
原帖由 ruochen 于 2009-1-21 11:57 发表
sql语句追求短而不是长

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

论坛徽章:
0
发表于 2009-01-21 13:18 |显示全部楼层
谁他妈写程序写成这样我直接第一个开了他.

论坛徽章:
0
发表于 2009-01-21 13:25 |显示全部楼层
谁他妈写程序写成这样我直接第一个开了他.


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

论坛徽章:
0
发表于 2009-01-21 14:33 |显示全部楼层
原帖由 jinmiaobis 于 2009-1-21 13:18 发表
谁他妈写程序写成这样我直接第一个开了他.


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

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

论坛徽章:
0
发表于 2009-01-21 14:38 |显示全部楼层
原帖由 jinmiaobis 于 2009-1-21 13:18 发表
谁他妈写程序写成这样我直接第一个开了他.

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

论坛徽章:
39
白银圣斗士
日期:2015-11-24 10:40:40酉鸡
日期:2015-03-20 14:15:44寅虎
日期:2015-03-20 14:13:59午马
日期:2015-03-20 14:13:16白羊座
日期:2015-03-20 14:12:54金牛座
日期:2015-03-20 14:12:09双子座
日期:2015-03-20 14:11:57巨蟹座
日期:2015-03-20 14:11:44狮子座
日期:2015-03-20 14:11:29亥猪
日期:2015-03-20 14:16:24戌狗
日期:2015-03-20 14:16:40申猴
日期:2015-03-20 14:17:05
发表于 2009-01-21 14:49 |显示全部楼层
ms sql 的最多好像可以包含 255 个select子查询,MYSQL当然也不能输啦

论坛徽章:
0
发表于 2009-01-21 15:00 |显示全部楼层
:wink: :wink: 4和5执行这类还是有区别的哟。需要特别注意一下

论坛徽章:
0
发表于 2009-01-23 09:33 |显示全部楼层
如果效率真的不低倒也无所谓,不过我看楼主的那句肯定有隐患。
且不说里面有若干无意义的select。
先对messageid排序再union,最后得到的结果并不一定是真正排好序的结果,完全可以把排序放在union后。
另外,sql字符串是有长度限制的,当sql字符串长度超过你系统的设定值,执行就会出错。
另外现在的mysql有内置表分区功能,不必再去费神自己再搞一套了吧。
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP