是否交叉应用正确的方法来连接没有所有行的唯一标识符的表(重复记录)？
是否交叉应用正确的方法来连接没有所有行的唯一标识符的表(重复记录)？
提问于 2018-06-15 20:25:23
我对SQL非常陌生。我正在尝试连接两个表，tblEmployeeHours和tblWages。但是，工资表可以包含重复行。
下面是EmployeeHours表的一个片段：
x--------------x-------------x---------x-------x
| ReportDate | DeptCode | EmpID | Hours |
x--------------x-------------x---------x-------x
| 12/31/2017 | A | 112233 | 240 |
| 6/30/2018 | A | 112233 | 390 |
| 12/31/2017 | A | 224466 | 422 |
| 6/30/2018 | B | 334455 | 390 |
x--------------x-------------x---------x-------x工资表：
x----------x--------------x-------------x
| EmpID | CostCenter | HourlyWage |
x----------x--------------x-------------x
| 112233 | Engineering | 24.0 |
| 112233 | Engineering | 35.0 |
| 334455 | Field | 42.2 |
| 334455 | Field | 42.2 |
x----------x--------------x-------------x由于工资表中没有真正的唯一标识符，工资表中也没有每小时工资的生效日期(在本例中，我认为这是更大的问题)，因此如何确保我正在用正确的时薪计算薪资支票金额(HourlyWage x小时)？我们假设工资是按时间顺序排列的。
例如，对于雇员112233，24.0是旧工资，35.0是最近的工资。我想用35.0美元/小时计算他2018年6月30日的薪水，用24美元计算他2017年12月31日的薪水。
我认为交叉申请也许能达到最高1 *的水平，但这仍然只会返回24美元的第一(旧)工资。
select e.ReportDate, e.EmpID, e.Hours, tw.HourlyWage
from tblEmployeeHours e
cross apply
( select top 1 *
from tblWages w
where w.EmpID = e.EmpID
) tw结果：
x--------------x----------x--------x------------x
| ReportDate | EmpID | Hours | HourlyWage |
x--------------x----------x--------x------------x
| 12/31/2017 | 112233 | 240 | 24 |
| 6/30/2018 | 112233 | 390 | 24 |
x--------------x----------x--------x------------x预期结果：
x--------------x----------x--------x------------x
| ReportDate | EmpID | Hours | HourlyWage |
x--------------x----------x--------x------------x
| 12/31/2017 | 112233 | 240 | 24 |
| 6/30/2018 | 112233 | 390 | **35** |
x--------------x----------x--------x------------x回答 3
Stack Overflow用户
回答已采纳
发布于 2018-06-16 00:58:35
你可以这样做(快速和肮脏，请清理)。这应该能给你一个主意。注意:查询是针对这里的特定数据编写的(参见插入)。，这不是的确切答案。我只想给你们一个开始的想法-联合CTE，交叉/外部应用，窗口等等。
CREATE TABLE EmployeeHours (
ReportDate SMALLDATETIME,
DeptCode VARCHAR(1),
EmpID INT,
[Hours] INT
)
CREATE TABLE Wages (
EmpID INT,
CostCenter VARCHAR(25),
HourlyWage DECIMAL
)
INSERT INTO dbo.EmployeeHours
(
ReportDate,
DeptCode,
EmpID,
Hours
)
VALUES
('12/31/2017', 'A', 112233, 240 ),
('6/30/2018', 'A', 112233, 390 ),
('12/31/2017', 'A', 224466, 422 ),
('6/30/2018', 'B', 334455, 390 )
INSERT INTO dbo.Wages
(
EmpID,
CostCenter,
HourlyWage
)
VALUES
( 112233, 'Engineering', 24.0 ),
( 112233, 'Engineering', 35.0 ),
( 334455, 'Field', 42.2 ),
( 334455, 'Field', 42.2 )
--Query
WITH EmployeeWage AS (SELECT eh.ReportDate, eh.EmpID, eh.[Hours], w.HourlyWage
FROM dbo.EmployeeHours eh
JOIN Wages w ON eh.EmpID=w.EmpID)
,EmployeeWageChage AS (SELECT ROW_NUMBER() OVER (PARTITION BY a.empid, x.hourlywage, a.ReportDate
ORDER BY a.ReportDate, a.empid, x.HourlyWage) AS row_num, a.*
FROM EmployeeWage a
CROSS APPLY(SELECT HourlyWage FROM wages)x )
, result AS (SELECT DISTINCT reportDate, EmpID, Hours, HourlYwage
FROM EmployeeWageChage z
WHERE z.row_num=1)
, final AS (SELECT ROW_NUMBER() OVER (PARTITION BY reportdate, empid, hours ORDER BY reportdate, empid) AS rn, *
FROM result)
SELECT reportdate, empid, Hours, HourlyWage FROM final WHERE rn=1;
Stack Overflow用户
发布于 2018-06-15 20:34:01
SQL表表示无序集。没有订单。没有“时间顺序”或其他顺序。
这种方法：
select e.ReportDate, e.EmpID, e.Hours, tw.HourlyWage
from tblEmployeeHours e cross apply
(select top 1 *
from tblWages w
where w.EmpID = e.EmpID
) tw;任意选择任意匹配行。你真的需要：
select e.ReportDate, e.EmpID, e.Hours, tw.HourlyWage
from tblEmployeeHours e cross apply
(select top 1 *
from tblWages w
where w.EmpID = e.EmpID
order by w.effective_date -- or whatever column goes here
) tw;如果您有identity列或dateCreated列，则可以使用选择(例如)。
您可以假设工资总是上涨，并使用order by w.HourlyWage desc，但对于这个应用程序来说可能不是这样。
Stack Overflow用户
发布于 2018-06-17 09:38:06
表中没有时间顺序，但我认为工资会随着时间的推移而增加(希望如此)。
然后，可以为两个表分配一个row_number：
;WITH EmployeeHourRN AS
(
SELECT *
,ROW_NUMBER()
OVER (PARTITION BY empid
ORDER BY ReportDate desc) as rn
FROM dbo.EmployeeHours
)
,WagesRN AS
(
SELECT *
,ROW_NUMBER() -- assuming wages increase over time
OVER (PARTITION BY empid
ORDER BY HourlyWage desc) AS rn
FROM wages
)
select *
from EmployeeHourRN as e
left join WagesRN as w
on e.empid = w.empid
and e.rn = w.rn当然，如果EmployeeHour中有比工资更多的行(每个空行)，这将无法工作。那么应该分配给空224466的工资是多少？左联接将显示这两个问题，请参见小提琴
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50882233
复制相关文章
相似问题
腾讯云开发者
