试图将列值作为范围 [英] Trying to get column values as range

问题描述

我正在使用 mysql5.7.19 使用 pos 机打印卡片.表销售有serial_no、pos_machine_no 和card_amount 的列表.我想获得按card_amount

I'm using mysql5.7.19 to print cards using pos machine. Table sales has list of serial_no, pos_machine_no, and card_amount. I want to get the range of serial_no sold by a specific pos machine grouped by card_amount

小提琴

我想获得 pos_no 的所有范围:ps1

I wanted to get all the range for pos_no: ps1

我正在寻找的结果是:

|range  | card_amt|pos_no|

|100-102|   10    | ps1  |

|107-109|   10    | ps1  |

|111    |   5     | ps1  |

推荐答案

我假设您的预期结果中 111 的 card_amt 应该是 10.我采用了您之前查询的答案:

I assume that card_amt for 111 in your expected result should be 10. I adopted my answer from your previous query:

select dn.serial_no as dnval, min(up.serial_no) as upval, dn.card_amt, dn.pos_no  
from sales up
join sales dn
    on dn.serial_no <= up.serial_no
    and dn.pos_no = up.pos_no
    and dn.card_amt = up.card_amt
where not exists (
    select 1 from sales a 
    where a.serial_no = up.serial_no + 1 
    and a.pos_no = up.pos_no
    and a.card_amt = up.card_amt
)
and not exists (
    select 1 from sales b 
    where b.serial_no = dn.serial_no - 1 
    and b.pos_no = dn.pos_no
    and b.card_amt = dn.card_amt
)
group by dn.pos_no, dn.serial_no, dn.card_amt
order by dn.pos_no, dn.serial_no;

这个小提琴是对你的小提琴的轻微修改

This Fiddle is a slight modification of your fiddle

对于支持窗口函数的DBMS(以MySQL 8+为例)，可以构建更简洁、性能更高的SQL.

For DBMS that support window function (MySQL 8+ as an example), much neater and more performant SQL can be constructed.

这篇关于试图将列值作为范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！