Skip to content

Commit 8bd50c5

Browse files
yanglbmeyanglbme
committed
feat: add sql solutions to lc problems: No.1378,1393,1445
1 parent 32d6ae6 commit 8bd50c5

File tree

9 files changed

+95
-6
lines changed

9 files changed

+95
-6
lines changed

‎solution/1300-1399/1378.Replace Employee ID With The Unique Identifier/README.md

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,16 @@ Jonathan 唯一标识码是 1 。
9090
### **SQL**
9191

9292
```sql
93-
93+
# Write your MySQL query statement below
94+
SELECT
95+
b.unique_id AS unique_id,
96+
a.name AS name
97+
FROM
98+
Employees a
99+
LEFT JOIN
100+
EmployeeUNI b
101+
ON
102+
a.id = b.id;
94103
```
95104

96105
<!-- tabs:end -->

‎solution/1300-1399/1378.Replace Employee ID With The Unique Identifier/README_EN.md

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,16 @@ The unique ID of Jonathan is 1.
8686
### **SQL**
8787

8888
```sql
89-
89+
# Write your MySQL query statement below
90+
SELECT
91+
b.unique_id AS unique_id,
92+
a.name AS name
93+
FROM
94+
Employees a
95+
LEFT JOIN
96+
EmployeeUNI b
97+
ON
98+
a.id = b.id;
9099
```
91100

92101
<!-- tabs:end -->

‎solution/1300-1399/1378.Replace Employee ID With The Unique Identifier/Solution.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
# Write your MySQL query statement below
2+
SELECT
3+
b.unique_id AS unique_id,
4+
a.name AS name
5+
FROM
6+
Employees a
7+
LEFT JOIN
8+
EmployeeUNI b
9+
ON
10+
a.id = b.id;

‎solution/1300-1399/1393.Capital GainLoss/README.md

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,12 +66,24 @@ Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美
6666

6767
<!-- 这里可写通用的实现逻辑 -->
6868

69+
70+
`CASE WHEN` + `GROUP BY`。
71+
6972
<!-- tabs:start -->
7073

7174
### **SQL**
7275

7376
```sql
74-
77+
# Write your MySQL query statement below
78+
SELECT
79+
stock_name,
80+
sum(
81+
CASE WHEN operation = 'Buy' THEN -price ELSE price END
82+
) AS capital_gain_loss
83+
FROM
84+
Stocks
85+
GROUP BY
86+
stock_name;
7587
```
7688

7789
<!-- tabs:end -->

‎solution/1300-1399/1393.Capital GainLoss/README_EN.md

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,16 @@ Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$.
7272
### **SQL**
7373

7474
```sql
75-
75+
# Write your MySQL query statement below
76+
SELECT
77+
stock_name,
78+
sum(
79+
CASE WHEN operation = 'Buy' THEN -price ELSE price END
80+
) AS capital_gain_loss
81+
FROM
82+
Stocks
83+
GROUP BY
84+
stock_name;
7685
```
7786

7887
<!-- tabs:end -->

‎solution/1300-1399/1393.Capital GainLoss/Solution.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
# Write your MySQL query statement below
2+
SELECT
3+
stock_name,
4+
sum(
5+
CASE WHEN operation = 'Buy' THEN -price ELSE price END
6+
) AS capital_gain_loss
7+
FROM
8+
Stocks
9+
GROUP BY
10+
stock_name;

‎solution/1400-1499/1445.Apples & Oranges/README.md

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,12 +64,23 @@ Result 表:
6464

6565
<!-- 这里可写通用的实现逻辑 -->
6666

67+
`CASE WHEN` + `GROUP BY`。
68+
6769
<!-- tabs:start -->
6870

6971
### **SQL**
7072

7173
```sql
72-
74+
# Write your MySQL query statement below
75+
SELECT
76+
sale_date AS SALE_DATE,
77+
sum(
78+
CASE WHEN fruit = 'oranges' THEN -sold_num ELSE sold_num END
79+
) AS DIFF
80+
FROM
81+
Sales
82+
GROUP BY sale_date
83+
ORDER BY sale_date;
7384
```
7485

7586
<!-- tabs:end -->

‎solution/1400-1499/1445.Apples & Oranges/README_EN.md

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,16 @@ Day 2020-05-04, 15 apples and 16 oranges were sold (Difference 15 - 16 = -1).
6767
### **SQL**
6868

6969
```sql
70-
70+
# Write your MySQL query statement below
71+
SELECT
72+
sale_date AS SALE_DATE,
73+
sum(
74+
CASE WHEN fruit = 'oranges' THEN -sold_num ELSE sold_num END
75+
) AS DIFF
76+
FROM
77+
Sales
78+
GROUP BY sale_date
79+
ORDER BY sale_date;
7180
```
7281

7382
<!-- tabs:end -->

‎solution/1400-1499/1445.Apples & Oranges/Solution.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
# Write your MySQL query statement below
2+
SELECT
3+
sale_date AS SALE_DATE,
4+
sum(
5+
CASE WHEN fruit = 'oranges' THEN -sold_num ELSE sold_num END
6+
) AS DIFF
7+
FROM
8+
Sales
9+
GROUP BY sale_date
10+
ORDER BY sale_date;

0 commit comments

Comments
 (0)