SELECT LEFT(customer_id,2) AS PREFIX,
MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)
SQL Fiddle
MySQL 5.5.32 Schema Setup:
CREATE TABLE Table1
(`customer_id` varchar(6))
;
INSERT INTO Table1
(`customer_id`)
VALUES
('DD1234'),
('DD1222'),
('EE2345'),
('EE6789')
;
查询1:
SELECT LEFT(customer_id,2) AS PREFIX,
MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)
结果:
| PREFIX | MAX |
|--------|------|
| DD | 1234 |
| EE | 6789 |
编辑:
SELECT MAX(RIGHT(customer_id,4)) AS MAX
FROM table1
GROUP BY LEFT(customer_id,2)
WHERE LEFT(customer_id,2) = 'HE'