本文介绍了如何限制SQL中每个字段值的行数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
例如,我在Hive中有一个这样的表: 1 1
1 4
1 8
2 1
2 5
3 1
3 2
,我只想返回第一列每个唯一值的前两行。我希望这可以限制从Hive传输到MySQL中用于报告目的的数据量。我想要一个HiveQL查询给我这个:
1 1
1 4
2 1
2 5
3 1
3 2
解决方案
不幸的是,mysql没有解析函数。所以你必须玩变数。
假设您有一个自动增量字段:
mysql> create table mytab(
- > id int not null auto_increment primary key,
- > first_column int,
- > second_column int
- >)engine = myisam;
Query OK,0 rows affected(0.05 sec)
mysql>插入mytab(first_column,second_column)
- >值
- > (1,1),(1,4),(2,10),(3,4),(1,4),(2,5),(1,6);
Query OK,7 rows affected(0.00 sec)
记录:7重复:0警告:0
mysql> select * from mytab order by id;
+ ---- + -------------- + --------------- +
| id | first_column | second_column |
+ ---- + -------------- + --------------- +
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 10 |
| 4 | 3 | 4 |
| 5 | 1 | 4 |
| 6 | 2 | 5 |
| 7 | 1 | 6 |
+ ---- + -------------- + --------------- +
7行集合( 0.00秒)
mysql>选择
- > id,
- > first_column,
- > second_column,
- > row_num
- >从(
- > select *,
- > @num:= if(@first_column = first_column,@num:= @num + 1,1)作为row_num,
- > ; @first_column:= first_column as c
- > from mytab order by first_column,id)as t,(select @first_column:='',@ num:
= 0)as r;
+ ---- + -------------- + --------------- + --------- +
| id | first_column | second_column | row_num |
+ ---- + -------------- + --------------- + --------- +
| 1 | 1 | 1 | 1 |
| 2 | 1 | 4 | 2 |
| 5 | 1 | 4 | 3 |
| 7 | 1 | 6 | 4 |
| 3 | 2 | 10 | 1 |
| 6 | 2 | 5 | 2 |
| 4 | 3 | 4 | 1 |
+ ---- + -------------- + --------------- + --------- +
设置7行(0.00秒)
mysql>选择
- > id,
- > first_column,
- > second_column,
- > row_num
- >从(
- > select *,
- > @num:= if(@first_column = first_column,@num:= @num + 1,1)作为row_num,
- > ; @first_column:= first_column as c
- > from mytab order by first_column,id)as t,(select @first_column:='',@ num:
= 0)as r
- >具有row_num + ---- + -------------- + --------------- + --------- +
| id | first_column | second_column | row_num |
+ ---- + -------------- + --------------- + --------- +
| 1 | 1 | 1 | 1 |
| 2 | 1 | 4 | 2 |
| 3 | 2 | 10 | 1 |
| 6 | 2 | 5 | 2 |
| 4 | 3 | 4 | 1 |
+ ---- + -------------- + --------------- + --------- +
5行(0.02秒)
For example, I have a table like this in Hive:
1 1
1 4
1 8
2 1
2 5
3 1
3 2
and I want to only return the first two rows of each unique value of the first column. I want this to be able to limit the amount of data that I transfer from Hive into MySQL for reporting purposes. I'd like a single HiveQL query that gives me this:
1 1
1 4
2 1
2 5
3 1
3 2
解决方案
Unluckily mysql doesn't have Analytical Functions. So you have to play with variables.Supposing you have an autoincrement field:
mysql> create table mytab (
-> id int not null auto_increment primary key,
-> first_column int,
-> second_column int
-> ) engine = myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into mytab (first_column,second_column)
-> values
-> (1,1),(1,4),(2,10),(3,4),(1,4),(2,5),(1,6);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from mytab order by id;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 10 |
| 4 | 3 | 4 |
| 5 | 1 | 4 |
| 6 | 2 | 5 |
| 7 | 1 | 6 |
+----+--------------+---------------+
7 rows in set (0.00 sec)
mysql> select
-> id,
-> first_column,
-> second_column,
-> row_num
-> from (
-> select *,
-> @num := if(@first_column = first_column, @num:= @num + 1, 1) as row_num,
-> @first_column:=first_column as c
-> from mytab order by first_column,id) as t,(select @first_column:='',@num:
=0) as r;
+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 4 | 2 |
| 5 | 1 | 4 | 3 |
| 7 | 1 | 6 | 4 |
| 3 | 2 | 10 | 1 |
| 6 | 2 | 5 | 2 |
| 4 | 3 | 4 | 1 |
+----+--------------+---------------+---------+
7 rows in set (0.00 sec)
mysql> select
-> id,
-> first_column,
-> second_column,
-> row_num
-> from (
-> select *,
-> @num := if(@first_column = first_column, @num:= @num + 1, 1) as row_num,
-> @first_column:=first_column as c
-> from mytab order by first_column,id) as t,(select @first_column:='',@num:
=0) as r
-> having row_num<=2;
+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 4 | 2 |
| 3 | 2 | 10 | 1 |
| 6 | 2 | 5 | 2 |
| 4 | 3 | 4 | 1 |
+----+--------------+---------------+---------+
5 rows in set (0.02 sec)
这篇关于如何限制SQL中每个字段值的行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!