sql - SQLite如何查询这个？
问题描述
表：
CREATE TABLE "Employees" (
"Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"Name" TEXT NOT NULL,
"EnrollmentDate" INTEGER NOT NULL,
"BaseSalary" REAL NOT NULL,
"ChiefId" INTEGER,
"PositionId" INTEGER
)
CREATE TABLE "Position" (
"Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
"PositionName" INTEGER NOT NULL,
"YearPercent" INTEGER NOT NULL,
"MaxYearPercent" INTEGER
)
数据位置：
INSERT INTO "main"."Position" ("Id", "PositionName", "YearPercent", "MaxYearPercent") VALUES ('1', 'Employee', '0.03', '0.3');
INSERT INTO "main"."Position" ("Id", "PositionName", "YearPercent", "MaxYearPercent") VALUES ('2', 'Manager', '0.05', '0.4');
INSERT INTO "main"."Position" ("Id", "PositionName", "YearPercent", "MaxYearPercent") VALUES ('3', 'Salesman', '0.01', '0.35');
数据员工：
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('1', 'A', '2019-05-18', '10000.0', '5', '1');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('2', 'B', '2019-07-26', '10000.0', '5', '1');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('3', 'C', '1990-09-19', '15000.0', '12', '1');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('4', 'D', '2017-01-20', '12000.0', '6', '1');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('5', 'E', '2017-01-20', '12000.0', '', '2');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('6', 'F', '2016-01-20', '18000.0', '7', '2');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('7', 'G', '2015-05-15', '11500', '', '2');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('8', 'H', '2009-03-01', '12300', '7', '2');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('9', 'I', '2001-02-12', '14000', '', '3');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('10', 'G', '2013-05-20', '25000', '8', '3');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('11', 'K', '2019-07-26', '5000', '', '3');
INSERT INTO "main"."Employees" ("Id", "Name", "EnrollmentDate", "BaseSalary", "ChiefId", "PositionId") VALUES ('12', 'L', '2016-01-20', '2000', '', '3');
整个问题听起来：“有3组员工-员工，经理和销售员。每个员工都可能是老板。除员工之外的每个员工都可能有下属。
员工工资计算如下：
- 员工- BaseSalary + 每年工作的 3%，但不超过 30%
- 经理- BaseSalary + 每年 5%，但不超过 40% + 第一层级下属工资的 0.5%
- 推销员- BaseSalary + 每年 1%，但不超过 35% + 整个层级下属工资的 0.3%
查询：“选择所有员工及其上半年的工资（月份应该是水平的）”。
我试图通过小步骤来实现这一目标。首先，我计算了没有下属的薪水（我认为这很奇怪）：
SELECT Name,
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '1 month',
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '2 month',
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '3 month',
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '4 month',
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '5 month',
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId) THEN BaseSalary + BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = Employees.PositionId)
ELSE BaseSalary + BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)) * (SELECT YearPercent FROM Position WHERE Position.Id = Employees.PositionId)
END AS '6 month'
FROM Employees;
然后我计算了经理（第一级下属）的薪水：
SELECT e.Name, SUM(s.BaseSalary)
FROM Employees e
JOIN Employees s on e.Id = s.ChiefId
GROUP BY e.Name;
我为推销员（整个层次结构）计算薪水的最后一步：
WITH Subordinates (Id, ParentId) AS (
SELECT Id, Id as ParentId
FROM Employees
UNION ALL
SELECT e.Id, s.ParentId
FROM Employees e INNER JOIN Subordinates s ON (s.Id = e.ChiefId)
)
SELECT e.Name, sum(e1.BaseSalary)
FROM Subordinates s
JOIN Employees e on e.Id = s.ParentId
LEFT JOIN Employees e1 on e1.Id = s.Id and s.Id != s.ParentId
GROUP BY e.Name
ORDER BY e.Name;
下一步应该只是烘烤，在 1 个查询中我坚持这个（查询只是不计算员工工资正确，我不知道为什么会这样）：
WITH M1DIF AS (
SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
),
M2DIF AS (
SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
),
M3DIF AS (
SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
),
M4DIF AS (
SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
),
M5DIF AS (
SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
),
M6DIF AS (
SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', EnrollmentDate) + strftime('%j', EnrollmentDate) / 365.2422) AS INT)
FROM Employees
)
SELECT e.Name,
CASE
WHEN (SELECT * FROM M1DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN
CASE
WHEN e.PositionId = 1 THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
WHEN e.PositionId = 2 THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId) + (CASE WHEN (SELECT SUM(e1.BaseSalary) FROM Employees e1 WHERE e1.ChiefId = e.Id) IS NULL THEN 0 ELSE (SELECT SUM(e1.BaseSalary) FROM Employees e1 WHERE e1.ChiefId = e.Id) END) * 0.05
END
ELSE
CASE
WHEN e.PositionId = 1 THEN e.BaseSalary + e.BaseSalary * (SELECT * FROM M1DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
WHEN e.PositionId = 2 THEN e.BaseSalary + e.BaseSalary * (SELECT * FROM M1DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) + (CASE WHEN (SELECT SUM(e1.BaseSalary) FROM Employees e1 WHERE e1.ChiefId = e.Id) IS NULL THEN 0 ELSE (SELECT SUM(e1.BaseSalary) FROM Employees e1 WHERE e1.ChiefId = e.Id) END) * 0.05
END
END AS '1 month',
CASE
WHEN (SELECT * FROM M2DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
ELSE e.BaseSalary + e.BaseSalary * (SELECT * FROM M2DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
END AS '2 month',
CASE
WHEN (SELECT * FROM M3DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
ELSE e.BaseSalary + e.BaseSalary * (SELECT * FROM M3DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
END AS '3 month',
CASE
WHEN (SELECT * FROM M4DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
ELSE e.BaseSalary + e.BaseSalary * (SELECT * FROM M4DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
END AS '4 month',
CASE
WHEN (SELECT * FROM M5DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
ELSE e.BaseSalary + e.BaseSalary * (SELECT * FROM M5DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
END AS '5 month',
CASE
WHEN (SELECT * FROM M6DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId) >= (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
THEN e.BaseSalary + e.BaseSalary * (SELECT MaxYearPercent FROM Position WHERE Position.Id = e.PositionId)
ELSE e.BaseSalary + e.BaseSalary * (SELECT * FROM M6DIF) * (SELECT YearPercent FROM Position WHERE Position.Id = e.PositionId)
END AS '6 month'
FROM Employees e;
PS我是SQL的新手。
编辑：知道使用 WITH 语句是错误的决定。
解决方案
用这种方式完成它：
WITH Subordinates (Id, ParentId) AS (
SELECT Id, Id as ParentId
FROM Employees
UNION ALL
SELECT e.Id, s.ParentId
FROM Employees e INNER JOIN Subordinates s ON (s.Id = e.ChiefId)
)
SELECT emain.Name,
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-1 month')) + strftime('%j', date('now', '-1 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 1',
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-2 month')) + strftime('%j', date('now', '-2 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 2',
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-3 month')) + strftime('%j', date('now', '-3 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 3',
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-4 month')) + strftime('%j', date('now', '-4 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 4',
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-5 month')) + strftime('%j', date('now', '-5 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 5',
(CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
THEN emain.BaseSalary + emain.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = emain.PositionId)
ELSE emain.BaseSalary + emain.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', emain.EnrollmentDate) + strftime('%j', emain.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = emain.PositionId)
END) +
(Case
WHEN emain.PositionId = 1
THEN 0
WHEN emain.PositionId = 2
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
THEN s.BaseSalary + s.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = s.PositionId)
ELSE s.BaseSalary + s.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', s.EnrollmentDate) + strftime('%j', s.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = s.PositionId)
END),0)
FROM Employees e
LEFT JOIN Employees s ON e.Id = s.ChiefId AND e.Id = emain.Id
) * 0.005
WHEN emain.PositionId = 3
THEN (
SELECT ifnull(SUM(
CASE
WHEN (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId) >= (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
THEN e1.BaseSalary + e1.BaseSalary * (SELECT p.MaxYearPercent FROM Position p WHERE p.Id = e1.PositionId)
ELSE e1.BaseSalary + e1.BaseSalary * (SELECT CAST((strftime('%Y', date('now', '-6 month')) + strftime('%j', date('now', '-6 month')) / 365.2422) - (strftime('%Y', e1.EnrollmentDate) + strftime('%j', e1.EnrollmentDate) / 365.2422) AS INT)) * (SELECT p.YearPercent FROM Position p WHERE p.Id = e1.PositionId)
END
),0) as 'Pay'
FROM Subordinates s
JOIN Employees e ON e.Id = s.ParentId
LEFT JOIN Employees e1 ON e1.Id = s.Id AND s.Id != s.ParentId AND e.Id = emain.Id
) * 0.003
END) AS 'Month 6'
FROM Employees emain;
推荐阅读
- spring-boot - 使用 Spring Core JdbcTemplate 时查询不超时
- python - 在数据框列上应用 select_dtypes
- json - Scala - 如何获取 Json 字段的最大值？
- javascript - 有没有办法在基本地图中禁用 3d 建筑物视图？Javascript HERE 地图
- python - 如何使用用户提供的城市并使用它进行回复？使用如果
- php - 如何在 json 索引中对 JsonArray 结果内容进行分组
- javascript - 如何确保在 HTTP 拦截器之后发生在 observable 上的操作符？
- java - 无法解析导入 - Visual Studio Code for Java
- html - 如何向屏幕阅读器指示内容是内联可编辑的？
- javascript - Safari 上的 onclick 链接事件问题