Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdd4e291

Browse files
authored
Improved task 3482
1 parent07daff0 commitdd4e291

File tree

5 files changed

+58
-39
lines changed

5 files changed

+58
-39
lines changed

‎build.gradle

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@ repositories {
1414
dependencies {
1515
testImplementation'org.junit.jupiter:junit-jupiter:[5.12.2,)'
1616
testImplementation'org.hamcrest:hamcrest-core:[3.0,)'
17-
testImplementation'org.zapodot:embedded-db-junit-jupiter:2.2.0'
17+
testImplementation'org.zapodot:embedded-db-junit-jupiter:2.2.2'
1818
testRuntimeOnly'org.junit.platform:junit-platform-launcher:[1.12.2,)'
1919
}
2020

‎pom-central.xml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -167,7 +167,7 @@
167167
<dependency>
168168
<groupId>org.zapodot</groupId>
169169
<artifactId>embedded-db-junit-jupiter</artifactId>
170-
<version>2.2.0</version>
170+
<version>2.2.2</version>
171171
<scope>test</scope>
172172
</dependency>
173173
</dependencies>

‎pom-central21.xml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -173,7 +173,7 @@
173173
<dependency>
174174
<groupId>org.zapodot</groupId>
175175
<artifactId>embedded-db-junit-jupiter</artifactId>
176-
<version>2.2.0</version>
176+
<version>2.2.2</version>
177177
<scope>test</scope>
178178
</dependency>
179179
</dependencies>

‎pom.xml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -190,7 +190,7 @@
190190
<dependency>
191191
<groupId>org.zapodot</groupId>
192192
<artifactId>embedded-db-junit-jupiter</artifactId>
193-
<version>2.2.0</version>
193+
<version>2.2.2</version>
194194
<scope>test</scope>
195195
</dependency>
196196
</dependencies>
Lines changed: 54 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -1,43 +1,62 @@
11
# Write your MySQL query statement below
2-
# #Hard #Database #2025_03_11_Time_712_ms_(100.00%)_Space_0.0_MB_(100.00%)
3-
with recursive org_hierarchy(orig_employee_id, orig_employee_name, employee_id, employee_name, manager_id, salary, org_level)as
4-
(
5-
select employee_idas orig_employee_id,
6-
employee_nameas orig_employee_name,
2+
# #Hard #Database #2025_05_30_Time_294_ms_(80.03%)_Space_0.0_MB_(100.00%)
3+
WITH RECURSIVE org_hierarchy (
4+
orig_employee_id,
5+
orig_employee_name,
76
employee_id,
87
employee_name,
98
manager_id,
109
salary,
11-
1as org_level
12-
from Employees
10+
org_level
11+
)AS (
12+
SELECT
13+
employee_idAS orig_employee_id,
14+
employee_nameAS orig_employee_name,
15+
employee_id,
16+
employee_name,
17+
manager_id,
18+
salary,
19+
1AS org_level
20+
FROM Employees
21+
1322
UNION ALL
14-
selectP.orig_employee_id,
15-
P.orig_employee_name,
16-
CH.employee_id,
17-
CH.employee_name,
18-
CH.manager_id,
19-
CH.salary,
20-
P.org_level+1
21-
from org_hierarchy P, Employees CH
22-
wherech.manager_id=P.employee_id
23+
24+
SELECT
25+
P.orig_employee_id,
26+
P.orig_employee_name,
27+
CH.employee_id,
28+
CH.employee_name,
29+
CH.manager_id,
30+
CH.salary,
31+
P.org_level+1
32+
FROM org_hierarchy P
33+
JOIN Employees CHONCH.manager_id=P.employee_id
2334
),
24-
CEO_hierarchyas (
25-
selectorg_hierarchy.employee_idas SUB_employee_id,
26-
org_hierarchy.employee_name,
27-
org_hierarchy.org_levelas sub_level
28-
from org_hierarchy, Employees
29-
whereorg_hierarchy.orig_employee_id=Employees.employee_id
30-
andEmployees.manager_id isnull
35+
CEO_hierarchy (
36+
sub_employee_id,
37+
employee_name,
38+
sub_level
39+
)AS (
40+
SELECT
41+
oh.employee_idAS sub_employee_id,
42+
oh.employee_name,
43+
oh.org_levelAS sub_level
44+
FROM org_hierarchy oh
45+
JOIN Employees eONoh.orig_employee_id=e.employee_id
46+
WHEREe.manager_id ISNULL
3147
)
32-
select
33-
org_hierarchy.ORIG_EMPLOYEE_IDas employee_id,
34-
org_hierarchy.ORIG_EMPLOYEE_nameas employee_name,
35-
CEO_hierarchy.sub_levelas"level",
36-
count(*)-1as team_size,
37-
sum(org_hierarchy.salary)as budget
38-
from org_hierarchy, CEO_hierarchy
39-
whereorg_hierarchy.ORIG_EMPLOYEE_ID=CEO_hierarchy.SUB_employee_id
40-
group byorg_hierarchy.ORIG_EMPLOYEE_ID,
41-
org_hierarchy.ORIG_EMPLOYEE_name,
42-
CEO_hierarchy.sub_level
43-
order by3asc,5desc,2
48+
49+
SELECT
50+
oh.orig_employee_idAS employee_id,
51+
oh.orig_employee_nameAS employee_name,
52+
ch.sub_levelAS level,
53+
COUNT(*)-1AS team_size,
54+
SUM(oh.salary)AS budget
55+
FROM org_hierarchy oh
56+
JOIN CEO_hierarchy chONoh.orig_employee_id=ch.sub_employee_id
57+
GROUP BY
58+
oh.orig_employee_id,
59+
oh.orig_employee_name,
60+
ch.sub_level
61+
ORDER BY
62+
levelASC, budgetDESC, employee_nameASC;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp