mysql - 获取mysql数据库中一列的最大值

Question

我的数据库表列值是

tenant_ id  group_id
2           2-100
2           2-111
1           1-222
1           1-888
2           2-999 
2           2-1000

查询:

select max(group_id) from prospect_group where tenant_id=2

我已经使用上面的查询来获取 tenant_id=2 的最大值，但它返回的值是 999 而不是 1000。如何获取 1000 作为最大值。？？？谁能帮帮我..？？？

Answer 1

你需要有 GROUP BY 子句

SELECT tenant_ID, MAX(CAST(group_ID AS SIGNED))
FROM tableName
-- WHERE  tenant_id=2 -- uncomment this if you select only for specific tenant_ID
GROUP BY tenant_ID

尝试替换为空字符。

SELECT tenant_ID, 
       MAX(CAST(REPLACE(group_ID, CONCAT(tenant_ID, '-'), '')  AS SIGNED)) maxGID
FROM tableName
-- WHERE  tenant_id=2 -- uncomment this if you select only for specific tenant_ID
GROUP BY tenant_ID

SQLFiddle Demo