sql - SQL查询将X位置的N个字符与Y位置的M个字符进行匹配

Question

我试图弄清楚如何根据位置来匹配字符，但是我也希望子字符串根据位置来匹配。

这是我想做的事情：

    SELECT employee_table.LastFirst, employee_table.EmployeeId,
    MondayPlgReport.created_by,
        SUBSTR (employee_table.LastFirst, 0,5) AS employeeNameMatch
        SUBSTR (MondayPlgReport.created_by, 2,4) AS userNameMatch
    UPDATE MondayPlgReport
    SET MondayPlgReport.created_by=employee_table.EmployeeId
    WHERE employeeNameMatch=userNameMatch

我知道这很不对劲，但希望它能澄清我要达到的目标。

我也尝试过：

    SELECT LastFirst, EmployeeId,
      SUBSTR (LastFirst, 0,5) AS employeeNameMatch
    FROM employee_table

    SELECT created_by,
      SUBSTR (created_by, 2,4) AS userNameMatch
    FROM MondayPlgReport

    UPDATE MondayPlgReport
    SET MondayPlgReport.created_by=employee_table.EmployeeId
    WHERE employeeNameMatch=userNameMatch

当我跑

    SELECT LastFirst, EmployeeId,
      SUBSTR (LastFirst, 0,5) AS employeeNameMatch
    FROM employee_table

要么

    SELECT created_by,
      SUBSTR (created_by, 2,4) AS userNameMatch
    FROM MondayPlgReport

然后，我确实看到查询返回了我要查找的结果。这里的问题是员工经常更换，而我正在寻找另一个职位。如果每次有人离职或被雇用时都需要更新代码，那么我离开后对部门将毫无用处。

非常感谢您的帮助！

Answer 1

如果您使用的是mysql，则可以通过这种方式使用JOIN

UPDATE MondayPlgReport
JOIN mployee_table
SET MondayPlgReport.created_by=employee_table.EmployeeId
ON  SUBSTR(employee_table.LastFirst, 0,5)=SUBSTR(MondayPlgReport.created_by, 2,4)

对于sqlite

UPDATE MondayPlgReport
SET created_by = (select  EmployeeId 
                  from  employee_table 
                  where  SUBSTR(LastFirst, 0,5) = SUBSTR(MondayPlgReport.created_by, 2,4));