张会挽's Blog

关于sql的学习记录

Posted on Edited on In

例如，假设数据如下：

SHOPORDER_SCHEDULING 表：

ID EQUIPMENT_CODE SHOPORDER PLAN_QTY
1 EQ001 SO1001 100
2 EQ001 SO1002 150
3 EQ002 SO1003 200
4 EQ002 SO1003 250
5 EQ003 SO1001 300

SN_STEP 表：

ID RESOURCE_NAME SHOPORDER ADOPT
1 EQ001 SO1001 0
2 EQ001 SO1001 1
3 EQ001 SO1002 0
4 EQ002 SO1003 1

左连接

在执行以下 SQL 查询： 
1
2
3
4
SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME;

根据提供的两个表的数据，我们可以推导出 LEFT JOIN 的结果。LEFT JOIN 的特性是返回左表（SHOPORDER_SCHEDULING）的所有记录，以及右表（SN_STEP）中符合连接条件的记录。如果右表中没有符合的记录，右表的列将返回 NULL。

结合上面的分析，执行 LEFT JOIN 后的结果将会是：

SC.ID SC.EQUIPMENT_CODE SC.SHOPORDER SC.PLAN_QTY SS.ID SS.RESOURCE_NAME SS.SHOPORDER SS.ADOPT
1 EQ001 SO1001 100 1 EQ001 SO1001 0
1 EQ001 SO1001 100 2 EQ001 SO1001 1
1 EQ001 SO1001 100 3 EQ001 SO1002 0
2 EQ001 SO1002 150 1 EQ001 SO1001 0
2 EQ001 SO1002 150 2 EQ001 SO1001 1
2 EQ001 SO1002 150 3 EQ001 SO1002 0
3 EQ002 SO1003 200 4 EQ002 SO1003 1
4 EQ002 SO1003 250 4 EQ002 SO1003 1
5 EQ003 SO1001 300 NULL NULL NULL NULL

多表查询

普通的左连接

执行以下 SQL 语句： 
1
2
3
4
5
SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER;
SC.ID SC.EQUIPMENT_CODE SC.SHOPORDER SC.PLAN_QTY SS.ID SS.RESOURCE_NAME SS.SHOPORDER SS.ADOPT
1 EQ001 SO1001 100 1 EQ001 SO1001 0
1 EQ001 SO1001 100 2 EQ001 SO1001 1
2 EQ001 SO1002 150 3 EQ001 SO1002 0
3 EQ002 SO1003 200 4 EQ002 SO1003 1
4 EQ002 SO1003 250 4 EQ002 SO1003 1
5 EQ003 SO1001 300 NULL NULL NULL NULL

左连接结果进行过滤

1
2
3
4
5
6
7
SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1);
SC.ID SC.EQUIPMENT_CODE SC.SHOPORDER SC.PLAN_QTY SS.ID SS.RESOURCE_NAME SS.SHOPORDER SS.ADOPT
1 EQ001 SO1001 100 1 EQ001 SO1001 0
1 EQ001 SO1001 100 2 EQ001 SO1001 1
2 EQ001 SO1002 150 3 EQ001 SO1002 0
3 EQ002 SO1003 200 4 EQ002 SO1003 1
4 EQ002 SO1003 250 4 EQ002 SO1003 1

结果进行统计

1
2
3
4
5
6
7
8
9
10
11
# Oracle版
SELECT SC.ID,
       COUNT(DISTINCT SS.ID) OUT_QTY,
       TO_CHAR((COUNT(DISTINCT SS.ID) / SC.PLAN_QTY * 100), 'fm99990.00') || '%' AS ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID, SC.PLAN_QTY;
1
2
3
4
5
6
7
8
9
10
11
# mysql版，mysql中没有TO_CHAR函数
SELECT SC.ID,
       COUNT(DISTINCT SS.ID) AS OUT_QTY,
       CONCAT(FORMAT(COUNT(DISTINCT SS.ID) / SC.PLAN_QTY * 100, 2), '%') AS ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID, SC.PLAN_QTY;

这个地方_**GROUP BY SC.ID, SC.PLAN_QTY**_只写_**GROUP BY SC.ID**_也可以

****

  • ID 1: 有两个匹配的 SN_STEP 行 (ADOPT 为 0 和 1)，所以 OUT_QTY 为 2。
  • ID 2: 有一个匹配的 SN_STEP 行 (ADOPT 为 0)，所以 OUT_QTY 为 1。
  • ID 3: 有一个匹配的 SN_STEP 行，所以 OUT_QTY 为 1。
  • ID 4: 有一个匹配的 SN_STEP 行 (ADOPT 为 1)，所以 OUT_QTY 为 1。
SC.ID OUT_QTY ACHIEVED_RATE
1 2 2.00%
2 1 0.67%
3 1 0.05%
4 1 0.40%

count和case结合

`COUNT()`** 函数**

+ `COUNT()` 是 SQL 中的聚合函数，用来统计非 `NULL` 值的数量。

`DISTINCT`** 关键字**

+ `COUNT(DISTINCT SS.ID)` 会统计 `ID` 列中的不同（不重复）的非 `NULL` 值数量。 + `DISTINCT` 关键字是为了去除重复值，在有多个相同 `ID` 时只计数一次。

`CASE`** 表达式**

+ `CASE` 是 SQL 中的条件表达式，类似于编程语言中的 `if-else`，可以根据条件选择返回值。 + `CASE WHEN SS.ADOPT = 2 THEN SS.ID END` 的意思是： - 如果 `SS.ADOPT = 2`，那么返回 `MSST.ID`。 - 如果不满足条件，则返回 `NULL`。 + 因为 `COUNT()` 只统计非 `NULL` 值，所以当 `ADOPT != 2` 时，`SN` 会被视为 `NULL`，不会计入统计。

count和case结合

1
2
3
4
5
6
7
8
9
10
11
12
SELECT SC.ID,
       COUNT(DISTINCT SS.ID) OUT_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 0 THEN SS.ID END) FALSE_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END) PASS_QTY,
       TO_CHAR(COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END)) / SC.PLAN_QTY * 100, 'fm99990.00') || '%' ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID, SC.PLAN_QTY
1
2
3
4
5
6
7
8
9
10
11
12
SELECT SC.ID,
       COUNT(DISTINCT SS.ID) AS OUT_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 0 THEN SS.ID END) AS FALSE_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END) AS PASS_QTY,
       CONCAT(FORMAT(COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END) / SC.PLAN_QTY * 100, 2), '%') AS ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID, SC.PLAN_QTY;
  1. ID = 1:
    • OUT_QTY: 2 (ID 1 和 ID 2)
    • FALSE_QTY: 1 (ID 1)
    • PASS_QTY: 1 (ID 2)
    • ACHIEVED_RATE: (1 / 100) * 100 = 1.00%
  2. ID = 2:
    • OUT_QTY: 1 (ID 3)
    • FALSE_QTY: 1 (ID 3)
    • PASS_QTY: 0
    • ACHIEVED_RATE: (0 / 150) * 100 = 0.00%
  3. ID = 3:
    • OUT_QTY: 1 (ID 4)
    • FALSE_QTY: 0
    • PASS_QTY: 1 (ID 4)
    • ACHIEVED_RATE: (1 / 200) * 100 = 0.50%
  4. ID = 4:
    • OUT_QTY: 1 (ID 4)
    • FALSE_QTY: 0
    • PASS_QTY: 1 (ID 4)
    • ACHIEVED_RATE: (1 / 250) * 100 = 0.40%
SC.ID OUT_QTY FALSE_QTY PASS_QTY ACHIEVED_RATE
1 2 1 1 1.00%
2 1 1 0 0.00%
3 1 0 1 0.50%
4 1 0 1 0.40% 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
CREATE TABLE SHOPORDER_SCHEDULING (
    ID INT PRIMARY KEY,                  -- 主键
    EQUIPMENT_CODE VARCHAR(50),          -- 设备编码
    SHOPORDER VARCHAR(50),               -- 订单编号
    PLAN_QTY INT                         -- 计划数量
);
CREATE TABLE SN_STEP (
    ID INT PRIMARY KEY,                  -- 主键
    RESOURCE_NAME VARCHAR(50),           -- 资源名称，等同于设备编码
    SHOPORDER VARCHAR(50),               -- 订单编号
    ADOPT INT                            -- 采用标志 (例如 0, 1, 2)
);
INSERT INTO SHOPORDER_SCHEDULING (ID, EQUIPMENT_CODE, SHOPORDER, PLAN_QTY)
VALUES 
(1, 'EQ001', 'SO1001', 100),
(2, 'EQ001', 'SO1002', 150),
(3, 'EQ002', 'SO1003', 200),
(4, 'EQ002', 'SO1003', 250),
(5, 'EQ003', 'SO1001', 300);

INSERT INTO SN_STEP (ID, RESOURCE_NAME, SHOPORDER, ADOPT)
VALUES 
(1, 'EQ001', 'SO1001', 0),
(2, 'EQ001', 'SO1001', 1),
(3, 'EQ001', 'SO1002', 0),
(4, 'EQ002', 'SO1003', 1);

SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME;

SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER;

SELECT *
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1);


SELECT SC.ID,
       COUNT(DISTINCT SS.ID) AS OUT_QTY,
       CONCAT(FORMAT(COUNT(DISTINCT SS.ID) / SC.PLAN_QTY * 100, 2), '%') AS ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID;

SELECT SC.ID,
       COUNT(DISTINCT SS.ID) AS OUT_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 0 THEN SS.ID END) AS FALSE_QTY,
       COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END) AS PASS_QTY,
       CONCAT(FORMAT(COUNT(DISTINCT CASE WHEN SS.ADOPT = 1 THEN SS.ID END) / SC.PLAN_QTY * 100, 2), '%') AS ACHIEVED_RATE
FROM SHOPORDER_SCHEDULING SC
LEFT JOIN SN_STEP SS
ON SC.EQUIPMENT_CODE = SS.RESOURCE_NAME
AND SC.SHOPORDER = SS.SHOPORDER
WHERE SC.EQUIPMENT_CODE IS NOT NULL
AND SS.ADOPT IN (0, 1)
GROUP BY SC.ID, SC.PLAN_QTY;