owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/04平台项目管理.sql

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
DROP TABLE IF EXISTS report_project_list;

CREATE TABLE report_project_list AS

/* 项目列表视图 */

DROP VIEW IF EXISTS manage_project_list;

CREATE OR REPLACE VIEW manage_project_list AS
WITH
    order_stats AS (
        /* 统计每个项目的订单数量 */
        SELECT service_id /* 项目编号 */, COUNT(id) AS count /* 订单数量 */
        FROM `order` /* 使用反引号包裹order表名 */
        WHERE
            deleted_at IS NULL
        GROUP BY
            service_id
    )
SELECT
    p.id as project_id /* 项目编号 */,
    p.title as project_title /* 项目名称 */,
    pc.name as category_name /* 项目分类名称 */,
    p.price as project_price /* 项目价格 */,
    p.duration as service_duration /* 服务时长(分钟) */,
    COALESCE(os.count, 0) as order_count /* 下单数量 */,
    p.state as project_state /* 状态 */,
    p.cover as project_cover /* 项目封面图片 */
FROM
    project p
    LEFT JOIN project_cate pc ON p.cate_id = pc.id
    AND pc.deleted_at IS NULL /* 关联项目分类 */
    LEFT JOIN order_stats os ON p.id = os.service_id /* 关联订单统计 */
WHERE
    p.deleted_at IS NULL
ORDER BY p.id DESC;