mysql - 在 My Sql 中查找列的最大值

Question

这是 Material 表

ID DCNumber  InvoiceNumber  DeviceModel                          DeviceSerialNumber
 1 NULL               1001  Audio Door Phone                      1
 2 NULL               1001  Audio Door Phone                      2
 3 NULL               1001  Audio Door Phone                      3
 4 NULL               1001  Audio Door Phone                      4
 5 NULL               1001  Audio Door Phone                      5
 6 NULL               1001  Audio Door Phone                      6
 7 NULL               1001  Audio Door Phone                      7
 8 NULL               1001  Audio Door Phone                      8
 9 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   1
10 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)   2
11 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   3
12 NULL               1001  Forgo cleaning kit HDP-5000.(89200)   4
13 NULL               1001  Forgo cleaning ldt.HDP-5000.(89200)   5
14 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   6
15 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   7
16 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)   8
17 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)   9
18 NULL               1001  Forgo cleaning kit,HDP-5000.(89200)  10
19 NULL               1001  Forgo cleaning kit.HDP-5000.(89200)  11
20 NULL               1001  Forgo cleaning kit HDP-5000.(89200)  12
21 NULL               1001  IR Bullet Camera TC62L2A              1

以 ID 作为主键，我正在尝试执行以下查询..

select DeviceModel,group_concat(`DeviceSerialNumber` separator ',') ``as`SerialNumbers`,max(DeviceSerialNumber) as 'Quantity' from materials  group by InvoiceNumber,DeviceModel having InvoiceNumber= '1001';

以下是输出

但质量列返回错误数据..我需要最大设备序列号作为特定设备的质量。即本例中为 8,12,20,40

Answer 1

正如 Strawberry 在评论中指出的，问题很可能是 DevinceSerialNumber 列是字符类型，这可以解释为什么 9 高于 12。解决方案是将列转换为整数:

select
  DeviceModel,
  group_concat(`DeviceSerialNumber`  separator ',') 
as "SerialNumbers",
  max(cast(DeviceSerialNumber as unsigned)) as "Quantity" 
from materials  
where InvoiceNumber= '1001'
group by InvoiceNumber, DeviceModel;

查看此示例 SQL Fiddle其中第一个查询给出了错误的结果，第二个查询(使用强制转换)给出了正确的结果。

要按顺序获取 group_concat 中的 DeviceSerialNumber，您还应该向函数添加 order by 子句:

group_concat(
  cast(DeviceSerialNumber as unsigned) 
  order by cast(DeviceSerialNumber as unsigned) 
  separator ','
) 

最后，为了避免在多个地方使用强制转换，您可以在派生表中进行强制转换:

select
  DeviceModel,
  group_concat(DeviceSerialNumber order by DeviceSerialNumber separator ',') 
as "SerialNumbers",
  max(DeviceSerialNumber) as "Quantity" 
from (
  select 
    DeviceModel, 
    InvoiceNumber, 
    cast(DeviceSerialNumber as unsigned) DeviceSerialNumber
  from materials
) materials
where InvoiceNumber= '1001'
group by InvoiceNumber, DeviceModel;