SQL Server从两个关联表查询数据并以数组形式返回的实现方法

Got it, let's work through this. You've got a one-to-many relationship between tableA (primary key ServiceOrderId) and tableB (foreign key ServiceOrderId), and you need to query the data and return it in an array format. Here's how to approach this:

First, you'll need to join the two tables to pull related data. Use LEFT JOIN if you want to include all tableA records even if there are no matching tableB entries; use INNER JOIN if you only want tableA records that have corresponding tableB data.

-- Use LEFT JOIN to retain all tableA records
SELECT 
    a.ServiceOrderId,
    a.Tax,
    a.Total,
    a.OrderNumber,
    -- Replace with actual tableB column names instead of * for better performance
    b.id, b.your_field_1, b.your_field_2
FROM 
    tableA a
LEFT JOIN 
    tableB b ON a.ServiceOrderId = b.ServiceOrderId;

The exact code depends on your backend language, but here are common implementations for nested and flat array structures:

Example 1: Python (with psycopg2 for PostgreSQL) #

This structures data into a nested array where each entry contains tableA data and a list of its related tableB records:

import psycopg2
from collections import defaultdict

# Connect to your database (adjust credentials for your DB)
conn = psycopg2.connect(
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host"
)
cur = conn.cursor()

# Execute the join query
cur.execute("""
    SELECT 
        a.ServiceOrderId, a.Tax, a.Total, a.OrderNumber,
        b.id, b.your_field_1, b.your_field_2
    FROM tableA a
    LEFT JOIN tableB b ON a.ServiceOrderId = b.ServiceOrderId
""")

# Organize results into nested array structure
order_map = defaultdict(lambda: {
    "service_order": [],
    "related_records": []
})

for row in cur.fetchall():
    so_id, tax, total, order_num, b_id, b_field1, b_field2 = row
    # Initialize service order data once per ServiceOrderId
    if not order_map[so_id]["service_order"]:
        order_map[so_id]["service_order"] = [so_id, tax, total, order_num]
    # Add related tableB record if it exists
    if b_id is not None:
        order_map[so_id]["related_records"].append([b_id, b_field1, b_field2])

# Convert to final array list
final_array = list(order_map.values())

# Cleanup connections
cur.close()
conn.close()

print(final_array)

Example 2: Java (JDBC for MySQL) #

This achieves the same nested array structure using standard JDBC:

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OrderDataFetcher {
    public static void main(String[] args) {
        String dbUrl = "jdbc:mysql://your_host:3306/your_database";
        String dbUser = "your_username";
        String dbPass = "your_password";

        try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("""
                 SELECT 
                     a.ServiceOrderId, a.Tax, a.Total, a.OrderNumber,
                     b.id, b.your_field_1, b.your_field_2
                 FROM tableA a
                 LEFT JOIN tableB b ON a.ServiceOrderId = b.ServiceOrderId
             """)) {

            Map<Integer, Map<String, Object>> orderDataMap = new HashMap<>();

            while (rs.next()) {
                int serviceOrderId = rs.getInt("ServiceOrderId");
                double tax = rs.getDouble("Tax");
                int total = rs.getInt("Total");
                String orderNumber = rs.getString("OrderNumber");

                // Set up entry for this ServiceOrderId if it doesn't exist
                if (!orderDataMap.containsKey(serviceOrderId)) {
                    Map<String, Object> orderEntry = new HashMap<>();
                    List<Object> serviceOrderDetails = new ArrayList<>();
                    serviceOrderDetails.add(serviceOrderId);
                    serviceOrderDetails.add(tax);
                    serviceOrderDetails.add(total);
                    serviceOrderDetails.add(orderNumber);
                    orderEntry.put("service_order", serviceOrderDetails);
                    orderEntry.put("related_records", new ArrayList<List<Object>>());
                    orderDataMap.put(serviceOrderId, orderEntry);
                }

                // Add tableB record if present
                if (rs.getObject("id") != null) {
                    List<Object> tableBRecord = new ArrayList<>();
                    tableBRecord.add(rs.getInt("id"));
                    tableBRecord.add(rs.getString("your_field_1"));
                    tableBRecord.add(rs.getDouble("your_field_2"));
                    ((List<List<Object>>) orderDataMap.get(serviceOrderId).get("related_records")).add(tableBRecord);
                }
            }

            // Convert to final array list
            List<Map<String, Object>> finalArray = new ArrayList<>(orderDataMap.values());
            System.out.println(finalArray);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

If you don't need a nested structure and just want each joined row as an array element, it's simpler:

• In Python, cur.fetchall() returns a list of tuples (already array-like)
• In Java, loop through the ResultSet and add each row's values to a list of lists

• Replace your_field_1, your_field_2, etc. with the actual column names from tableB
• Adjust database connection code to match your DB type (MySQL, SQL Server, etc.)
• For ORM users: Tools like SQLAlchemy (Python) or Hibernate (Java) can auto-map the one-to-many relationship to objects, which you can easily convert to arrays.

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