• 关于

    mysql row_number()

    的搜索结果

问题

MySQL大圆距离(Haversine公式)

保持可爱mmm 2020-05-08 11:09:04 0 浏览量 回答数 1

回答

对我来说,决定性的因素是我是否使用调用查询列*。 bind_result()为此,使用会更好: // Use bind_result() with fetch() $query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?'; get_result()为此,使用会更好: // Use get_result() with fetch_assoc() $query2 = 'SELECT * FROM table WHERE id = ?'; 示例1 $query1使用bind_result() $query1 = 'SELECT id, first_name, last_name, username FROM table WHERE id = ?'; $id = 5; if($stmt = $mysqli->prepare($query)){ /* Binds variables to prepared statement i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets */ $stmt->bind_param('i',$id); /* execute query */ $stmt->execute(); /* Store the result (to get properties) */ $stmt->store_result(); /* Get the number of rows */ $num_of_rows = $stmt->num_rows; /* Bind the result to variables */ $stmt->bind_result($id, $first_name, $last_name, $username); while ($stmt->fetch()) { echo 'ID: '.$id.''; echo 'First Name: '.$first_name.''; echo 'Last Name: '.$last_name.''; echo 'Username: '.$username.''; } /* free results */ $stmt->free_result(); /* close statement */ $stmt->close(); } /* close connection */ $mysqli->close(); 实施例2对于$query2使用get_result() $query2 = 'SELECT * FROM table WHERE id = ?'; $id = 5; if($stmt = $mysqli->prepare($query)){ /* Binds variables to prepared statement i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets */ $stmt->bind_param('i',$id); /* execute query */ $stmt->execute(); /* Get the result */ $result = $stmt->get_result(); /* Get the number of rows */ $num_of_rows = $result->num_rows; while ($row = $result->fetch_assoc()) { echo 'ID: '.$row['id'].''; echo 'First Name: '.$row['first_name'].''; echo 'Last Name: '.$row['last_name'].''; echo 'Username: '.$row['username'].''; } /* free results */ $stmt->free_result(); /* close statement */ $stmt->close(); } /* close connection / $mysqli->close(); 正如你所看到的,你不能使用bind_result带。但是,get_result两者均可使用,但bind_result更简单,并且消除了一些麻烦$row['name']。 bind_result() 优点: 更简单 不用惹 $row['name'] 用途 fetch() 缺点: 不适用于使用 * get_result() 优点: 适用于所有SQL语句 用途 fetch_assoc() 缺点: 必须搞乱数组变量 $row[] 不那么整齐 需要MySQL本机驱动程序(mysqlnd)来源:stack overflow

保持可爱mmm 2020-05-10 21:37:23 0 浏览量 回答数 0

问题

MySQL中的ROW_NUMBER()

保持可爱mmm 2020-05-08 10:20:58 0 浏览量 回答数 1

阿里云试用中心,为您提供0门槛上云实践机会!

0元试用32+款产品,最高免费12个月!拨打95187-1,咨询专业上云建议!

问题

如何将MS-SQL Server SELECT查询转换/迁移到Oracle和MySQL?

心有灵_夕 2019-12-22 14:34:02 0 浏览量 回答数 0

回答

如上面的注释中所述,您应该分而治之,以使您的生活更轻松(尤其是当您在该大型函数中使用代码时编写代码)。确实很容易做到: function file_put($number, $data) { $path = sprintf("C:/temp/wamp/www/file%d.txt", $number); file_put_contents($path, $data); } 例如,这只是替换许多重复的行,您只需要在其中放入一些(编号)文件即可。 但是您也可以使用更复杂的内容(例如数据库操作)来执行此操作。您可能希望将错误处理移到您的视线之外,并在需要时小心地连接到数据库,并以更灵活的方式来获取数据。可以通过将(已弃用的)mysql_*函数移至其自己的一两个类中来完成,以使它不被看到。这将使它的使用更加容易(我首先显示): // Create your database object to use it later on: $config = array( 'server' => 'localhost', 'name' => 'root', 'password' => '', 'db' => 'test', ); $db = new MySql($config); 我称它为数据库类,MySql因为它代表了mysql连接,并且可以与旧的mysql扩展一起使用。您只需要将该数据库对象传递给问题中的函数即可。结合file_put功能,它看起来像这样: function checkin(MySql $DB, $TechID, $ClientID, $SiteID) { $query = sprintf("SELECT Type FROM Log WHERE TechID = '%d' ORDER BY LogTime DESC LIMIT 1", $TechID); file_put(5, $query); $result1 = $DB->query("SELECT COUNT(*) FROM Log"); $result2 = $DB->query($query); foreach ($result1 as $row1) { list($count) = $row1; $data = "ClientID:$ClientID TechID:$TechID SiteID:$SiteID Count:$count" file_put(3, $data); foreach ($result2 as $row2) { file_put(4, $data); } } } 该checkin函数仍然接近大型(已经有12行代码),但是比您的第一个版本短得多,因为它委派了编写文件和访问数据库的工作。我希望这个示范有用。以下是完整的代码示例: /** * MySql Exception */ class MySqlException extends RuntimeException { } /** * MySql Database Class */ class MySql { private $server; private $name; private $password; private $db; private $connection; public function __construct(array $config) { $this->server = $config['server']; $this->name = $config['name']; $this->password = $config['password']; $this->db = $config['db']; } private function connect($server, $name, $password) { $this->connection = mysql_connect($server, $name, $password); if (!$this->connection) { $this->error("Unable to connect to '%s' as user '%s'", $server, $name); } } private function select($db) { if (!mysql_select_db($db, $this->connection)) { $this->error("Unable to select database '%s'", $db); } } private function close() { $this->connection && mysql_close($this->connection); } private function connectSelect() { $this->connect($this->server, $this->name, $this->password); $this->select($this->db); } /** * @param $query * @return MySqlResult */ public function query($query) { $this->connection || $this->connectSelect(); $result = mysql_query($query, $this->connection); if (!$result) { $this->error("Unable to execute query '%s'", $query); } return new MySqlResult($result); } /** * @param string $format * @param ... * @throws MySqlException */ private function error($format) { $args = func_get_args(); array_shift($args); $format .= ': %s'; $args[] = $this->connection ? mysql_error($this->connection) : mysql_error(); throw new MySqlException(vsprintf($format, $args)); } public function __destruct() { $this->close(); } } /** * MySql Result Set - Array Based */ class MySqlResult implements Iterator, Countable { private $result; private $index = 0; private $current; public function __construct($result) { $this->result = $result; } public function fetch($result_type = MYSQL_BOTH) { $this->current = mysql_fetch_array($this->result, $result_type); return $this->current; } /** * Return the current element * @link http://php.net/manual/en/iterator.current.php * @return array */ public function current() { return $this->current; } public function next() { $this->current && $this->fetch(); } /** * Return the key of the current element * @link http://php.net/manual/en/iterator.key.php * @return mixed scalar on success, or null on failure. */ public function key() { return $this->current ? $this->index : null; } /** * Checks if current position is valid * @link http://php.net/manual/en/iterator.valid.php * @return boolean The return value will be casted to boolean and then evaluated. * Returns true on success or false on failure. */ public function valid() { return (bool)$this->current; }来源:stack overflow

保持可爱mmm 2020-05-11 17:28:36 0 浏览量 回答数 0

回答

ROW_NUMBER(MySQL 8.0+)可以使用: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY col_time DESC) AS rn -- taking the newest posts FROM posts WHERE user_id IN (1,2,3,4 .... 98,99,100) ) sub WHERE rn <= 6;

几许相思几点泪 2019-12-29 19:32:37 0 浏览量 回答数 0

回答

才开始学SQL不久,只不过前几天恰好遇到另外的问题,可能相比题主的方案那边的解决方案能用,也更好一些。 在我看来,题主的方法即使能行,但我感觉属于效率不高的,举个例子:100本书,每本书10个标签,你的WHERE下面的子查询都得查个1000次吧,接下来再说我之前遇到的问题。 我之前遇到的是合并行的问题,如果在你的例子中,我们就需要根据BookID把labels合并,就是需要下面的效果 book_id|label_id 001|001,002 002|001 然后在对这个结果集进行查询的时候就可以根据“001,002”这样的字符串匹配进行查询了。 我用的是oracle,贴上oracle可用的解决方案的SQL: SELECT bookID,LTRIM(MAX(SYS_CONNECT_BY_PATH(labelID,','))KEEP(DENSE_RANK LAST ORDER BY curr),',') AS labels FROM ( SELECT labelID ,bookID ROW_NUMBER() OVER (ORDER BY bookID) curr , ROW_NUMBER() OVER (ORDER BY bookID) -1 prev FROM book_label_maps) GROUP BY bookID CONNECT BY prev = PRIOR curr AND bookID=bookID START WITH curr=1以上SQL语句可以通过你的MAPS表生成根据bookID聚合的表(我没有实际测试)。之前我搜索的时候也看到过MYSQL也有它的关于合并行的函数,你可以去看看。如果这种“包含几个标记的书”这样的需求很多的话,可以搞个视图,就生成合并行的结果集,然后之后这样的需求就都到视图里面查,省事。

a123456678 2019-12-02 03:02:18 0 浏览量 回答数 0

问题

升级PHP7后,PHP脚本无法正常工作

云产品优惠 2019-12-01 22:00:29 26 浏览量 回答数 0

回答

AliSQL提供Inventory Hint,帮助您快速提交/回滚事务,配合Returning和Statement Queue,能有效提高业务吞吐能力。 背景信息 在秒杀等业务场景中,减少库存是一个常见的需要高并发,同时也需要串行化的任务模型,AliSQL使用排队和事务性hint来控制并发和快速提交/回滚事务,提高业务吞吐能力。 前提条件 实例版本为RDS MySQL 8.0。 语法 新增了三个hint, 支持SELECT、UPDATE、INSERT、DELETE 语句。 COMMIT_ON_SUCCESS/ROLLBACK_ON_FAIL 两个事务hint为COMMIT_ON_SUCCESS和ROLLBACK_ON_FAIL: COMMIT_ON_SUCCESS:当前语句执行成功就提交事务上下文。 ROLLBACK_ON_FAIL:当前语句执行失败就回滚事务上下文。 语法: /*+ COMMIT_ON_SUCCESS / /+ ROLLBACK_ON_FAIL / 示例: UPDATE /+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL / T SET c = c - 1 WHERE id = 1; TARGET_AFFECT_ROW(NUMBER) 条件hint为TARGET_AFFECT_ROW(NUMBER) :如果当前语句影响行数是指定的就成功,否则语句失败。 语法: /+ TARGET_AFFECT_ROW(NUMBER) / 示例: UPDATE /+ TARGET_AFFECT_ROW(1) */ T SET c = c - 1 WHERE id = 1; 注意事项 事务hint不能运行在autocommit模式下, 例如: mysql> UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) / t -> SET col1 = col1 + 1 -> WHERE id = 1; ERROR 7531 (HY000): Inventory transactinal hints didn't allowed in autocommit mode 事务hint不能运行在sub statement下,例如: mysql> CREATE TRIGGER tri_1 -> BEFORE INSERT ON t -> FOR EACH ROW -> BEGIN -> INSERT /+ commit_on_success */ INTO t1 VALUES (1); -> end// mysql> INSERT INTO t VALUES (2, 1); ERROR HY000: Inventory transactional hints didn't alllowed in stored procedure 条件hint不能运行在SELECT/EXPLAIN statement下, 例如: mysql> EXPLAIN UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) / t -> SET col1 = col1 + 1 -> WHERE id = 1; ERROR 7532 (HY000): Inventory conditional hints didn't match with result 说明 您可以指定target_affect_row为一个无效的number进行测试,系统会有告警。 mysql> EXPLAIN UPDATE /+ commit_on_success rollback_on_fail target_affect_row(-1) */ t -> SET col1 = col1 + 1 -> WHERE id = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | UPDATE | t | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1064 | Optimizer hint syntax error near '-1) / t set col1=col1+1 where id =1' at line 1 | | Note | 1003 | update /+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ test.t set test.t.col1 = (test.t.col1 + 1) where (test.t.id = 1) | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 配合Returning使用 Inventory Hint可以配合Returning使用,实时返回结果集, 例如: mysql> CALL dbms_trans.returning("", "update /+ commit_on_success rollback_on_fail target_affect_row(1) */ t set col1=col1+1 where id=1"); +----+------+ | id | col1 | +----+------+ | 1 | 13 | +----+------+ 1 row in set (0.00 sec) mysql> CALL dbms_trans.returning("", "insert /+ commit_on_success rollback_on_fail target_affect_row(1) */ into t values(10,10)"); +----+------+ | id | col1 | +----+------+ | 10 | 10 | +----+------+ 1 row in set (0.01 sec) 配合Statement queue使用 Inventory Hint可以配合Statement Queue进行排队,例如: mysql> UPDATE /*+ ccl_queue_field(id) commit_on_success rollback_on_fail target_affect_row(1) */ t -> SET col1 = col1 + 1 -> WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE /*+ ccl_queue_value(1) commit_on_success rollback_on_fail target_affect_row(1) */ t -> SET col1 = col1 + 1 -> WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

游客yl2rjx5yxwcam 2020-03-08 13:38:39 0 浏览量 回答数 0

回答

您缺少分号,逗号,即使在更正语法之后,每次仅在循环中再插入一行也不是一个好主意。 从http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator删除并使用生成器: CREATE OR REPLACE VIEW generator_16 AS SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15; CREATE OR REPLACE VIEW generator_256 AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n FROM generator_16 lo, generator_16 hi; CREATE OR REPLACE VIEW generator_4k AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n FROM generator_256 lo, generator_16 hi; CREATE OR REPLACE VIEW generator_64k AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n FROM generator_256 lo, generator_256 hi; CREATE OR REPLACE VIEW generator_1m AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n FROM generator_64k lo, generator_16 hi; 如果出于任何原因您确实需要数字表,请执行以下操作: INSERT INTO numbers(number) SELECT n FROM generator_64k WHERE n < 64000来源:stack overflo

保持可爱mmm 2020-05-11 16:04:09 0 浏览量 回答数 0

回答

SELECT sensor1, sensor2, sensor3, dias FROM ( SELECT sensor1, sensor2, sensor3, dias, id FROM $id ORDER BY id DESC LIMIT 10 ) t ORDER BY id 或者,如果您正在运行MySQL 8.0,则可以使用ROW_NUMBER(): SELECT sensor1, sensor2, sensor3, dias FROM ( SELECT sensor1, sensor2, sensor3, dias, id, ROW_NUMBER() OVER(ORDER BY id DESC) rn FROM $id ) t WHERE rn <= 10 ORDER BY id

保持可爱mmm 2019-12-02 03:15:12 0 浏览量 回答数 0

回答

在SQL Server 2012中,支持ANSI标准OFFSET/ FETCH语法。我在博客上发表了这篇文章,这是官方文档(这是的扩展ORDER BY)。为SQL Server 2012转换的语法为: SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC OFFSET (@start_from - 1) ROWS -- not sure if you need -1 -- because I don't know how you calculated @start_from FETCH NEXT @items_on_page ROWS ONLY; 在此之前,您需要使用各种解决方法,包括ROW_NUMBER()方法。请参阅本文和后续讨论。如果您不在SQL Server 2012上,则不能使用标准语法或MySQL的非标准语法,LIMIT但可以使用更详细的解决方案,例如: ;WITH o AS ( SELECT TOP ((@start_from - 1) + @items_on_page) -- again, not sure if you need -1 because I -- don't know how you calculated @start_from RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC) /* , other columns / FROM Products ) SELECT RowNum / , other columns */ FROM o WHERE RowNum >= @start_from ORDER BY RowNum; 还有很多其他方法可以为这只猫蒙皮,这可能不是最有效的方法,但是从语法角度来说可能是最简单的方法。我建议查看我发布的链接以及对该问题的评论中指出的重复建议。 来源:stack overflow

保持可爱mmm 2020-05-13 14:08:45 0 浏览量 回答数 0

问题

rds-mysql 分组排序怎么实现

扶摇随心 2020-05-09 09:18:59 0 浏览量 回答数 0

回答

这个需要用到窗口查询,PostgreSQL中可以这样来使用,MySQL不支持:select * from (select *,row_number() over (partition by user_id) rn from tbl) t where t.rn<=10;建议你更换数据库。

德哥 2019-12-02 01:46:15 0 浏览量 回答数 0

问题

数据库不会使用MySQL和Python自动更新

保持可爱mmm 2020-05-11 11:14:25 0 浏览量 回答数 1

问题

在Microsoft SQL Server 2000中模拟MySQL LIMIT子句

保持可爱mmm 2020-05-11 17:20:49 0 浏览量 回答数 1

问题

如何有效地找到给定位置附近的最近位置?mysql

保持可爱mmm 2020-05-17 18:50:45 1 浏览量 回答数 1

回答

以下是带有FOR UPDATE 意向锁的简单示例。使用INNODB引擎的行级锁。该示例显示了四行用于下一个可用序列,这些序列不会受到众所周知的INNODB间隙异常(在AUTO_INCREMENT使用失败后出现间隙的情况)的影响。 架构: -- drop table if exists sequences; create table sequences ( id int auto_increment primary key, sectionType varchar(200) not null, nextSequence int not null, unique key(sectionType) ) ENGINE=InnoDB; -- truncate table sequences; insert sequences (sectionType,nextSequence) values ('Chassis',1),('Engine Block',1),('Brakes',1),('Carburetor',1); 样例代码: START TRANSACTION; -- Line1 SELECT nextSequence into @mine_to_use from sequences where sectionType='Carburetor' FOR UPDATE; -- Line2 select @mine_to_use; -- Line3 UPDATE sequences set nextSequence=nextSequence+1 where sectionType='Carburetor'; -- Line4 COMMIT; -- Line5 理想情况下,您根本没有Line3或繁琐的代码,这会使其他客户端延迟等待锁定。意思是,让你的下一个序列来使用,执行更新(递增部分),并且COMMIT,尽快。 上面的一个存储过程: DROP PROCEDURE if exists getNextSequence; DELIMITER $$ CREATE PROCEDURE getNextSequence(p_sectionType varchar(200),OUT p_YoursToUse int) BEGIN -- for flexibility, return the sequence number as both an OUT parameter and a single row resultset START TRANSACTION; SELECT nextSequence into @mine_to_use from sequences where sectionType=p_sectionType FOR UPDATE; UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType; COMMIT; -- get it and release INTENTION LOCK ASAP set p_YoursToUse=@mine_to_use; -- set the OUT parameter select @mine_to_use as yourSeqNum; -- also return as a 1 column, 1 row resultset END$$ DELIMITER ; 测试: set @myNum:= -1; call getNextSequence('Carburetor',@myNum); +------------+ | yourSeqNum | +------------+ | 4 | +------------+ select @myNum; -- 4 根据您的需要相应地修改存储过程,例如只有两种检索序号的机制中的一种(OUT参数或结果集)。换句话说,很容易放弃OUT参数概念。 如果您不遵循LOCK的ASAP发行(显然在更新后不需要),并继续执行耗时的代码,则在发行之前,对于等待序列的其他客户端,可能会在超时后发生以下情况数: 错误1205(HY000):超出了锁定等待超时;尝试重新启动事务 希望这绝不是问题。 show variables where variable_name='innodb_lock_wait_timeout'; MySQL手册页innodb_lock_wait_timeout。 目前在我的系统上,它的值为50(秒)。在大多数情况下,超过一两秒钟的等待可能无法忍受。 在TRANSACTIONS期间,另一个有趣的地方是以下命令的输出部分: SHOW ENGINE INNODB STATUS;来源:stack overflow

保持可爱mmm 2020-05-16 22:22:48 0 浏览量 回答数 0

回答

在派生表(子句内的子查询FROM)中,我们对数据user_id进行排序,以使所有具有相同值的行放在一起,并根据game_detail降序对它们进行进一步排序。 现在,我们使用此结果集并使用条件CASE..WHEN表达式来评估行编号。就像循环技术(我们在应用程序代码中使用的,例如:PHP)一样。我们将前一行的值存储在用户定义的变量中,然后对照前一行检查当前行的值。最终,我们将相应地分配行号。 编辑:基于MySQL 文档和@Gordon Linoff的观察: 涉及用户变量的表达式的求值顺序不确定。例如,不能保证SELECT @a,@a:= @ a + 1首先评估@a,然后执行分配。 我们将需要评估行号,并将user_id值分配给@u同一表达式中的变量。 SET @r := 0, @u := 0; SELECT @r := CASE WHEN @u = dt.user_id THEN @r + 1 WHEN @u := dt.user_id /* Notice := instead of = */ THEN 1 END AS user_game_rank, dt.user_id, dt.game_detail, dt.game_id FROM ( SELECT user_id, game_id, game_detail FROM game_logs ORDER BY user_id, game_detail DESC ) AS dt 结果 user_game_rankuser_idgame_detailgame_id162601126100101712001027500113726012475013 在数据库小提琴上查看 我最近发现了MySQL Docs的一个有趣的注释: MySQL的早期版本使得可以在SET以外的语句中为用户变量分配值。MySQL 8.0支持此功能以实现向后兼容,但是在将来的MySQL版本中可能会删除该功能。 另外,由于有一个SO成员,MySQL团队访问了此博客:https : //mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/ 通常的观察是,ORDER BY在同一查询块中与用户变量的评估一起使用时,并不能确保值始终正确。至于,MySQL优化可以接触到的地方,并改变我们的假设计算顺序。 解决此问题的最佳方法是升级到MySQL 8+并使用以下Row_Number()功能: 模式(MySQL v8.0) SELECT user_id, game_id, game_detail, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY game_detail DESC) AS user_game_rank FROM game_logs ORDER BY user_id, user_game_rank; 结果 user_idgame_idgame_detailuser_game_rank611260161010027101200171150027122603713504来源:stack overflow

保持可爱mmm 2020-05-17 12:44:42 0 浏览量 回答数 0

回答

在派生表(子句内的子查询FROM)中,我们对数据user_id进行排序,以使所有具有相同值的行放在一起,并根据game_detail降序对它们进行进一步排序。 现在,我们使用此结果集并使用条件CASE..WHEN表达式来评估行编号。就像循环技术(我们在应用程序代码中使用的,例如:PHP)一样。我们将前一行的值存储在用户定义的变量中,然后对照前一行检查当前行的值。最终,我们将相应地分配行号。 编辑:基于MySQL 文档和@Gordon Linoff的观察: 涉及用户变量的表达式的求值顺序不确定。例如,不能保证SELECT @a,@a:= @ a + 1首先评估@a,然后执行分配。 我们将需要评估行号,并将user_id值分配给@u同一表达式中的变量。 SET @r := 0, @u := 0; SELECT @r := CASE WHEN @u = dt.user_id THEN @r + 1 WHEN @u := dt.user_id /* Notice := instead of = */ THEN 1 END AS user_game_rank, dt.user_id, dt.game_detail, dt.game_id FROM ( SELECT user_id, game_id, game_detail FROM game_logs ORDER BY user_id, game_detail DESC ) AS dt 结果 user_game_rankuser_idgame_detailgame_id162601126100101712001027500113726012475013 在数据库小提琴上查看 我最近发现了MySQL Docs的一个有趣的注释: MySQL的早期版本使得可以在SET以外的语句中为用户变量分配值。MySQL 8.0支持此功能以实现向后兼容,但是在将来的MySQL版本中可能会删除该功能。 另外,由于有一个SO成员,MySQL团队访问了此博客:https : //mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/ 通常的观察是,ORDER BY在同一查询块中与用户变量的评估一起使用时,并不能确保值始终正确。至于,MySQL优化可以接触到的地方,并改变我们的假设计算顺序。 解决此问题的最佳方法是升级到MySQL 8+并使用以下Row_Number()功能: 模式(MySQL v8.0) SELECT user_id, game_id, game_detail, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY game_detail DESC) AS user_game_rank FROM game_logs ORDER BY user_id, user_game_rank; 结果 user_idgame_idgame_detailuser_game_rank611260161010027101200171150027122603713504来源:stack overflow

保持可爱mmm 2020-05-17 20:18:32 0 浏览量 回答数 0

回答

这是一个常见的误解:sql表表示无序的行集。数据中没有固有的顺序。如果不使用order by子句,则返回行的顺序不确定。 因此,首先,您需要订购记录。假设您要按它们排序id(这似乎是唯一的列,由于避免了联系,因此对排序很有用)。如果您正在运行MySQL 8.0,则可以用于row_number()对记录进行排名: select * from (select t.*, row_number() over(order by id) rn from mytable t) t where age = 67 该年龄为67的记录将被分配第2行,因为这是数据按排序时的位置id。如果您有多条年龄为67岁的记录,它们都会连同相关的行号一起显示在结果集中。

保持可爱mmm 2019-12-02 03:15:18 0 浏览量 回答数 0

问题

MSSQL-Declutter报告,替换列中的重复值-> SPACE或QOUTATIONMARK

祖安文状元 2020-01-05 14:10:04 0 浏览量 回答数 1

回答

MySQL 8.0现在支持窗口功能,就像几乎所有流行的SQL实现一样。使用这种标准语法,我们可以编写每组最多n个查询: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; 以下是我在2009年为此问题写的原始答案: 我这样写解决方案: SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id) WHERE m2.id IS NULL; 关于性能,一种解决方案可能会更好,这取决于数据的性质。因此,您应该测试两个查询,并使用给定数据库性能最好的查询。 例如,我有一个StackOverflow August数据转储的副本。我将其用于基准测试。该Posts表中有1,114,357行。它在Macbook Pro 2.40GHz的MySQL 5.0.75上运行。 我将编写查询以查找给定用户ID(我的用户)的最新帖子。 首先在子查询中使用@Eric 所示的技术GROUP BY: SELECT p1.postid FROM Posts p1 INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid FROM Posts pi GROUP BY pi.owneruserid) p2 ON (p1.postid = p2.maxpostid) WHERE p1.owneruserid = 20860; 1 row in set (1 min 17.89 sec) 甚至EXPLAIN分析也要花费超过16秒的时间: +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 76756 | | | 1 | PRIMARY | p1 | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY | 8 | p2.maxpostid | 1 | Using where | | 2 | DERIVED | pi | index | NULL | OwnerUserId | 8 | NULL | 1151268 | Using index | +----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+ 3 rows in set (16.09 sec) 现在用产生同样的查询结果我的技术有LEFT JOIN: SELECT p1.postid FROM Posts p1 LEFT JOIN posts p2 ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid) WHERE p2.postid IS NULL AND p1.owneruserid = 20860; 1 row in set (0.28 sec) 该EXPLAIN分析表明,这两个表都能够使用他们的指标: +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ | 1 | SIMPLE | p1 | ref | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index | | 1 | SIMPLE | p2 | ref | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists | +----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+ 2 rows in set (0.00 sec) 这是我的Posts桌子的DDL : CREATE TABLE posts ( PostId bigint(20) unsigned NOT NULL auto_increment, PostTypeId bigint(20) unsigned NOT NULL, AcceptedAnswerId bigint(20) unsigned default NULL, ParentId bigint(20) unsigned default NULL, CreationDate datetime NOT NULL, Score int(11) NOT NULL default '0', ViewCount int(11) NOT NULL default '0', Body text NOT NULL, OwnerUserId bigint(20) unsigned NOT NULL, OwnerDisplayName varchar(40) default NULL, LastEditorUserId bigint(20) unsigned default NULL, LastEditDate datetime default NULL, LastActivityDate datetime default NULL, Title varchar(250) NOT NULL default '', Tags varchar(150) NOT NULL default '', AnswerCount int(11) NOT NULL default '0', CommentCount int(11) NOT NULL default '0', FavoriteCount int(11) NOT NULL default '0', ClosedDate datetime default NULL, PRIMARY KEY (PostId), UNIQUE KEY PostId (PostId), KEY PostTypeId (PostTypeId), KEY AcceptedAnswerId (AcceptedAnswerId), KEY OwnerUserId (OwnerUserId), KEY LastEditorUserId (LastEditorUserId), KEY ParentId (ParentId), CONSTRAINT posts_ibfk_1 FOREIGN KEY (PostTypeId) REFERENCES posttypes (PostTypeId) ) ENGINE=InnoDB;

保持可爱mmm 2020-05-08 10:11:26 0 浏览量 回答数 0

回答

最后报错貌似看不到了 FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (read thread)I/O thread 7 state: waiting for completed aio requests (read thread)I/O thread 8 state: waiting for completed aio requests (read thread)I/O thread 9 state: waiting for completed aio requests (read thread)I/O thread 10 state: waiting for completed aio requests (write thread)I/O thread 11 state: waiting for completed aio requests (write thread)I/O thread 12 state: waiting for completed aio requests (write thread)I/O thread 13 state: waiting for completed aio requests (write thread)I/O thread 14 state: waiting for completed aio requests (write thread)I/O thread 15 state: waiting for completed aio requests (write thread)I/O thread 16 state: waiting for completed aio requests (write thread)I/O thread 17 state: waiting for completed aio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 03228795 OS file reads, 79307426 OS file writes, 53415531 OS fsyncs 0.12 reads/s, 16384 avg bytes/read, 18.57 writes/s, 18.57 fsyncs/s INSERT BUFFER AND ADAPTIVE HASH INDEX Ibuf: size 1, free list len 7343, seg size 7345, 221744 mergesmerged operations: insert 760128, delete mark 30713, delete 10041discarded operations: insert 0, delete mark 0, delete 0Hash table size 151384511, node heap has 0 buffer(s) 0.00 hash searches/s, 125235.99 non-hash searches/s LOG Log sequence number 1211209887140Log flushed up to 1211209886822Pages flushed up to 1211199528202Last checkpoint at 12111995282020 pending log writes, 0 pending chkp writes 21554306 log i/o's done, 18.57 log i/o's/second BUFFER POOL AND MEMORY Total memory allocated 70330089472; in additional pool allocated 0Dictionary memory allocated 3050346Buffer pool size 4194296Free buffers 612428Database pages 3581868Old database pages 1322105Modified db pages 6988Pending reads 0Pending writes: LRU 0, flush list 2, single page 0Pages made young 21711, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 3221371, created 360497, written 480254980.12 reads/s, 0.17 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 3581868, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] INDIVIDUAL BUFFER POOL INFO ---BUFFER POOL 0Buffer pool size 524287Free buffers 74538Database pages 449749Old database pages 166003Modified db pages 1559Pending reads 0Pending writes: LRU 0, flush list 2, single page 0Pages made young 2734, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 404393, created 45356, written 108182270.00 reads/s, 0.02 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 449749, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 524287Free buffers 75464Database pages 448823Old database pages 165664Modified db pages 675Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2696, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 403809, created 45014, written 40149190.02 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 448823, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 524287Free buffers 73881Database pages 450406Old database pages 166244Modified db pages 636Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2662, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 403732, created 46674, written 39969520.02 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 450406, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size 524287Free buffers 76810Database pages 447477Old database pages 165181Modified db pages 947Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2910, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 402913, created 44564, written 85749130.02 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 447477, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size 524287Free buffers 77520Database pages 446767Old database pages 164920Modified db pages 770Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2546, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 402432, created 44335, written 68310570.02 reads/s, 0.05 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 446767, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size 524287Free buffers 81699Database pages 442588Old database pages 163359Modified db pages 677Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2803, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 398192, created 44396, written 41508310.00 reads/s, 0.02 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 442588, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size 524287Free buffers 75357Database pages 448930Old database pages 165700Modified db pages 907Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2625, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 404076, created 44854, written 45544630.02 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 448930, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool size 524287Free buffers 77159Database pages 447128Old database pages 165034Modified db pages 817Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2735, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 401824, created 45304, written 50841360.00 reads/s, 0.07 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 447128, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ROW OPERATIONS 0 queries inside InnoDB, 0 queries in queue2 read views open inside InnoDBMain thread process no. 7890, id 140438058379008, state: enforcing dict cache limitNumber of rows inserted 25334846, updated 19974009, deleted 3278941, read 1085167903960 14.57 inserts/s, 9.70 updates/s, 0.15 deletes/s, 630031.82 reads/s END OF INNODB MONITOR OUTPUT InnoDB: ###### Diagnostic info printed to the standard error streamInnoDB: Error: semaphore wait has lasted > 600 secondsInnoDB: We intentionally crash the server, because it appears to be hung.2017-06-01 13:35:18 7fba49d54700 InnoDB: Assertion failure in thread 140438079358720 in file srv0srv.cc line 1756InnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to http://bugs.mysql.com.InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlInnoDB: about forcing recovery.05:35:18 UTC - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully helpdiagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608read_buffer_size=2097152max_used_connections=1221max_threads=2000thread_count=1221connection_count=1221It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8225785 K bytes of memoryHope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0 thread_stack 0x40000/app/mysql/bin/mysqld(my_print_stacktrace+0x35)[0x910535]/app/mysql/bin/mysqld(handle_fatal_signal+0x3d8)[0x6755e8]/lib64/libpthread.so.0[0x31b3e0f710]/lib64/libc.so.6(gsignal+0x35)[0x31b3a32625]/lib64/libc.so.6(abort+0x175)[0x31b3a33e05]/app/mysql/bin/mysqld[0x9e53bd]/lib64/libpthread.so.0[0x31b3e079d1]/lib64/libc.so.6(clone+0x6d)[0x31b3ae89dd]The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.170601 13:35:18 mysqld_safe Number of processes running now: 0170601 13:35:18 mysqld_safe mysqld restarted 没人吗?问下怎么解决

丶阿狸不哭 2019-12-02 00:01:19 0 浏览量 回答数 0

回答

row_number是一个返回数据排序编号的排名方法,从1开始。我们经常需要用到row_number去生成某些报表,不幸的是,MySQL并不像MSSQL,Oracle一样支持这个方法。在MySQL中如果想实现这个功能需要临时变量。为了在MySQL中实现row_number,需要在查询中实用临时变量,下图中从employees表中查询出5条数据,并且从1开始,为每一行添加了编号(row number)。在上面的查询中:首先,我们定义了一个变量叫做row_number,初始值为0,row_number是以@为前缀的一个临时变量。然后,在查询中,我们为这个变量每次+1,LIMIT分句是为了限制返回的结果数,此处设为5.另一个方法是使用临时变量作为派生表,联合主表。看下边的查询语句:需要注意,派生表必须使用别名,以使查询语句在语法上没有问题。

蛮大人123 2019-12-02 01:44:18 0 浏览量 回答数 0

问题

mysql启动不了了,求高数指点,日志已贴出

hi34028332 2019-12-01 21:01:31 9098 浏览量 回答数 2

回答

分页显示一般有两种实现方式:业务层分页、数据库层分页(以下会用到两个参数,提前说明下 page:请求第几页,size:每页显示多少条)。 业务层分页:从数据库取出所有数据,然后通过传过来的page和size对所有数据截取,比如一共查了100条数据,保存在list里面,要求查询第2页,每页显示10条,则可以通过list属性,取100条数据 中的第11条到第20条,可通过遍历实现。 数据库层分页:数据库都会有分页函数(mysql 是limit函数,sqlServer是row_number()函数,可自行百度下)该方法是通过传过来的page和size在查询数据库时就开始分页,以mysql为例,查询第2页,每页显示10条,则sql语句是 ”select * from XX limit 10,10“(第一个10表示从下标为10开始查,第二个10是共读取10条)。 性能肯定是第二种分页方式好,只要搞懂分页原理,想实现分页其实很简单,只要搞清楚分页是将多条数据中的某几条挑出来。另外通过JSP分页标签也可以实现:http://edu.51cto.com/course/course_id-4201.html

爵霸 2019-12-02 02:28:04 0 浏览量 回答数 0

问题

如何在SQL数据库表中选择第n行?

保持可爱mmm 2020-05-10 18:53:03 0 浏览量 回答数 1

问题

如何在SQL数据库表中选择第n行?

保持可爱mmm 2019-12-01 21:58:04 5 浏览量 回答数 1

问题

MaxCompute快速入门:运行SQL

行者武松 2019-12-01 22:01:39 1282 浏览量 回答数 0
阿里云大学 云服务器ECS com域名 网站域名whois查询 开发者平台 小程序定制 小程序开发 国内短信套餐包 开发者技术与产品 云数据库 图像识别 开发者问答 阿里云建站 阿里云备案 云市场 万网 阿里云帮助文档 免费套餐 开发者工具 企业信息查询 小程序开发制作 视频内容分析 企业网站制作 视频集锦 代理记账服务 2020阿里巴巴研发效能峰会 企业建站模板 云效成长地图 高端建站