SQL 错误:ROW_NUMBER() OVER (PARTITION) [英] SQL error: ROW_NUMBER() OVER (PARTITION
问题描述
我的 SQL 代码有什么问题?
What is wrong with my SQL code?
我尝试在此答案
但我不断收到以下错误:
But I keep getting the following error:
("附近:语法错误:SELECT rn = ROW_NUMBER() OVER (
near "(": syntax error: SELECT rn = ROW_NUMBER() OVER (
这里是SQL代码:
SELECT
rn = ROW_NUMBER() OVER (PARTITION BY s.stop_id, s.stop_name
ORDER BY s.stop_id, s.stop_name)
FROM stops s
我在某处读到它与 SQL 版本或 sqlite3 的使用有关 ??
I read somewhere that it has to do with SQL versions or the usage of sqlite3 ??
这里有一些关于这个问题的额外信息:
Here some additional information to the problem:
我有起始表:
table_beginning =
[some_text0 , some_text1 , some_text2 , some_text3 ]
[ bla_1 , monday , red , bla_something ]
[ bla_77 , tuesday , green , bla_other ]
[ bla_99 , monday , blue , bla_anything ]
[ bla_00 , wednesday , red , bla_maybe ]
[ bla_55 , monday , violet , bla_more ]
[ bal_66 , monday , red , bla_kind ]
[ bal_22 , monday , red , bla_stuff ]
我想最终得到一个关于 col_2 和 col_3 没有重复的表(不关心 col_1 和 col_4 是什么!!)
I would like to end up with a table that has no doublicates concerning col_2 and col_3 (not caring whatever col_1 and col_4 are !!)
例如:
table_final1 =
[some_text0 , some_text1 , some_text2 , some_text3 ]
[ bla_1 , monday , red , bla_something ]
[ bla_77 , tuesday , green , bla_other ]
[ bla_99 , monday , blue , bla_anything ]
[ bla_00 , wednesday , red , bla_maybe ]
[ bla_55 , monday , violet , bla_more ]
(--> 哪些行被踢出并不重要.因此,接受的最终表也可能如下所示:
(--> which ones of the rows that are kicked out does not matter. The accepted final table could therefore also look like:
table_final2 =
[some_text0 , some_text1 , some_text2 , some_text3 ]
[ bla_77 , tuesday , green , bla_other ]
[ bla_99 , monday , blue , bla_anything ]
[ bla_00 , wednesday , red , bla_maybe ]
[ bla_55 , monday , violet , bla_more ]
[ bal_66 , monday , red , bla_kind ]
或
table_final3 =
[some_text0 , some_text1 , some_text2 , some_text3 ]
[ bla_77 , tuesday , green , bla_other ]
[ bla_99 , monday , blue , bla_anything ]
[ bla_00 , wednesday , red , bla_maybe ]
[ bla_55 , monday , violet , bla_more ]
[ bal_22 , monday , red , bla_stuff ]
重要的是 col_2 和 col_3 从来没有相同的两个条目!
All that matters is that col_2 and col_3 have never the same two entries !
如你所见 some_text1 = monday AND some_text2 = red
现在只存在一次!!(从col_2和col_3的角度排除重复)
As you can see some_text1 = monday AND some_text2 = red
exists now only once !!
(eliminating doublicates from the point of view only looking at col_2 and col_3)
至于 col_1 和 col_4 中的内容 - 我根本不在乎里面有什么!我只关心 col_2 和 col_3 里面有什么(那里没有任何重复!)
As of content in col_1 and col_4 - I don't care at all what is in there ! I'm only concerned about what is inside col_2 and col_3 (not having any doublicates there !)
一种解决方案:
我想出了一种方法(但也许有更优雅的方法??)
I figured out a way (but maybe there is a more elegant one??)
CREATE TABLE table_intermediate AS
SELECT DISTINCT col_2, col_3
FROM table_beginning;
--> 这将创建一个中间表--> 使用 DISTINCT
关键字这确实消除了重复-->(缺点,我完全丢失了 col_1 和 col_4 信息)
--> This creates an intermediate table
--> with the DISTINCT
keyword this does eliminate doublicates
--> (disadvantage, I loose col_1 and col_4 information completely)
也许有一个解决方案可以让我保留有关 col_1 和 col_4 的信息???(同样,我不在乎 col_1 或 col_4 中的哪一个!)
Maybe there is a solution where I can keep information on col_1 and col_4 ??? (again, which of the col_1 or col_4 I do not care !)
推荐答案
在 SQLite 中,通常可以使用 rowid
:
In SQLite, you can typically use rowid
:
select s.*
from stops s
where s.rowid = (select min(s2.rowid)
from stops s2
where s2.stop_id = s.stop_id and s2.stop_name = s.stop_name
);
我不确定这是否是您真正需要的.但这似乎是您想要用 row_number()
做的事情.如果这没有达到目的,请使用示例数据和所需结果提出另一个问题.
I'm not sure if this is what you really need. But this seems to be what you want to do with row_number()
. If this doesn't hit the spot, then ask another question with sample data and desired results.
这篇关于SQL 错误:ROW_NUMBER() OVER (PARTITION)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!