需要临时表去做统计的话,我们可能会想到with,以下是简单的with用法
一.取单值
1.求年份是1986的时间的占比
WITH (
SELECT count()
FROM salaries
WHERE toYear(from_date) = 1986
) AS t1
SELECT round(t1 / count(), 2)
FROM salaries
2.分别求from_date 和 to_date的占比
WITH
(
SELECT count()
FROM salaries
WHERE toYear(from_date) = 1986
) AS t1,
(
SELECT count()
FROM salaries
WHERE toYear(to_date) = 1986
) AS t2
SELECT
t1 / count(),
t2 / count()
FROM salaries
二.取多值,这里我就用一个表演示一下
####一个表join
WITH t1 AS
(
SELECT
emp_no,
salary
FROM salaries
)
SELECT *
FROM salaries AS t2
INNER JOIN t1 ON t1.emp_no = t2.emp_no
LIMIT 20
Query id: db68570b-130d-4e35-8e06-fda4fb9e92b4
┌─emp_no─┬─salary─┬──from_date─┬────to_date─┬─t1.emp_no─┬─t1.salary─┐
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 60117 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 85097 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 88958 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 75994 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 76884 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 80013 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 81025 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 81097 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 84917 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 85112 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 62102 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 66074 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 66596 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 66961 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 71046 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 74333 │
│ 10001 │ 60117 │ 1986-06-26 │ 1987-06-26 │ 10001 │ 75286 │
│ 10001 │ 62102 │ 1987-06-26 │ 1988-06-25 │ 10001 │ 60117 │
│ 10001 │ 62102 │ 1987-06-26 │ 1988-06-25 │ 10001 │ 85097 │
│ 10001 │ 62102 │ 1987-06-26 │ 1988-06-25 │ 10001 │ 88958 │
└────────┴────────┴────────────┴────────────┴───────────┴───────────┘
###两个表join
WITH
t1 AS
(
SELECT
emp_no,
salary
FROM salaries
),
t3 AS
(
SELECT
emp_no,
from_date
FROM salaries
)
SELECT *
FROM salaries AS t2
INNER JOIN t1 ON t1.emp_no = t2.emp_no
INNER JOIN t3 ON t1.emp_no = t3.emp_no
LIMIT 5
Query id: 749d1d3d-ae77-43f2-88b7-fc88f9ce2bb7
┌─t2.emp_no─┬─t2.salary─┬─t2.from_date─┬─t2.to_date─┬─t1.emp_no─┬─t1.salary─┬─t3.emp_no─┬─t3.from_date─┐
│ 286476 │ 57757 │ 1992-01-03 │ 1993-01-02 │ 286476 │ 57757 │ 286476 │ 1992-01-03 │
│ 286476 │ 57757 │ 1992-01-03 │ 1993-01-02 │ 286476 │ 57757 │ 286476 │ 2000-01-01 │
│ 286476 │ 57757 │ 1992-01-03 │ 1993-01-02 │ 286476 │ 57757 │ 286476 │ 2000-12-31 │
│ 286476 │ 57757 │ 1992-01-03 │ 1993-01-02 │ 286476 │ 57757 │ 286476 │ 2001-12-31 │
│ 286476 │ 57757 │ 1992-01-03 │ 1993-01-02 │ 286476 │ 57757 │ 286476 │ 1988-01-04 │
└───────────┴───────────┴──────────────┴────────────┴───────────┴───────────┴───────────┴──────────────┘