SELECT t1.person_id, t1.name, t1.state, t1.company, t3.dept_0, t3.dept_1, t3.title, t3.city FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY effect DESC, syseffect DESC) AS rid, person_id, name, state, landed, `type`, company FROM tb_person WHERE landed <= '20230630') t1 WHERE t1.rid = '1' AND t1.`type` = '入职' LEFT JOIN (SELECT t2.person_id, t2.dept_0, t2.dept_1, t2.title, t2.city FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY effect DESC) AS rid, person_id, name, landed, dept_0, dept_1, city, title FROM tb_dept WHERE landed <= '20230630') t2 WHERE t2.rid = '1') t3 ON t1.person_id = t3.person_id报错了

根据你提供的 SQL 查询语句，报错的原因可能是在 WHERE 子句中使用了引号引起的字符串值。在 SQL 中，字符串值应该使用单引号（''）或者双引号（""）括起来。请检查以下部分的引号使用：

1. `WHERE t1.rid = '1' AND t1.`type` = '入职'`
2. `WHERE t2.rid = '1'`

请确保这些地方的引号使用正确，如果问题仍然存在，请提供具体的错误信息，以便更好地帮助您解决问题。

相关问题

WITH RECURSIVE tb_deptrelation (dept_id, dept_f, deptpath) AS ( -- 初始查询 SELECT t1.dept_id, t1.dept_f, t1.dept_id FROM db_attendance.tb_dept AS t1 WHERE dept_f IS NULL UNION ALL -- 递归查询 SELECT t2.dept_id, t2.dept_f, t3.deptpath + '-' + t2.dept_f FROM db_attendance.tb_dept AS t2 JOIN tb_deptrelation t3 ON t2.dept_id = t3.dept_id ) SELECT dept_id, dept_f, deptpath FROM tb_deptrelation; 哪有问题

根据你提供的查询语句，目前我没有发现任何明显的问题。该查询使用了递归公共表表达式（CTE）来构建一个部门层级关系的结果集。

初始查询选择了 `db_attendance.tb_dept` 表中 `dept_f` 为NULL的行作为起始行，然后递归查询通过连接CTE本身来获取下一级部门，并使用字符串连接操作符将部门路径拼接起来。

最后的SELECT语句从CTE中选择了 `dept_id`、`dept_f` 和 `deptpath` 作为输出列。

如果你运行这个查询并没有得到预期的结果，请提供更多的上下文信息，例如表 `db_attendance.tb_dept` 的结构和示例数据，以及你期望得到的输出结果。这样我可以更好地帮助你解决问题。

SELECT t1.organization_id, t1.organization_name, t1.road_name, SUM( t2.sj_amount ) xfSjAmount, SUM( t2.jl_amount ) xfJlAmount, SUM( t3.xfGeneralSectionAmount ) xfGeneralSectionAmount, SUM( t3.xfGeneralSectionLength ) xfGeneralSectionLength FROM `main_project` t1 LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id LEFT JOIN ( SELECT project_id, data_type, SUM( money ) xfGeneralSectionAmount, SUM( mileage ) xfGeneralSectionLength FROM mat_project_pavement_approval GROUP BY project_id ) t3 ON t3.project_id = t2.id WHERE t1.project_general_name = '修复性养护' AND t1.project_category_name = '路面工程' AND t1.process_status = '100' AND t3.data_type = '1' AND t1.road_name = '常祁高速' AND t1.project_year = '2023' 这条sql中t1.road_name多条数据只会展示一个，导致根据t1.road_name查询不到数据

这个问题的原因可能是使用了`LEFT JOIN`，导致t1表中的多个记录与t2和t3表中的记录进行了匹配。如果t1表中有多个记录的`t1.road_name`值相同，那么在`SELECT`语句中使用`SUM`函数对t2和t3表中的记录进行了合并，使得最终查询结果中只有一个记录。这可能导致你根据`t1.road_name`无法查到完整的数据。

为了解决这个问题，你可以考虑在`SELECT`语句中增加`t1.road_name`字段，并在`GROUP BY`子句中包含该字段，以确保t1表中的每个记录都能够被正确匹配和展示。例如：

SELECT
  t1.organization_id,
  t1.organization_name,
  t1.road_name,
  SUM(t2.sj_amount) AS xfSjAmount,
  SUM(t2.jl_amount) AS xfJlAmount,
  SUM(t3.xfGeneralSectionAmount) AS xfGeneralSectionAmount,
  SUM(t3.xfGeneralSectionLength) AS xfGeneralSectionLength
FROM
  main_project t1
  LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id
  LEFT JOIN (
    SELECT
      project_id,
      data_type,
      SUM(money) AS xfGeneralSectionAmount,
      SUM(mileage) AS xfGeneralSectionLength
    FROM
      mat_project_pavement_approval
    GROUP BY
      project_id
  ) t3 ON t3.project_id = t2.id
WHERE
  t1.project_general_name = '修复性养护'
  AND t1.project_category_name = '路面工程'
  AND t1.process_status = '100'
  AND t3.data_type = '1'
  AND t1.road_name = '常祁高速'
  AND t1.project_year = '2023'
GROUP BY
  t1.organization_id,
  t1.organization_name,
  t1.road_name;

这样，就可以确保每个`t1.road_name`值都能够正确地展示在查询结果中。