如何比较2个表Oracle数据库

发布于 2025-02-09 18:33:13 字数 905 浏览 1 评论 0原文

表1

我们将ID作为主键，他自己的工资

ID	名称	薪金
1	`“ x”`	500
2	`“ y”`	1000
3	`“ z”`	1500

表2

我们从付款系统

ID	日期	薪金
1	`“ 6/22/2020”`	500
2	`“ 6/25/2020”`	1000
3	<代码>“ 8/05/2021”	1500

我希望查询表2中的项目与表1进行比较，我的目标是确保每个员工在表1

示例

中的员工“ 1” 中获得确切的薪水薪水500在一个月的时间里，他只收到300个要检索员工

*优先：计算他的就业时间中他没有得到确切的薪水并计算有多大差异$

*注意：员工可能会得到多少差异。支付比他的薪水更少或更高

原文

Table 1

where we have ID as primary key and his own salary

ID	Name	Salary
1	`" X "`	500
2	`" Y "`	1000
3	`" Z "`	1500

table 2

where we have data from the payment system

ID	Date	Salary
1	`" 6/22/2020 "`	500
2	`" 6/25/2020 "`	1000
3	`" 8/05/2021 "`	1500

i want a query to compare items from table 2 with table 1 where my goal is to make sure every employee gets his exact salary paid as it in table 1

example

employee "1" with salary 500 let say on a month he received only 300 want to retrieve employee

* preferred : calculate how many months out of his employment time where he didn't get his exact salary and calculate how much difference $

* Note : the employee might get paid less or more than his salary

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泪眸﹌ 2025-02-16 18:33:13

这样的事情应该做到这一点：

select
  t1.salary as t1_salary,
  t2.*
from 
  t1 left outer join t2 on t1.id = t2.id

Something like this should do it:

select
  t1.salary as t1_salary,
  t2.*
from 
  t1 left outer join t2 on t1.id = t2.id

回复收藏 0 原文

最初的梦 2025-02-16 18:33:13

在ID上加入表，然后将差异放入子句中。

示例数据（您拥有它＆amp;您不必输入）：

SQL> with
  2  table1 (id, name, salary) as
  3    (select 1, 'x',  500 from dual union all
  4     select 2, 'y', 1000 from dual
  5    ),
  6  table2 (id, datum, salary) as
  7    (select 1, date '2022-01-01',  500 from dual union all
  8     select 1, date '2022-02-01',  300 from dual union all
  9     select 1, date '2022-03-01',  500 from dual union all
 10     select 1, date '2022-04-01',  700 from dual union all
 11     select 2, date '2022-01-01', 1000 from dual union all
 12     select 2, date '2022-02-01', 1000 from dual union all
 13     select 2, date '2022-03-01',  900 from dual union all
 14     select 2, date '2022-04-01', 1200 from dual
 15    )

查询从这里开始：

 16  select a.id,
 17    a.name,
 18    b.datum,
 19    b.salary - a.salary as difference
 20  from table1 a join table2 b on a.id = b.id
 21  where b.salary - a.salary <> 0;

        ID NAME DATUM      DIFFERENCE
---------- ---- ---------- ----------
         1 x    01.02.2022       -200
         1 x    01.04.2022        200
         2 y    01.03.2022       -100
         2 y    01.04.2022        200

SQL>

Join tables on ID and put the difference into the where clause.

Sample data (you have it & you don't have to type that):

SQL> with
  2  table1 (id, name, salary) as
  3    (select 1, 'x',  500 from dual union all
  4     select 2, 'y', 1000 from dual
  5    ),
  6  table2 (id, datum, salary) as
  7    (select 1, date '2022-01-01',  500 from dual union all
  8     select 1, date '2022-02-01',  300 from dual union all
  9     select 1, date '2022-03-01',  500 from dual union all
 10     select 1, date '2022-04-01',  700 from dual union all
 11     select 2, date '2022-01-01', 1000 from dual union all
 12     select 2, date '2022-02-01', 1000 from dual union all
 13     select 2, date '2022-03-01',  900 from dual union all
 14     select 2, date '2022-04-01', 1200 from dual
 15    )

Query begins here:

 16  select a.id,
 17    a.name,
 18    b.datum,
 19    b.salary - a.salary as difference
 20  from table1 a join table2 b on a.id = b.id
 21  where b.salary - a.salary <> 0;

        ID NAME DATUM      DIFFERENCE
---------- ---- ---------- ----------
         1 x    01.02.2022       -200
         1 x    01.04.2022        200
         2 y    01.03.2022       -100
         2 y    01.04.2022        200

SQL>

回复收藏 0 原文

~没有更多了~