按语法分区错误-如何获取分区结果集

xcitsw88  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(320)

这个问题在这里已经有答案了

mysql中的行号(24个答案)
两年前关门了。
我的问题:

SELECT education,nickname,height
    ROW_NUMBER() OVER (PARTITION BY education ORDER BY height DESC) INDEX_NUM
FROM user
LIMIT 100;

我的表定义:

CREATE TABLE user (

female BOOLEAN NOT NULL,

nickname VARCHAR(60) NOT NULL,
id INT NOT NULL,
charm INT NOT NULL,
standardmember BOOLEAN NOT NULL,
idcard BOOLEAN NOT NULL,

age INT NOT NULL,
marriage VARCHAR(5) NOT NULL,
region VARCHAR(10) NOT NULL,

education VARCHAR(10) NOT NULL,
height INT NOT NULL,
weight INT NOT NULL,
constellation VARCHAR(5),
race VARCHAR(5),
animalzodiac VARCHAR(2) NOT NULL,

intro TEXT NOT NULL,

rage VARCHAR(10) NOT NULL,
rheight VARCHAR(15) NOT NULL,
rrace VARCHAR(10) NOT NULL,
reducation VARCHAR(10) NOT NULL,
rphoto VARCHAR(5) NOT NULL,
rmarriage VARCHAR(10) NOT NULL,
rregion VARCHAR(100) NOT NULL,
rhonesty VARCHAR(10) NOT NULL,

jobtitle VARCHAR(15),
industry VARCHAR(20),
companynature VARCHAR(10),
employeewelfare VARCHAR(10),
workintensity VARCHAR(10),
mobility VARCHAR(10),
careervsfamily VARCHAR(10),
overseaassignment VARCHAR(10),

college VARCHAR(30),
majortype VARCHAR(5),
languages VARCHAR(50),

birthplace VARCHAR(10),
residence VARCHAR(10),
nationality VARCHAR(15),
personality VARCHAR(5),
humor VARCHAR(6),
temper VARCHAR(6),
seriousness VARCHAR(15),
kidwish VARCHAR(5),
whentomarry VARCHAR(5),
longdistancelove VARCHAR(5),
dreammarriage TEXT,

livewithparents VARCHAR(5),
parents VARCHAR(5),
siblings VARCHAR(25),
financialsituation VARCHAR(10),
parentsinsurance VARCHAR(10),
parentsemployment VARCHAR(35),

sent BOOLEAN NOT NULL DEFAULT 0;

PRIMARY KEY (id)

);
我的目标是划分结果集并将它们按高度升序排序。这样地:

但我错了:

嗯,我不太擅长mysql和查询语法。如果我问了一个愚蠢的问题,请随意教我。谢谢。

tvz2xvvm

tvz2xvvm1#

尝试此查询:

SELECT education,
       nickname,
       height,
       ROW_NUMBER() OVER w AS INDEX_NUM
FROM u
WINDOW w AS (
             PARTITION BY education,
             nickname ORDER BY height DESC 
             );

我用的是 ROW_NUMBER 具有窗口功能。因为mysql支持只作为窗口函数使用的非聚集函数。对于这些,over条款是强制性的,例如:

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

有关更多信息,请访问以下链接:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

相关问题