sql 如何实现编号按指定格式自增

Question

高手，高手！！！员工编号：默认自动生成，可以手动修改；默认生成的格式如下,以BXY开头加6为数字，数字取数据库内最大编号加1。例如：BXY000001

Answer 1

private void button1_Click(object sender, EventArgs e)
{
    string maxid = "BXY000001"; //从库中取出maxid
    string newid = "BXY" + Convert.ToString((int.Parse(maxid.Substring(3)) + 1)).PadLeft(6, '0');
    MessageBox.Show(newid);
}

Answer 2

select max(convert(int,substring(字段名,4,6)))+1 from 表名

Answer 3

从库中取出maxid后，最好判断一下是否符合截取的条件（前面3位为BXY,后6位可以成功转成int）,之后再累加...

Answer 4

 public string POAutoNewNo(string format)
        {            string strJobYear = DateTime.Today.Year.ToString();
            string strJobMonth = DateTime.Today.Month.ToString();
            strJobMonth = strJobMonth.PadLeft(2, Convert.ToChar("0"));            string order_no = "";
            string temp = "";            int TempOrder_no = 0;            string selSQLPONO = "";            string PONO = "PO" + strJobYear + strJobMonth;
                          selSQLPONO = "Select top 1 order_no from tb_order_emea where order_no like '%" + PONO + "%' order by order_no DESC";
         
            con = new SqlConnection(DbConnStr());            try
            {
                con.Open();
                SqlDataReader dr = SqlHelper.ExecuteReader(con, CommandType.Text, selSQLPONO);
                if (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        temp = dr.GetString(0);
                        temp = temp.Replace(PO, "");
                        TempOrder_no = Convert.ToInt32(temp) + 1;                    }                }
                else
                {
                    TempOrder_no = 1;
                }
                temp = TempOrder_no.ToString().PadLeft(4, Convert.ToChar("0"));
                order_no = "PO" + strJobYear + strJobMonth + temp.ToString();            }
            catch (Exception ex)
            {
                return ex.Message.ToString();
            }
            finally
            {                con.Close();            }            return order_no;        }

Answer 5

create proc sp_employees()
as
declare @newId int;
begin
select @newId=max(convert(int,substring(员工编号,4,6)))+1 from 员工表 
end

Answer 6

max(convert(int,substring(字段名,4,6)))+1

Answer 7

谢谢 ，我已经知道了
这样最简单
CREATE TABLE [dbo].[EmpInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[EmpCode]  AS ('BYX'+right('00000'+rtrim([id]),(6))),
）