EF Core实现：按父ID从子表随机获取指定数量数据

1. SQL 查询实现 #

核心思路是通过窗口函数按ColorId分组，随机排序后取每组前3条数据，以下是不同数据库的实现语句：

MySQL/MariaDB #

SELECT t.Id, t.ColorId, t.Name
FROM (
    SELECT 
        Id, ColorId, Name,
        ROW_NUMBER() OVER (PARTITION BY ColorId ORDER BY RAND()) AS rn
    FROM 子表名
) t
WHERE t.rn <= 3;

PostgreSQL #

SELECT t.Id, t.ColorId, t.Name
FROM (
    SELECT 
        Id, ColorId, Name,
        ROW_NUMBER() OVER (PARTITION BY ColorId ORDER BY RANDOM()) AS rn
    FROM 子表名
) t
WHERE t.rn <= 3;

SQL Server #

SELECT t.Id, t.ColorId, t.Name
FROM (
    SELECT 
        Id, ColorId, Name,
        ROW_NUMBER() OVER (PARTITION BY ColorId ORDER BY NEWID()) AS rn
    FROM 子表名
) t
WHERE t.rn <= 3;

如果需要关联父表获取颜色名称，可添加JOIN（以MySQL为例）：

SELECT p.Name AS ColorName, t.Id, t.ColorId, t.Name AS ItemName
FROM (
    SELECT 
        Id, ColorId, Name,
        ROW_NUMBER() OVER (PARTITION BY ColorId ORDER BY RAND()) AS rn
    FROM 子表名
) t
LEFT JOIN 父表名 p ON t.ColorId = p.Id
WHERE t.rn <= 3
ORDER BY p.Name;

2. 后端处理示例（Python Flask） #

以下是通过Flask连接数据库，查询并整理数据的示例：

from flask import Flask, jsonify
import mysql.connector

app = Flask(__name__)

@app.route('/random-items')
def get_random_items():
    db = mysql.connector.connect(
        host="localhost",
        user="你的数据库用户名",
        password="你的数据库密码",
        database="你的数据库名"
    )
    cursor = db.cursor(dictionary=True)
    query = """
        SELECT p.Name AS ColorName, t.Id, t.ColorId, t.Name AS ItemName
        FROM (
            SELECT 
                Id, ColorId, Name,
                ROW_NUMBER() OVER (PARTITION BY ColorId ORDER BY RAND()) AS rn
            FROM 子表名
        ) t
        LEFT JOIN 父表名 p ON t.ColorId = p.Id
        WHERE t.rn <= 3
        ORDER BY p.Name;
    """
    cursor.execute(query)
    results = cursor.fetchall()
    
    # 按颜色分组整理返回数据
    grouped_data = {}
    for item in results:
        color = item['ColorName']
        if color not in grouped_data:
            grouped_data[color] = []
        grouped_data[color].append({
            'item_id': item['Id'],
            'item_name': item['ItemName']
        })
    
    cursor.close()
    db.close()
    return jsonify(grouped_data)

if __name__ == '__main__':
    app.run(debug=True)

3. 前端展示示例（HTML + JavaScript） #

通过AJAX获取后端数据，渲染到页面上：

<!DOCTYPE html>
<html>
<head>
    <title>随机颜色条目展示</title>
    <style>
        .color-group {
            margin: 20px;
            padding: 15px;
            border: 1px solid #eee;
            border-radius: 6px;
        }
        .color-group h3 {
            margin-top: 0;
            color: #333;
        }
        .item-list {
            list-style-type: none;
            padding-left: 0;
        }
        .item-list li {
            padding: 5px 0;
            color: #666;
        }
    </style>
</head>
<body>
    <div id="color-container"></div>

    <script>
        fetch('/random-items')
            .then(res => res.json())
            .then(data => {
                const container = document.getElementById('color-container');
                Object.keys(data).forEach(color => {
                    const group = document.createElement('div');
                    group.className = 'color-group';
                    group.innerHTML = `
                        <h3>${color}</h3>
                        <ul class="item-list">
                            ${data[color].map(item => `<li>${item.item_name} (ID: ${item.item_id})</li>`).join('')}
                        </ul>
                    `;
                    container.appendChild(group);
                });
            })
            .catch(err => console.error('加载数据出错:', err));
    </script>
</body>
</html>

内容的提问来源于stack exchange，提问作者RoyalDesigner