跨表字段对比插入、HTML/CSS表单及手术表关联查询需求咨询

Alright, let's tackle your three technical needs with concrete, actionable solutions:

To insert differing fields from two tables into a target table, you can use an INSERT ... SELECT statement with join conditions to filter out matching records. Here are two common scenarios:

Scenario 1: Insert unlinked records #

Suppose you want to add patient_id from the patient table that haven't been linked to any surgery in the surgery table into a relationship table (e.g., surgery_patient_link):

INSERT INTO surgery_patient_link (patient_id, surgery_id)
SELECT p.patient_id, NULL  -- Assign a default surgery ID if needed
FROM patient p
LEFT JOIN surgery s ON p.patient_id = s.patient_id
WHERE s.patient_id IS NULL;

Scenario 2: Insert records with field value differences #

If you need to capture rows where specific field values don't match between two tables (e.g., compare table_a.field_x and table_b.field_y):

INSERT INTO target_table (diff_field_a, diff_field_b, common_id)
SELECT a.field_x, b.field_y, a.common_id
FROM table_a a
INNER JOIN table_b b ON a.common_id = b.common_id
WHERE a.field_x != b.field_y;

Here's a responsive form tailored to your surgery table schema, with basic styling for usability:

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>手术信息录入表单</title>
    <style>
        .form-wrapper {
            max-width: 600px;
            margin: 3rem auto;
            padding: 2.5rem;
            background-color: #f8f9fa;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        }
        .form-group {
            margin-bottom: 1.5rem;
        }
        label {
            display: block;
            margin-bottom: 0.6rem;
            font-size: 1.1rem;
            color: #333;
        }
        input[type="number"], select, textarea {
            width: 100%;
            padding: 0.9rem;
            border: 1px solid #ced4da;
            border-radius: 6px;
            font-size: 1rem;
            transition: border-color 0.3s;
        }
        input[type="number"]:focus, select:focus, textarea:focus {
            outline: none;
            border-color: #007bff;
            box-shadow: 0 0 0 0.2rem rgba(0,123,255,0.25);
        }
        textarea {
            resize: vertical;
            min-height: 120px;
        }
        .submit-btn {
            width: 100%;
            padding: 1rem;
            background-color: #007bff;
            color: white;
            border: none;
            border-radius: 6px;
            font-size: 1.1rem;
            cursor: pointer;
            transition: background-color 0.3s;
        }
        .submit-btn:hover {
            background-color: #0056b3;
        }
    </style>
</head>
<body>
    <div class="form-wrapper">
        <h2 style="text-align: center; margin-bottom: 2rem;">手术信息录入</h2>
        <form action="/submit-surgery" method="POST">
            <div class="form-group">
                <label for="doc_id">医生ID:</label>
                <input type="number" id="doc_id" name="doc_id" required>
            </div>
            <div class="form-group">
                <label for="nurse_id">护士ID:</label>
                <input type="number" id="nurse_id" name="nurse_id" required>
            </div>
            <div class="form-group">
                <label for="patient_id">患者ID:</label>
                <input type="number" id="patient_id" name="patient_id" required>
            </div>
            <div class="form-group">
                <label for="surgery_status">手术状态:</label>
                <select id="surgery_status" name="surgery_status" required>
                    <option value="Success">成功</option>
                    <option value="Failed">失败</option>
                </select>
            </div>
            <div class="form-group">
                <label for="description">手术描述:</label>
                <textarea id="description" name="description" placeholder="请输入手术详情..." required></textarea>
            </div>
            <button type="submit" class="submit-btn">提交信息</button>
        </form>
    </div>
</body>
</html>

Pro tip: For better user experience, replace the ID input fields with dropdown menus populated from your doctor, nurse, and patient tables (fetch the options via backend code like PHP/Node.js/Python).

Since your surgery table links to doctor, nurse, and patient tables via ID fields, use JOIN queries to retrieve the associated names. First, ensure each linked table has a name field (e.g., doc_name in doctor, nurse_name in nurse, patient_name in patient).

Query to Get Surgery Data with Associated Names #

SELECT
    s.surgery_id,
    d.doc_name AS 医生姓名,
    n.nurse_name AS 护士姓名,
    p.patient_name AS 患者姓名,
    s.surgery_status AS 手术状态,
    s.description AS 手术描述
FROM surgery s
INNER JOIN doctor d ON s.doc_id = d.doc_id
INNER JOIN nurse n ON s.nurse_id = n.nurse_id
INNER JOIN patient p ON s.patient_id = p.patient_id;

Quick Fix for Your Surgery Table Schema #

I noticed two issues in your original CREATE TABLE statement:

1. A typo: Fialled should be Failed in the CHECK constraint (otherwise data insertion will fail)
2. Missing patient_id column (you mentioned it links to the patient table)

Here's the corrected schema:

CREATE TABLE surgery (
    surgery_id INT AUTO_INCREMENT PRIMARY KEY,
    doc_id INT NOT NULL REFERENCES doctor(doc_id),
    nurse_id INT NOT NULL REFERENCES nurse(nurse_id),
    patient_id INT NOT NULL REFERENCES patient(patient_id),
    surgery_status VARCHAR(8) NOT NULL CHECK (surgery_status IN ('Success', 'Failed')),
    description NVARCHAR(200) NOT NULL
) ENGINE=INNODB CHARSET=UTF8 COLLATE UTF8_BIN;

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