在我的Sql中查找列的最大值

Question

这是材料表

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';

以下是输出

​

但是Quality返回错误的数据，..i需要最大的设备序列号作为特定设备的质量。ie在本案中8、12、20、40

Answer 1

正如草莓在评论中指出的那样，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，其中第一个查询给出了不正确的结果，第二个查询(使用强制转换)给出了正确的结果。

要按顺序获得DeviceSerialNumber中的group_concat，还应该向函数中添加order子句：

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;