-- 复杂的数据库查询示例 WITH post_stats AS ( SELECT p.id, p.title, p.created_at, COUNT(c.id) as comment_count, AVG(r.rating) as avg_rating, COUNT(DISTINCT v.id) as view_count FROM posts p LEFTJOIN comments c ON p.id = c.post_id LEFTJOIN ratings r ON p.id = r.post_id LEFTJOIN page_views v ON p.id = v.post_id WHERE p.status ='published' AND p.created_at >='2024-01-01' GROUPBY p.id, p.title, p.created_at ), category_ranking AS ( SELECT pc.post_id, c.name as category_name, COUNT(*) OVER (PARTITIONBY c.name) as posts_in_category FROM post_categories pc JOIN categories c ON pc.category_id = c.id ) SELECT ps.title, ps.created_at, ps.comment_count, ps.avg_rating, ps.view_count, cr.category_name, cr.posts_in_category, ROW_NUMBER() OVER (ORDERBY ps.view_count DESC, ps.avg_rating DESC) as popularity_rank FROM post_stats ps LEFTJOIN category_ranking cr ON ps.id = cr.post_id WHERE ps.comment_count >0OR ps.avg_rating ISNOT NULL ORDERBY popularity_rank LIMIT 20;
📈 Mermaid 图表
本博客支持Mermaid图表,可以创建流程图、时序图、甘特图等多种图表。
流程图
{% mermaid %} graph TD A[开始] --> B{编写Markdown} B -->|完成| C[预览效果] B -->|继续编辑| D[修改内容] D --> B C --> E{满意?} E -->|是| F[发布文章] E -->|否| D F --> G[结束] {% endmermaid %}
效果展示:
graph TD
A[开始] --> B{编写Markdown}
B -->|完成| C[预览效果]
B -->|继续编辑| D[修改内容]
D --> B
C --> E{满意?}
E -->|是| F[发布文章]
E -->|否| D
F --> G[结束]
时序图
{% mermaid %} sequenceDiagram participant U as 用户 participant B as 博客系统 participant D as 数据库 participant C as CDN U->>B: 访问文章页面 B->>D: 查询文章内容 D-->>B: 返回Markdown数据 B->>B: 解析Markdown内容 B->>C: 获取静态资源 C-->>B: 返回CSS/JS B-->>U: 渲染完整页面 U->>B: 互动操作(点赞、评论) B->>D: 更新数据库 D-->>B: 确认更新 B-->>U: 更新页面显示 {% endmermaid %}
效果展示:
sequenceDiagram
participant U as 用户
participant B as 博客系统
participant D as 数据库
participant C as CDN
U->>B: 访问文章页面
B->>D: 查询文章内容
D-->>B: 返回Markdown数据
B->>B: 解析Markdown内容
B->>C: 获取静态资源
C-->>B: 返回CSS/JS
B-->>U: 渲染完整页面
U->>B: 互动操作(点赞、评论)
B->>D: 更新数据库
D-->>B: 确认更新
B-->>U: 更新页面显示
类图
{% mermaid %} classDiagram class Post { +String id +String title +String content +Date createdAt +Date updatedAt +Array~String~ tags +String status +save() Boolean +publish() Boolean +archive() Boolean +addTag(String tag) void } class Category { +String id +String name +String description +String slug +Array~Post~ posts +addPost(Post post) void +removePost(Post post) void +getPostCount() Number } class User { +String id +String username +String email +Array~Post~ posts +Date lastLoginAt +createPost(PostData data) Post +editPost(String postId, PostData data) Post +deletePost(String postId) Boolean } User "1" -- "*" Post : creates > Post "*" -- "1" Category : belongs to > {% endmermaid %}
效果展示:
classDiagram
class Post {
+String id
+String title
+String content
+Date createdAt
+Date updatedAt
+Array~String~ tags
+String status
+save() Boolean
+publish() Boolean
+archive() Boolean
+addTag(String tag) void
}
class Category {
+String id
+String name
+String description
+String slug
+Array~Post~ posts
+addPost(Post post) void
+removePost(Post post) void
+getPostCount() Number
}
class User {
+String id
+String username
+String email
+Array~Post~ posts
+Date lastLoginAt
+createPost(PostData data) Post
+editPost(String postId, PostData data) Post
+deletePost(String postId) Boolean
}
User "1" -- "*" Post : creates >
Post "*" -- "1" Category : belongs to >