SQL 从数据库取出"干净、正确粒度"的数据
   ↓
Power BI / Python 做可视化和建模
   ↓
Excel 做临时查看和汇报

客户表 ──< 订单表 ──< 工单表 ──< 报工记录表
                        │
                        ├──< 质量检验表
                        └──< 物料消耗表
产品表 ──< BOM表 ──< 物料表
设备表 ──< 设备状态记录表

SELECT    -- 要哪些列
FROM      -- 从哪张表
WHERE     -- 行过滤条件
GROUP BY  -- 按什么分组
HAVING    -- 分组后的过滤
ORDER BY  -- 排序
LIMIT     -- 限制行数

1. FROM      确定数据来源
2. WHERE     行级过滤
3. GROUP BY  分组
4. HAVING    组级过滤
5. SELECT    选择列
6. ORDER BY  排序
7. LIMIT     限制

-- 基础查询
SELECT work_order_no, product_code, plan_qty, actual_qty
FROM mes_work_order;

-- 全部列（生产环境慎用，字段多时慢）
SELECT * FROM mes_work_order;

-- 去重
SELECT DISTINCT product_code FROM mes_work_order;

-- 计算列与别名
SELECT
    work_order_no,
    plan_qty,
    actual_qty,
    actual_qty - plan_qty AS diff_qty,
    ROUND(actual_qty * 1.0 / plan_qty * 100, 2) AS complete_rate_pct
FROM mes_work_order;

-- 比较运算
WHERE plan_qty > 1000
WHERE product_code = 'A001'
WHERE actual_start_time >= '2026-01-01'

-- 范围
WHERE plan_qty BETWEEN 100 AND 1000
WHERE product_code IN ('A001', 'A002', 'A003')

-- 模糊匹配
WHERE product_name LIKE '%轴承%'    -- 包含"轴承"
WHERE work_order_no LIKE 'WO2026%'  -- 以 WO2026 开头

-- 空值判断（重要！）
WHERE actual_end_time IS NULL       -- 正确
WHERE actual_end_time = NULL        -- 错误！永远返回空

-- 组合
WHERE status = 'CLOSED'
  AND actual_end_time >= '2026-01-01'
  AND (product_code IN ('A001','A002') OR priority = 'URGENT')

-- 取反
WHERE status NOT IN ('CANCELLED', 'DRAFT')

-- 按产线统计工单数与产量
SELECT
    line_code,
    COUNT(*)                    AS order_count,
    SUM(plan_qty)               AS total_plan,
    SUM(actual_qty)             AS total_actual,
    ROUND(AVG(actual_qty * 1.0 / plan_qty) * 100, 2) AS avg_complete_pct
FROM mes_work_order
WHERE status = 'CLOSED'
GROUP BY line_code
ORDER BY total_actual DESC;

-- 只显示工单数超过 10 的产线
SELECT line_code, COUNT(*) AS order_count
FROM mes_work_order
GROUP BY line_code
HAVING COUNT(*) > 10;

-- HAVING 与 WHERE 的区别
-- WHERE  在分组【前】过滤行（不能用聚合函数）
-- HAVING 在分组【后】过滤组（通常用聚合函数）

-- 工单 + 产品名称
SELECT
    wo.work_order_no,
    wo.plan_qty,
    p.product_name,
    p.unit
FROM mes_work_order wo
LEFT JOIN md_product p
    ON wo.product_code = p.product_code;

SELECT
    wo.work_order_no,
    p.product_name,
    l.line_name,
    e.equip_name,
    SUM(rpt.good_qty) AS total_good
FROM mes_work_order wo
LEFT JOIN md_product p   ON wo.product_code = p.product_code
LEFT JOIN md_line l      ON wo.line_code = l.line_code
LEFT JOIN md_equip e     ON wo.equip_code = e.equip_code
LEFT JOIN mes_report rpt ON wo.work_order_no = rpt.work_order_no
GROUP BY wo.work_order_no, p.product_name, l.line_name, e.equip_name;

-- 找出产量高于平均产量的工单
SELECT work_order_no, actual_qty
FROM mes_work_order
WHERE actual_qty > (
    SELECT AVG(actual_qty) FROM mes_work_order
);

WITH line_summary AS (
    SELECT
        line_code,
        COUNT(*) AS order_count,
        SUM(actual_qty) AS total_qty
    FROM mes_work_order
    WHERE status = 'CLOSED'
    GROUP BY line_code
)
SELECT
    line_code,
    order_count,
    total_qty,
    RANK() OVER (ORDER BY total_qty DESC) AS qty_rank
FROM line_summary
WHERE order_count > 5
ORDER BY qty_rank;

WITH
base AS (...),      -- 第一步：取基础数据
agg  AS (...),      -- 第二步：聚合
final AS (...)      -- 第三步：计算指标
SELECT * FROM final;

WITH ranked AS (
    SELECT
        work_order_no,
        status,
        update_time,
        ROW_NUMBER() OVER (
            PARTITION BY work_order_no
            ORDER BY update_time DESC
        ) AS rn
    FROM mes_wo_status_log
)
SELECT work_order_no, status, update_time
FROM ranked
WHERE rn = 1;   -- 只取每个工单最新的一条

WITH flow AS (
    SELECT
        work_order_no,
        operation_seq,
        operation_name,
        start_time,
        end_time,
        LEAD(start_time) OVER (
            PARTITION BY work_order_no
            ORDER BY operation_seq
        ) AS next_start_time
    FROM mes_operation_record
)
SELECT
    work_order_no,
    operation_name,
    DATEDIFF(MINUTE, start_time, end_time) AS process_min,
    DATEDIFF(MINUTE, end_time, next_start_time) AS wait_min  -- 工序间等待
FROM flow;

SELECT
    stat_date,
    daily_output,
    AVG(daily_output) OVER (
        ORDER BY stat_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma7
FROM daily_output_summary
ORDER BY stat_date;

-- 当前时间
GETDATE()                        -- SQL Server
NOW()                            -- MySQL / PostgreSQL
SYSDATE                          -- Oracle

-- 日期截断
CAST(GETDATE() AS DATE)          -- 只要日期部分
CONVERT(VARCHAR(7), GETDATE(), 120)  -- '2026-09' 年月

-- 日期加减
DATEADD(DAY, -7, GETDATE())      -- 7 天前
DATEADD(MONTH, -1, GETDATE())    -- 1 个月前

-- 日期差
DATEDIFF(DAY, start_time, end_time)    -- 天数差
DATEDIFF(MINUTE, start_time, end_time) -- 分钟差（最常用）
DATEDIFF(SECOND, start_time, end_time) -- 秒差

-- 提取部分
YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())
DATEPART(WEEKDAY, GETDATE())     -- 星期几
DATEPART(HOUR, GETDATE())        -- 小时（班次分析用）

-- 构造日期
DATEFROMPARTS(2026, 9, 1)        -- SQL Server 2012+

-- 根据小时判断班次（白班 8:00-20:00，夜班 20:00-次日8:00）
CASE
    WHEN DATEPART(HOUR, record_time) >= 8
     AND DATEPART(HOUR, record_time) < 20 THEN '白班'
    ELSE '夜班'
END AS shift

-- 班次日期 = 如果是 0-8 点，归属前一天
CASE
    WHEN DATEPART(HOUR, record_time) < 8
    THEN DATEADD(DAY, -1, CAST(record_time AS DATE))
    ELSE CAST(record_time AS DATE)
END AS shift_date

SELECT
    work_order_no,
    DATEDIFF(HOUR, actual_start, actual_end) AS duration_h,
    CASE
        WHEN DATEDIFF(HOUR, actual_start, actual_end) <= 4  THEN '1. 短(≤4h)'
        WHEN DATEDIFF(HOUR, actual_start, actual_end) <= 12 THEN '2. 中(4-12h)'
        WHEN DATEDIFF(HOUR, actual_start, actual_end) <= 24 THEN '3. 长(12-24h)'
        ELSE '4. 超长(>24h)'
    END AS duration_bucket
FROM mes_work_order;

SELECT
    line_code,
    COUNT(*) AS total_order,
    SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_order,
    SUM(CASE WHEN actual_qty >= plan_qty THEN 1 ELSE 0 END) AS on_target_order,
    SUM(CASE WHEN defect_qty > 0 THEN defect_qty ELSE 0 END) AS total_defect
FROM mes_work_order
GROUP BY line_code;

md_customer ──< erp_sales_order ──< mes_work_order ──< mes_report
                                          │
md_product ──< md_routing ────────────────┤
    │                                      ├──< qms_inspection ──< qms_defect
    └──< md_bom ──< md_material            ├──< mes_material_consume
                                           └──< mes_downtime

md_line ──< md_workcenter ──< md_equipment ──< mes_equip_status
                                    └──< eam_maintenance

-- 1. 看有哪些表
SELECT name FROM sys.tables ORDER BY name;          -- SQL Server
SHOW TABLES;                                         -- MySQL
SELECT table_name FROM information_schema.tables;    -- 通用

-- 2. 看表结构
EXEC sp_columns 'mes_work_order';                    -- SQL Server
DESC mes_work_order;                                 -- MySQL
SELECT * FROM information_schema.columns
WHERE table_name = 'mes_work_order';                 -- 通用

-- 3. 看数据量
SELECT COUNT(*) FROM mes_work_order;

-- 4. 抽样看数据
SELECT TOP 100 * FROM mes_work_order;                -- SQL Server
SELECT * FROM mes_work_order LIMIT 100;              -- MySQL/PG

-- 5. 看关键字段的取值分布（理解枚举值含义）
SELECT status, COUNT(*) AS cnt
FROM mes_work_order
GROUP BY status
ORDER BY cnt DESC;

-- 6. 看时间范围
SELECT MIN(create_time), MAX(create_time) FROM mes_work_order;

WITH equip_time AS (
    SELECT
        equip_code,
        CAST(shift_date AS DATE) AS stat_date,
        SUM(CASE WHEN status = 'RUNNING' THEN duration_min ELSE 0 END) AS running_min,
        SUM(CASE WHEN status = 'IDLE'    THEN duration_min ELSE 0 END) AS idle_min,
        SUM(CASE WHEN status = 'FAULT'   THEN duration_min ELSE 0 END) AS fault_min,
        SUM(CASE WHEN status = 'SETUP'   THEN duration_min ELSE 0 END) AS setup_min,
        SUM(duration_min) AS total_min
    FROM mes_equip_status
    WHERE shift_date >= '2026-08-01' AND shift_date < '2026-09-01'
    GROUP BY equip_code, CAST(shift_date AS DATE)
),
prod AS (
    SELECT
        equip_code,
        CAST(report_time AS DATE) AS stat_date,
        SUM(good_qty)   AS good_qty,
        SUM(defect_qty) AS defect_qty,
        COUNT(DISTINCT wo_no) AS wo_count
    FROM mes_report
    WHERE report_time >= '2026-08-01' AND report_time < '2026-09-01'
    GROUP BY equip_code, CAST(report_time AS DATE)
)
SELECT
    t.equip_code,
    t.stat_date,
    -- 时间开动率 = 运行时间 / 负荷时间（总时间 - 计划停机）
    ROUND(t.running_min * 1.0 / NULLIF(t.total_min - t.idle_min, 0) * 100, 1) AS availability_pct,
    -- 性能开动率 = 理论加工时间 / 实际运行时间
    ROUND(p.good_qty * 1.0 / NULLIF(t.running_min, 0) * 100, 1) AS performance_pct,
    -- 合格品率
    ROUND(p.good_qty * 1.0 / NULLIF(p.good_qty + p.defect_qty, 0) * 100, 2) AS quality_pct
FROM equip_time t
LEFT JOIN prod p
    ON t.equip_code = p.equip_code AND t.stat_date = p.stat_date
ORDER BY t.equip_code, t.stat_date;

WITH wo_cycle AS (
    SELECT
        wo_no,
        product_code,
        line_code,
        plan_qty,
        actual_start,
        actual_end,
        DATEDIFF(MINUTE, actual_start, actual_end) AS cycle_min,
        DATEDIFF(MINUTE, plan_start, actual_end) AS lead_min,
        CASE WHEN actual_end > plan_end THEN 1 ELSE 0 END AS is_delay
    FROM mes_work_order
    WHERE status = 'CLOSED'
      AND actual_end >= '2026-01-01'
)
SELECT
    product_code,
    COUNT(*) AS wo_count,
    AVG(cycle_min) AS avg_cycle_min,
    MAX(cycle_min) AS max_cycle_min,
    -- 中位数（SQL Server 用 PERCENTILE_CONT）
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_min)
        OVER (PARTITION BY product_code) AS median_cycle_min,
    ROUND(AVG(cycle_min * 1.0 / NULLIF(plan_qty, 0)), 2) AS min_per_unit,
    SUM(is_delay) AS delay_count,
    ROUND(SUM(is_delay) * 100.0 / COUNT(*), 1) AS delay_rate_pct
FROM wo_cycle
GROUP BY product_code, cycle_min
ORDER BY wo_count DESC;

WITH op_stat AS (
    SELECT
        operation_seq,
        operation_name,
        wc_code,
        COUNT(DISTINCT wo_no) AS wo_count,
        AVG(DATEDIFF(SECOND, start_time, end_time)) AS avg_process_sec,
        MAX(DATEDIFF(SECOND, start_time, end_time)) AS max_process_sec,
        STDEV(DATEDIFF(SECOND, start_time, end_time)) AS std_process_sec
    FROM mes_operation_record
    WHERE start_time >= DATEADD(DAY, -30, GETDATE())
    GROUP BY operation_seq, operation_name, wc_code
),
op_wait AS (
    SELECT
        operation_seq,
        AVG(wait_before_sec) AS avg_wait_sec
    FROM (
        SELECT
            operation_seq,
            DATEDIFF(SECOND,
                LAG(end_time) OVER (PARTITION BY wo_no ORDER BY operation_seq),
                start_time) AS wait_before_sec
        FROM mes_operation_record
    ) t
    WHERE wait_before_sec IS NOT NULL AND wait_before_sec >= 0
    GROUP BY operation_seq
)
SELECT
    o.operation_seq,
    o.operation_name,
    o.wc_code,
    ROUND(o.avg_process_sec, 1) AS avg_process_sec,
    ROUND(w.avg_wait_sec, 1) AS avg_wait_sec,
    ROUND(o.avg_process_sec * 1.0 /
          NULLIF(o.avg_process_sec + ISNULL(w.avg_wait_sec,0), 0) * 100, 1) AS value_add_pct,
    ROUND(o.std_process_sec, 1) AS std_process_sec
FROM op_stat o
LEFT JOIN op_wait w ON o.operation_seq = w.operation_seq
ORDER BY o.avg_process_sec DESC;

WITH defect_agg AS (
    SELECT
        d.defect_code,
        ISNULL(dc.defect_name, d.defect_code) AS defect_name,
        SUM(d.defect_qty) AS total_qty
    FROM qms_defect d
    LEFT JOIN md_defect_code dc ON d.defect_code = dc.defect_code
    WHERE d.create_time >= DATEADD(MONTH, -3, GETDATE())
    GROUP BY d.defect_code, dc.defect_name
),
with_pareto AS (
    SELECT
        defect_code,
        defect_name,
        total_qty,
        SUM(total_qty) OVER (ORDER BY total_qty DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_qty,
        SUM(total_qty) OVER () AS grand_total
    FROM defect_agg
)
SELECT
    defect_code,
    defect_name,
    total_qty,
    ROUND(total_qty * 100.0 / grand_total, 2) AS pct,
    ROUND(cum_qty * 100.0 / grand_total, 2) AS cum_pct,
    CASE WHEN cum_qty * 1.0 / grand_total <= 0.8 THEN '★ 关键少数' ELSE '' END AS vital_few
FROM with_pareto
ORDER BY total_qty DESC;

SELECT
    ISNULL(reason_category, '未分类') AS reason_category,
    COUNT(*) AS downtime_count,
    SUM(duration_min) AS total_downtime_min,
    ROUND(AVG(duration_min), 1) AS avg_downtime_min,
    MAX(duration_min) AS max_downtime_min,
    ROUND(SUM(duration_min) * 100.0 /
          SUM(SUM(duration_min)) OVER (), 1) AS pct_of_total,
    -- MTBF = 总运行时间 / 故障次数
    -- MTTR = 总故障时间 / 故障次数
    ROUND(AVG(duration_min), 1) AS mttr_min
FROM mes_downtime
WHERE start_time >= DATEADD(MONTH, -6, GETDATE())
GROUP BY reason_category
ORDER BY total_downtime_min DESC;

WITH wo_status AS (
    SELECT
        wo_no,
        product_code,
        line_code,
        plan_qty,
        SUM(good_qty) AS completed_qty,
        MAX(report_time) AS last_report_time
    FROM mes_report
    GROUP BY wo_no, product_code, line_code, plan_qty
)
SELECT
    w.line_code,
    COUNT(*) AS wip_orders,
    SUM(w.plan_qty - ISNULL(s.completed_qty, 0)) AS wip_qty,
    AVG(DATEDIFF(HOUR, s.last_report_time, GETDATE())) AS avg_stale_hours,
    SUM(CASE WHEN DATEDIFF(DAY, s.last_report_time, GETDATE()) > 3
             THEN 1 ELSE 0 END) AS stale_over_3d
FROM mes_work_order w
LEFT JOIN wo_status s ON w.wo_no = s.wo_no
WHERE w.status = 'RELEASED'   -- 已下达未关闭
GROUP BY w.line_code
ORDER BY wip_qty DESC;

WITH daily_capacity AS (
    SELECT
        line_code,
        CAST(shift_date AS DATE) AS stat_date,
        SUM(planned_work_min) AS planned_min,   -- 计划工作分钟（含加班）
        SUM(actual_work_min)  AS actual_min     -- 实际出勤分钟
    FROM mes_attendance
    WHERE shift_date >= DATEADD(MONTH, -1, GETDATE())
    GROUP BY line_code, CAST(shift_date AS DATE)
),
daily_output AS (
    SELECT
        line_code,
        CAST(report_time AS DATE) AS stat_date,
        SUM(good_qty) AS output_qty
    FROM mes_report
    WHERE report_time >= DATEADD(MONTH, -1, GETDATE())
    GROUP BY line_code, CAST(report_time AS DATE)
)
SELECT
    c.line_code,
    SUM(o.output_qty) AS total_output,
    -- 需要标准工时，从工艺路线表取
    ROUND(SUM(o.output_qty * r.std_time_sec) / 60.0, 0) AS earned_min,  -- 挣值工时
    SUM(c.actual_min) AS actual_work_min,
    ROUND(SUM(o.output_qty * r.std_time_sec) / 60.0 * 100.0
          / NULLIF(SUM(c.actual_min), 0), 1) AS labor_efficiency_pct
FROM daily_capacity c
LEFT JOIN daily_output o
    ON c.line_code = o.line_code AND c.stat_date = o.stat_date
LEFT JOIN (
    SELECT product_code, AVG(std_time_sec) AS std_time_sec
    FROM md_routing GROUP BY product_code
) r ON r.product_code = c.product_code
GROUP BY c.line_code;

-- 正向追溯：给定批次号，找出用这个批次料的所有成品工单
WITH RECURSIVE_CTE AS (
    SELECT
        wo_no AS root_wo,
        material_code,
        batch_no,
        wo_no
    FROM mes_material_consume
    WHERE batch_no = 'BATCH20260815'
)
SELECT DISTINCT
    c.root_wo,
    w.product_code,
    w.actual_end,
    s.customer_code
FROM RECURSIVE_CTE c
LEFT JOIN mes_work_order w ON c.wo_no = w.wo_no
LEFT JOIN erp_sales_order s ON w.so_no = s.so_no;

-- 反向追溯：给定成品序列号，找出用了哪些批次的料
SELECT
    w.wo_no,
    w.product_code,
    m.material_code,
    m.batch_no,
    sup.supplier_name,
    m.consume_time
FROM mes_work_order w
LEFT JOIN mes_material_consume m ON w.wo_no = m.wo_no
LEFT JOIN md_supplier sup ON m.supplier_code = sup.supplier_code
WHERE w.serial_no = 'SN20260815000123';

WITH monthly AS (
    SELECT
        YEAR(report_time) AS yr,
        MONTH(report_time) AS mo,
        line_code,
        SUM(good_qty) AS output_qty,
        SUM(defect_qty) AS defect_qty
    FROM mes_report
    WHERE report_time >= DATEADD(MONTH, -13, GETDATE())
    GROUP BY YEAR(report_time), MONTH(report_time), line_code
),
with_lag AS (
    SELECT
        yr, mo, line_code, output_qty, defect_qty,
        LAG(output_qty) OVER (PARTITION BY line_code ORDER BY yr, mo) AS prev_month_qty,
        LAG(output_qty, 12) OVER (PARTITION BY line_code ORDER BY yr, mo) AS last_year_qty
    FROM monthly
)
SELECT
    yr, mo, line_code, output_qty,
    ROUND((output_qty - prev_month_qty) * 100.0 / NULLIF(prev_month_qty, 0), 1) AS mom_pct,
    ROUND((output_qty - last_year_qty) * 100.0 / NULLIF(last_year_qty, 0), 1) AS yoy_pct,
    ROUND(defect_qty * 100.0 / NULLIF(output_qty + defect_qty, 0), 2) AS defect_rate_pct
FROM with_lag
WHERE yr = YEAR(GETDATE())
ORDER BY line_code, mo;

WITH seq AS (
    SELECT
        equip_code,
        product_code,
        start_time,
        end_time,
        LAG(product_code) OVER (PARTITION BY equip_code ORDER BY start_time) AS prev_product
    FROM mes_operation_record
    WHERE start_time >= DATEADD(MONTH, -3, GETDATE())
),
changeover AS (
    SELECT
        equip_code,
        prev_product,
        product_code,
        DATEDIFF(MINUTE,
            LAG(end_time) OVER (PARTITION BY equip_code ORDER BY start_time),
            start_time) AS changeover_min
    FROM seq
    WHERE prev_product IS NOT NULL
      AND prev_product <> product_code
)
SELECT
    equip_code,
    COUNT(*) AS changeover_count,
    ROUND(AVG(changeover_min * 1.0), 1) AS avg_changeover_min,
    MAX(changeover_min) AS max_changeover_min,
    SUM(changeover_min) AS total_changeover_min,
    -- 换型矩阵：哪两个产品之间换型最耗时
    TOP_1 = NULL
FROM changeover
GROUP BY equip_code
ORDER BY total_changeover_min DESC;

SELECT
    e.emp_no,
    e.emp_name,
    e.skill_level,
    COUNT(DISTINCT r.wo_no) AS wo_count,
    SUM(r.good_qty) AS total_output,
    SUM(DATEDIFF(MINUTE, r.start_time, r.end_time)) AS work_min,
    ROUND(SUM(r.good_qty * rt.std_time_sec) / 60.0 * 100.0
          / NULLIF(SUM(DATEDIFF(MINUTE, r.start_time, r.end_time)), 0), 1) AS efficiency_pct,
    -- 多技能：能操作几个工作中心
    COUNT(DISTINCT r.wc_code) AS wc_variety
FROM mes_report r
LEFT JOIN md_employee e ON r.emp_no = e.emp_no
LEFT JOIN md_routing rt ON r.product_code = rt.product_code AND r.operation_seq = rt.operation_seq
WHERE r.report_time >= DATEADD(MONTH, -3, GETDATE())
GROUP BY e.emp_no, e.emp_name, e.skill_level
ORDER BY efficiency_pct DESC;

SELECT
    m.material_code,
    m.material_name,
    SUM(i.on_hand_qty) AS on_hand_qty,
    SUM(i.on_hand_qty * i.std_price) AS on_hand_value,
    MAX(t.last_txn_date) AS last_move_date,
    DATEDIFF(DAY, MAX(t.last_txn_date), GETDATE()) AS idle_days,
    CASE
        WHEN DATEDIFF(DAY, MAX(t.last_txn_date), GETDATE()) > 180 THEN '呆滞(>180天)'
        WHEN DATEDIFF(DAY, MAX(t.last_txn_date), GETDATE()) > 90  THEN '呆滞风险(>90天)'
        ELSE '正常'
    END AS idle_flag
FROM wms_inventory i
LEFT JOIN md_material m ON i.material_code = m.material_code
LEFT JOIN (
    SELECT material_code, MAX(txn_time) AS last_txn_date
    FROM wms_inventory_txn
    GROUP BY material_code
) t ON i.material_code = t.material_code
GROUP BY m.material_code, m.material_name
HAVING SUM(i.on_hand_qty * i.std_price) > 10000
ORDER BY on_hand_value DESC;

-- 错误示例
SELECT
    wo.line_code,
    SUM(wo.plan_qty) AS total_plan_qty   -- 会被重复计算！
FROM mes_work_order wo
LEFT JOIN mes_report rpt ON wo.wo_no = rpt.wo_no
GROUP BY wo.line_code;

-- 解法 A：先聚合后关联（推荐）
WITH rpt_agg AS (
    SELECT wo_no, SUM(good_qty) AS good_qty
    FROM mes_report
    GROUP BY wo_no          -- 先按工单聚合，变成 1:1
)
SELECT wo.line_code, SUM(wo.plan_qty), SUM(r.good_qty)
FROM mes_work_order wo
LEFT JOIN rpt_agg r ON wo.wo_no = r.wo_no
GROUP BY wo.line_code;

-- 解法 B：用 DISTINCT
SELECT wo.line_code, SUM(DISTINCT wo.plan_qty)  -- 不推荐，逻辑不清晰

-- 解法 C：子查询内联聚合
SELECT
    wo.line_code,
    SUM(wo.plan_qty) AS total_plan,
    SUM((SELECT SUM(good_qty) FROM mes_report r WHERE r.wo_no = wo.wo_no)) AS total_good
FROM mes_work_order wo
GROUP BY wo.line_code;

SELECT
    line_code,
    ISNULL(SUM(good_qty), 0) AS total_good,          -- SQL Server
    COALESCE(SUM(good_qty), 0) AS total_good2,       -- 通用（推荐）
    ROUND(COALESCE(SUM(good_qty),0) * 100.0 /
          NULLIF(COALESCE(SUM(good_qty),0) + COALESCE(SUM(defect_qty),0), 0), 2) AS yield_pct
FROM mes_report
GROUP BY line_code;

-- 错误：BETWEEN 包含边界，可能重复计数
WHERE report_time BETWEEN '2026-08-01' AND '2026-08-31'
-- 如果有 8月31日 23:59:59.997 的数据，BETWEEN '2026-08-31' 实际是 00:00:00，会漏掉

-- 正确：左闭右开
WHERE report_time >= '2026-08-01'
  AND report_time <  '2026-09-01'

-- 想要"每月产量"，但忘了 GROUP BY 月份
SELECT SUM(good_qty) FROM mes_report WHERE ...  -- 只有一行总数

-- 想要"每条线每月产量"，但只 GROUP BY 了产线
SELECT line_code, SUM(good_qty)
FROM mes_report GROUP BY line_code  -- 跨月混合了

-- 慢：对列做函数，索引失效
WHERE YEAR(report_time) = 2026 AND MONTH(report_time) = 8

-- 快：对常量做运算，能用索引
WHERE report_time >= '2026-08-01' AND report_time < '2026-09-01'

CREATE VIEW v_line_daily_oee AS
SELECT ...   -- 前面模板 1 的查询
;

-- 之后可以直接查
SELECT * FROM v_line_daily_oee
WHERE stat_date >= '2026-08-01';

Power BI
  ├─ 数据源：SQL Server / MySQL / PostgreSQL（直连）
  ├─ 查询方式：
  │    ├─ 直接选表（简单，但会拉全表，慢）
  │    └─ 自定义 SQL 查询（推荐，只取需要的粒度）
  ├─ 刷新：网关（Gateway）定时刷新（如每小时）
  └─ 建模：在 Power BI 里建表关系、写 DAX 度量值

import pandas as pd
import pyodbc

conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=192.168.1.100;'
    'DATABASE=MES;'
    'UID=readonly_user;PWD=xxx'
)

sql = """
SELECT line_code, stat_date, output_qty, defect_qty
FROM v_line_daily_oee
WHERE stat_date >= '2026-01-01'
"""
df = pd.read_sql(sql, conn)
# 后续用 pandas 做分析