row_number 由几列 [英] row_number by several columns
问题描述
我有以下数据:
type id date1 date2 diff
-----------------------------------
blue 1 x1 xxx 18
blue 1 x2 - -
red 1 x1 - -
blue 2 x1 xx 15
blue 2 x2 xx 18
blue 2 x3 - -
我想添加一个 row_number
来获取这样的数据:
And I want to add a row_number
to get the data like this:
type id date1 date2 diff row_number
---------------------------------------------
blue 1 x1 xxx 18 1
blue 1 x2 - - 2
red 1 x1 - - 1
blue 2 x1 xx 15 1
blue 2 x2 xx 18 2
blue 2 x3 - - 3
即首先按类型排序,然后是 id 和最后日期.
I.e. first sort by type, then id and last date.
我尝试了以下语法:
Create table t(type char(7), id int(13), date1 date, date2 date, diff int, row_number int) ;
Insert into t(type, id, date1, date2, diff, row_number)
(SELECT a.type, a.id, a.date1, a.date2, a.diff
FROM
(Select
type, id, date1, date2, diff, row_number() over (order by type, id, date1) as r
from table) a
order by
a.type, a.id, a.date1;
上面的语法不起作用,我收到错误消息:
The syntax above doesn't work and I get the error message:
您的 SQL 语法有错误;检查与您的 MYSQL 版本相对应的手册....
我尝试了一种更简单的语法,只是为了看看命令是否像这样:
I tried an easier syntax just to see if the command work like:
SELECT
type,
ROW_NUMBER() OVER (PARTITION BY type, id, date1 ORDER By type, lpnr, date1) as t,
id,
date1
FROM table;
或
select
row_number() over(order by id),
id
from table;
并且仍然收到相同的错误消息.
and still get the same error message.
你能告诉我我做错了什么,或者 row_number 在 MYSQL 版本中不起作用吗(我有 heidi 和 workbench)?如果命令不起作用,还有其他方法可以做我想做的事情吗?
Can you please tell me what I am doing wrong or if row_number doesn't work in the MYSQL versions (I have heidi and workbench)? If the command doesn't work is there any other way to do what I want to do?
非常感谢您的帮助!
琳达
推荐答案
不幸的是,我不相信 MySQL 提供您尝试使用的分析函数,即 ROWNUMBER() OVER PARTITION
;
Unfortunately I don't believe that MySQL provides the analytical functions that you are trying to use i.e ROWNUMBER() OVER PARTITION
;
然而,这并不意味着它不能通过其他方式推导出来.试试这个:
However, that does not mean that it can't be derived using others means. Give this a go:
create table myTable (type varchar(50) not null,id int(10) unsigned not null,
date1 varchar(10) default null,date2 varchar(10) default null,diff int unsigned default null
);
insert into myTable (type,id,date1,date2,diff) values ('blue',1,'x1','xxx',18);
insert into myTable (type,id,date1,date2,diff) values ('blue',1,'x2',null,null);
insert into myTable (type,id,date1,date2,diff) values ('red',1,'x1',null,null);
insert into myTable (type,id,date1,date2,diff) values ('blue',2,'x1','xx',15);
insert into myTable (type,id,date1,date2,diff) values ('blue',2,'x2','xx',18);
insert into myTable (type,id,date1,date2,diff) values ('blue',2,'x3',null,null);
select t.type,t.id,t.date1,t.date2,t.rownum
from
(
select mt.type,mt.id,mt.date1,mt.date2,mt.diff,
case
when mt.id = @curId and mt.type = @curType then @curRow := @curRow + 1
else @curRow := 1
END as rownum,
@curId := mt.id,
@curType := mt.type
from myTable mt
join (select @curRow := 0, @curId := -1,@curType="") r
order by mt.id,mt.type
) t;
这篇关于row_number 由几列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!