如何实现样本与多测试的数据库关联存储并生成指定结果？

一、正确的表结构设计 #

样本与测试是多对多关系（一个样本可对应多个测试，一个测试可被多个样本使用），无法通过单列存储多个外键实现关联，需新增中间关联表来维护两者的对应关系：

• samples表（保留核心字段，移除原TESTS列）

  ID	NAME
  0	S_0
  1	S_1
  2	S_2

  主键：ID

• tests表（结构不变）

  TESTID	TEST
  T0	test 0
  T1	test 1
  T2	test 2
  T3	test 3
  T4	test 4
  T5	test 5

  主键：TESTID

• sample_tests关联表（存储样本与测试的关联关系）

  sample_id	test_id
  0	T0
  0	T1
  1	T0
  1	T2
  2	T3
  2	T4
  2	T5

  联合主键：sample_id + test_id（避免重复关联）
  外键：sample_id关联samples.ID，test_id关联tests.TESTID

二、插入数据示例 #

对应以上结构，执行以下SQL语句插入数据：

-- 插入样本数据
INSERT INTO samples (ID, NAME) VALUES
(0, 'S_0'), (1, 'S_1'), (2, 'S_2');

-- 插入测试数据
INSERT INTO tests (TESTID, TEST) VALUES
('T0', 'test 0'), ('T1', 'test 1'), ('T2', 'test 2'),
('T3', 'test 3'), ('T4', 'test 4'), ('T5', 'test 5');

-- 插入关联数据
INSERT INTO sample_tests (sample_id, test_id) VALUES
(0, 'T0'), (0, 'T1'), (1, 'T0'), (1, 'T2'),
(2, 'T3'), (2, 'T4'), (2, 'T5');

三、查询得到目标格式结果 #

通过聚合函数将关联的测试ID拼接为逗号分隔的字符串，不同数据库的实现如下：

MySQL/MariaDB #

SELECT 
    s.ID,
    s.NAME,
    GROUP_CONCAT(st.test_id SEPARATOR ',') AS TESTS
FROM samples s
LEFT JOIN sample_tests st ON s.ID = st.sample_id
GROUP BY s.ID, s.NAME;

PostgreSQL #

SELECT 
    s.ID,
    s.NAME,
    STRING_AGG(st.test_id, ',') AS TESTS
FROM samples s
LEFT JOIN sample_tests st ON s.ID = st.sample_id
GROUP BY s.ID, s.NAME;

SQL Server（2017及以上版本） #

SELECT 
    s.ID,
    s.NAME,
    STRING_AGG(st.test_id, ',') AS TESTS
FROM samples s
LEFT JOIN sample_tests st ON s.ID = st.sample_id
GROUP BY s.ID, s.NAME;

执行上述查询后，即可得到与你示例完全一致的结果：

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