数据库表及外键关联数据展示求助：多条形码条目显示问题

Hey there! Let's break down how to get your item information formatted exactly the way you want. Here's a step-by-step approach combining SQL querying and application-level formatting:

1. 编写关联查询的SQL语句 #

First, we need to join your three tables to pull together all related data. Since one item can have multiple barcodes, we'll use database string aggregation functions to combine all barcodes (with their added dates) for a single item into one string—this avoids returning duplicate rows for the same item.

MySQL 版本 #

SELECT 
    i.title,
    i.edition,
    i.subject,
    d.donorName,
    d.donorDepartment,
    GROUP_CONCAT(CONCAT(b.barcodeNumber, ', Added on: ', b.dateAdded) SEPARATOR '; ') AS barcodes
FROM Items i
JOIN Barcodes b ON i.itemNumber = b.itemNumber
JOIN Donors d ON b.donorNumber = d.donorNumber
GROUP BY i.itemNumber, i.title, i.edition, i.subject, d.donorName, d.donorDepartment;

PostgreSQL/SQL Server 版本 #

PostgreSQL and SQL Server use STRING_AGG instead of GROUP_CONCAT:

-- PostgreSQL
SELECT 
    i.title,
    i.edition,
    i.subject,
    d.donorName,
    d.donorDepartment,
    STRING_AGG(CONCAT(b.barcodeNumber, ', Added on: ', b.dateAdded), '; ') AS barcodes
FROM Items i
JOIN Barcodes b ON i.itemNumber = b.itemNumber
JOIN Donors d ON b.donorNumber = d.donorNumber
GROUP BY i.itemNumber, i.title, i.edition, i.subject, d.donorName, d.donorDepartment;

-- SQL Server
SELECT 
    i.title,
    i.edition,
    i.subject,
    d.donorName,
    d.donorDepartment,
    STRING_AGG(CONCAT(b.barcodeNumber, ', Added on: ', b.dateAdded), '; ') WITHIN GROUP (ORDER BY b.dateAdded) AS barcodes
FROM Items i
JOIN Barcodes b ON i.itemNumber = b.itemNumber
JOIN Donors d ON b.donorNumber = d.donorNumber
GROUP BY i.itemNumber, i.title, i.edition, i.subject, d.donorName, d.donorDepartment;

Quick note: If one item has barcodes from different donors, the query above will group barcodes by donor (since we're grouping on donor fields). If you want to combine all barcodes for an item regardless of donor, you'll need to adjust the GROUP BY and aggregate donor info—but this might lead to duplicate donor details, so tweak it based on your actual business needs.

2. 在应用层格式化输出结果 #

Once you have the SQL results, you can use any programming language to format and print the data exactly as you want. Here's a Python example using MySQL as the database:

import mysql.connector

# Set up database connection
db_config = {
    'host': 'your_db_host',
    'user': 'your_db_user',
    'password': 'your_db_password',
    'database': 'your_db_name'
}
conn = mysql.connector.connect(**db_config)
cursor = conn.cursor(dictionary=True)

# Execute the SQL query
sql_query = """
SELECT 
    i.title,
    i.edition,
    i.subject,
    d.donorName,
    d.donorDepartment,
    GROUP_CONCAT(CONCAT(b.barcodeNumber, ', Added on: ', b.dateAdded) SEPARATOR '; ') AS barcodes
FROM Items i
JOIN Barcodes b ON i.itemNumber = b.itemNumber
JOIN Donors d ON b.donorNumber = d.donorNumber
GROUP BY i.itemNumber, i.title, i.edition, i.subject, d.donorName, d.donorDepartment;
"""
cursor.execute(sql_query)

# Print results in your desired format
for item in cursor.fetchall():
    print(f"Title: {item['title']}")
    print(f"Edition: {item['edition']}")
    print(f"Subject: {item['subject']}")
    print(f"Donor Name: {item['donorName']}")
    print(f"Donor Department: {item['donorDepartment']}")
    print(f"Barcodes: {item['barcodes']}\n")

# Clean up connections
cursor.close()
conn.close()

This will output exactly the format you requested, like:

Title: 条目标题
Edition: 2nd Edition
Subject: 计算机编程
Donor Name: John Doe
Donor Department: Technology
Barcodes: 001, Added on: 04/27/2024; 002, Added on: 04/28/2024

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