取1个表 前10% 的数据
这个sql在mysql如俄写?(sqlserver 或者oracle都可以的)
这个sql在mysql如俄写?(sqlserver 或者oracle都可以的)
解决方案 »
- 一个小问题,求教!!
- 如何利用mysql求出本月份的前三个月是哪年哪月
- group by 的输出不理解
- 拼接sql语句的问题,求高手
- 菜鸟问题,无法安装MYSQL,WHY?
- mysql数据库怎么样存储中文?
- 求一位神人帮我改一条Sql语句
- mysql数据库没有备份就进行替换。但替换的人说mysql数据库替换前,已经把它先放到回收站里了,然后才进行替换。如果想还原,可以用回收站里的mysql数据库进
- 求mysql的两张表在用户id等于某个数字,查出两张表的所有信息
- 建表总是提示1064,不知哪里出了问题
- postgresql的linux下的odbc驱动谁有?
- alter table xxx drop xxx,如何判断可以避免在没有字段的情况下不提示
mysql:用动态SQL语句,得到总数,再LIMIT 总数/10
问 mysql 具体这个动态sql该如何写 (最好写成一个sql)
set @sql=concat('select * from tt limit ',((select count(*) from tt)/10));
prepare sql from @sql;
execute sql
mysql> set @sql=concat('select * from t2 limit ',((select count(*) from t2)/10));
Query OK, 0 rows affected (0.12 sec)mysql> prepare sql from @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql from @sql' at line 1
mysql> prepare @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@sql' at line 1
mysql> execute @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@sql' at line 1
mysql> execute sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql' at line 1
mysql> set @sql=concat('select * from tt limit ',((select count(*) from tt)/10));
ERROR 1146 (42S02): Table 'aa.tt' doesn't exist
mysql> prepare sql from @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql from @sql' at line 1
mysql> execute sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql' at line 1
第1此写这个动态sql
是否必须把他写道存储过错
上面错在什么地方??
SET @SQL=CONCAT('select * from a limit ',ROUND(((SELECT COUNT(*) FROM a)/10)));
SELECT @SQL;
PREPARE sql1 FROM @SQL;
EXECUTE sql1;
这个sql如何??
2 楼liuyi8903(不让疑问伴随)回复于 2004-11-13 11:41:02 得分 0select * from table t where to_char(t.col2)='2004-11-11' rownum<=(select count(*)/10 from table a t.col2=a.col2)
union all
select * from table t where to_char(t.col2)='2004-11-10' rownum<=(select count(*)/10 from table a t.col2=a.col2)
Top
3 楼ATGC(花开蝶舞,木秀鸟栖)回复于 2004-11-13 12:19:28 得分 80SELECT * FROM (
SELECT col2,col1,ROW_NUMBER()
OVER (
PARTITION BY to_char(col2,'yyyy-mm-dd') ORDER BY col2
) ratio FROM temp
) a
WHERE a.ratio <= (select round(count(*)*0.5) tatio from temp b
where to_char(a.col2,'yyyy-mm-dd')=to_char(b.col2,'yyyy-mm-dd')
group by to_char(b.col2,'yyyy-mm-dd'))
/Top
4 楼ATGC(花开蝶舞,木秀鸟栖)回复于 2004-11-13 12:20:57 得分 0上面的是取50%的记录,现在改成10%
SELECT * FROM (
SELECT col2,col1,ROW_NUMBER()
OVER (
PARTITION BY to_char(col2,'yyyy-mm-dd') ORDER BY col2
) ratio FROM temp
) a
WHERE a.ratio <= (select round(count(*)*0.1) tatio from temp b
where to_char(a.col2,'yyyy-mm-dd')=to_char(b.col2,'yyyy-mm-dd')
group by to_char(b.col2,'yyyy-mm-dd'))
/Top
5 楼percy_620515(percy)回复于 2004-11-13 12:29:24 得分 0由于表里面的数据非常多,涉及到很多年的数据,用union all效率不是太好!Top
6 楼ATGC(花开蝶舞,木秀鸟栖)回复于 2004-11-13 12:32:15 得分 0刚刚上面是按照一定的顺序,对不同的时期分别取10%的记录
下面的语句可以随机按你设定的比例取记录,每次取的记录不同,但都是10%
比例你可以自己改改,比如要20%,知道在哪里改吧?把round(count(*)*0.1)改成round(count(*)*0.2)
就是分别取20%记录。根据你的假设,会分别取出5条记录和30条记录
因为24的20%是4.8,四舍五入就是5了,你也可以改改语句,取出4条也可以的,不要四舍五入即可
SELECT * FROM (
SELECT col2,col1,ROW_NUMBER()
OVER (
PARTITION BY to_char(col2,'yyyy-mm-dd') order by trunc(dbms_random.value(0,10))
) ratio FROM temp
) a
WHERE a.ratio <= (select round(count(*)*0.1) ratio from temp b
where to_char(a.col2,'yyyy-mm-dd')=to_char(b.col2,'yyyy-mm-dd')
group by to_char(b.col2,'yyyy-mm-dd'))
/Top
7 楼ATGC(花开蝶舞,木秀鸟栖)回复于 2004-11-13 12:33:31 得分 0试试俺的方法嘛。。Top
8 楼percy_620515(percy)回复于 2004-11-13 14:33:37 得分 0感谢 ATGC(这一生受了很多委屈吃了很多苦。。) 和liuyi8903(西西) .
ATGC的方法能实现,只是再实现处理一下四舍五入的问题,因为如果某天的记录非常少的话,就没有记录
http://topic.csdn.net/t/20041113/10/3549408.html