MySQL选择顺序 [英] Mysql select ordinal
问题描述
具有一个名为books的表,该表具有以下结构:
Having a table named books which has the following structure:
╔════════════╦═══════════╦════════╗
║ LibraryId ║ BookId ║ Price ║
╠════════════╬═══════════╬════════╣
║ 123 ║ 9001 ║ 10.99 ║
║ 123 ║ 9005 ║ 12.99 ║
║ 123 ║ 9006 ║ 7.99 ║
║ 124 ║ 8012 ║ 6.49 ║
║ 124 ║ 9001 ║ 3.19 ║
║ 124 ║ 9076 ║ 7.39 ║
╚════════════╩═══════════╩════════╝
我该如何进行选择以返回完整表,但又添加一个名为Ordinal的字段,该字段计算"每个图书馆的图书数量.结果应类似于:
How could I do a select that would return the full table, but additionally a field named Ordinal, that "counts" the number of books per library. The result should look something like:
╔════════════╦═══════════╦════════╦════════╗
║ LibraryId ║ BookId ║ Price ║Ordinal ║
╠════════════╬═══════════╬════════╬════════╣
║ 123 ║ 9001 ║ 10.99 ║ 1 ║
║ 123 ║ 9005 ║ 12.99 ║ 2 ║
║ 123 ║ 9006 ║ 7.99 ║ 3 ║
║ 124 ║ 8012 ║ 6.49 ║ 1 ║
║ 124 ║ 9001 ║ 3.19 ║ 2 ║
║ 124 ║ 9076 ║ 7.39 ║ 3 ║
╚════════════╩═══════════╩════════╝════════╝
我尝试过类似的事情:
SET @var_record = 1;
SELECT *, (@var_record := @var_record + 1) AS Ordinal
FROM books;
但这将继续计数,而不管libraryId如何.我需要一些可以在每次libraryId更改时重置序数的东西.我希望使用单个查询而不是过程.
But this will continue counting irrespective of the libraryId. I need something that will reset the ordinal every time the libraryId changes. I'd prefer a single query instead of procedures.
测试数据sql脚本:
create temporary table books(libraryId int, bookId int, price double(4,2));
insert into books (libraryId, bookId, price) values (123, 9001, 10.99),(123, 9005, 10.99),(123, 9006, 10.99),(124, 8001, 10.99),(124, 9001, 10.99),(124, 9076, 10.99);
推荐答案
使用变量和条件,您可以根据条件(libraryId已更改)重置计数器.必须按列libraryId进行排序.
Using variables and conditions you can reset the counter based on a condition (libraryId has changed). Mandatory to order by the column libraryId.
SELECT books.*,
if( @libId = libraryId,
@var_record := @var_record + 1,
if(@var_record := 1 and @libId := libraryId, @var_record, @var_record)
) AS Ordinal
FROM books
JOIN (SELECT @var_record := 0, @libId := 0) tmp
ORDER BY libraryId;
第二个if语句用于将两个赋值组合在一起并返回@var_record.
The second if statement is used to group two assignments together and return @var_record.
if(@var_record := 1 and @libId := libraryId, @var_record, @var_record)
这篇关于MySQL选择顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!