I have a varchar field as my Employee ID and it is formated like this:
我有一个varchar字段作为我的员工ID，它的格式如下:
Format: YYYY-DateHired-Number Example: 2012-1203-0001
格式:YYYY-DateHired-Number例子:2012-1203-0001
But Im having hard time in inserting new record because to insert a new incremented ID, I need to get the latest value in this field before I increment it.
但是我在插入新记录时遇到了困难，因为要插入一个新的递增ID，我需要在这个字段中获取最新的值，然后再对它进行递增。
How can I get the row with the latest/highest (MAX) Numberin the most recent year?
我怎样才能得到最近一年里最新/最高的数字?
Thanks in advance :)
提前谢谢:)
2 个解决方案
#1
2
Assuming that your EmployeeID's format is YYYY-MMdd-XXXX. Try this,
假设您的EmployeeID格式是yyyy - mdd - xxxx。试试这个,
SELECT MAX(EmployeeID)
FROM tableName
WHERE EmployeeID LIKE CONCAT(SUBSTRING(EmployeeID, 1, 4),'%')
SQLFiddle Demo
#2
2
Hope this helps too:
希望这有助于:
SELECT * FROM tableName
WHERE EmployeeID IN (SELECT MAX(EmployeeID) FROM tableName)
http://sqlfiddle.com/#!2/2d100/14
http://sqlfiddle.com/ ! 2/2d100/14
#1
2
Assuming that your EmployeeID's format is YYYY-MMdd-XXXX. Try this,
假设您的EmployeeID格式是yyyy - mdd - xxxx。试试这个,
SELECT MAX(EmployeeID)
FROM tableName
WHERE EmployeeID LIKE CONCAT(SUBSTRING(EmployeeID, 1, 4),'%')
SQLFiddle Demo
#2
2
Hope this helps too:
希望这有助于:
SELECT * FROM tableName
WHERE EmployeeID IN (SELECT MAX(EmployeeID) FROM tableName)
http://sqlfiddle.com/#!2/2d100/14
http://sqlfiddle.com/ ! 2/2d100/14