SELECT DISTINCT A.BEGIN_REPAIR_NO, A.PART_NO, A.CREATE_BY, B.EMPLOYEE_NAME AS CREATE_NAME, A.CREATE_TIME FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO
时间: 2024-02-14 13:35:24 浏览: 30
这是一条 SQL 查询语句,用于查询 TPMDATA 数据库中 PICKING 表中不同的 BEGIN_REPAIR_NO、PART_NO、CREATE_BY 和对应的 CREATE_NAME、CREATE_TIME。其中使用了 LEFT JOIN 关键字,将 TPMDATA.PICKING 表中的 CREATE_BY 字段与 TPMDATA.SAP_EMPLOYEE 表中的 EMPLOYEE_NO 字段关联起来,以便查询对应的 CREATE_NAME。
相关问题
SELECT DISTINCT A.BEGIN_REPAIR_NO, A.PART_NO, A.CREATE_BY, B.EMPLOYEE_NAME AS CREATE_NAME, A.CREATE_TIME FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO SELECT nvl(sum(A.CURRENT_QUANTITY),0) FROM TPMDATA.PICKING A LEFT JOIN TPMDATA.sys_part B ON A.PART_NO = B.PART_NO 将这俩条sql语句 合成一条
可以使用子查询将这两条 SQL 查询语句合并成一条:
SELECT DISTINCT
A.BEGIN_REPAIR_NO,
A.PART_NO,
A.CREATE_BY,
B.EMPLOYEE_NAME AS CREATE_NAME,
A.CREATE_TIME,
NVL((SELECT SUM(CURRENT_QUANTITY)
FROM TPMDATA.PICKING
LEFT JOIN TPMDATA.sys_part ON TPMDATA.PICKING.PART_NO = TPMDATA.sys_part.PART_NO
WHERE TPMDATA.PICKING.BEGIN_REPAIR_NO = A.BEGIN_REPAIR_NO AND TPMDATA.PICKING.PART_NO = A.PART_NO), 0) AS CURRENT_QUANTITY
FROM
TPMDATA.PICKING A
LEFT JOIN TPMDATA.SAP_EMPLOYEE B ON A.CREATE_BY = B.EMPLOYEE_NO;
这条语句将原本要查询的 SUM(CURRENT_QUANTITY) 作为子查询嵌入到了主查询中,并且增加了 WHERE 子句,以便查询对应的 BEGIN_REPAIR_NO 和 PART_NO。
修改代码“select a.employee_id, case when count(a.employee_id) = 1 then a.department_id when count(a.employee_id) != 1 then b.department_id end as department_id from Employee as a left join( select employee_id, department_id from Employee where primary_flag = 'Y') as b group by a.employee_id;”
这段代码的作用是查询每个员工的部门ID,如果该员工只在一个部门工作,则直接使用该部门ID,否则使用主部门ID。
根据给定的代码,我做了一些修改,具体如下:
```
SELECT a.employee_id,
CASE
WHEN COUNT(DISTINCT b.department_id) = 1 THEN MAX(b.department_id)
ELSE MAX(CASE WHEN b.primary_flag = 'Y' THEN b.department_id END)
END AS department_id
FROM Employee AS a
LEFT JOIN Employee AS b ON a.employee_id = b.employee_id
GROUP BY a.employee_id;
```
修改的思路如下:
1. 将子查询改为左连接原表,这样可以方便地处理每个员工在多个部门工作的情况;
2. 使用 MAX 函数结合 CASE 语句实现对部门ID的选择,如果该员工只在一个部门工作,则直接使用该部门ID;否则,使用主部门ID。
希望这次修改能够符合你的需求。