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报错了
时间: 2024-04-10 07:32:13 浏览: 17
根据你提供的 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_f = t3.dept_id ) SELECT dept_id, dept_f, deptpath FROM tb_deptrelation;哪有问题
根据你提供的查询语句,我注意到可能会有一个问题。在递归查询的第二个SELECT语句中,连接条件 `t2.dept_f = t3.dept_id` 可能是错误的。这取决于 `tb_dept` 表的结构和字段定义。
如果 `dept_f` 字段在 `tb_dept` 表中表示部门的父级部门ID,那么连接条件应该是 `t2.dept_id = t3.dept_id` 而不是 `t2.dept_f = t3.dept_id`。
请确保连接条件正确匹配表结构和字段定义。修改后的查询语句如下:
```sql
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;
```
请确保连接条件正确,并根据你的表结构做出适当的修改。希望这可以解决问题!如有其他问题,请随时提问。
SELECT t1.organization_id, t1.organization_name, SUM( t2.jl_amount ) yfSjAmount, SUM( t2.sj_amount ) yfJlAmount, SUM( t3.money ) yfSectionAmount, SUM( t3.mileage ) yfSectionLength FROM main_project t1 LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id LEFT JOIN mat_project_pavement_approval t3 ON t3.project_id = t2.id WHERE t1.project_general_name = '预防性养护' AND t1.project_category_name = '路面工程' AND t1.process_status = '100' AND t1.organization_id = 'b3f029fa-62ab-476e-9876-b87ce69c41c5' AND t1.project_year = '2023' SQL统计错误,请修改
这条SQL语句缺少结束符号,可以在语句末尾添加分号(;)来解决。
完整的SQL语句应该是:
```
SELECT t1.organization_id, t1.organization_name, SUM(t2.jl_amount) yfSjAmount, SUM(t2.sj_amount) yfJlAmount, SUM(t3.money) yfSectionAmount, SUM(t3.mileage) yfSectionLength
FROM main_project t1
LEFT JOIN mat_approval_project t2 ON t2.main_id = t1.id
LEFT JOIN mat_project_pavement_approval t3 ON t3.project_id = t2.id
WHERE t1.project_general_name = '预防性养护'
AND t1.project_category_name = '路面工程'
AND t1.process_status = '100'
AND t1.organization_id = 'b3f029fa-62ab-476e-9876-b87ce69c41c5'
AND t1.project_year = '2023';
```