SQL Server 2016存储PubMed PMID数据的报表查询优化咨询

绝对应该把每个PMID和对应的邮箱拆分成单独的关联表——这是解决你查询痛点、符合数据库设计最佳实践的最优方案，比继续用逗号分隔字符串靠谱太多了。下面给你详细拆解为什么这么做、具体怎么落地：

• 查询性能拉胯：要统计每个PMID关联多少成员，或者拆分出单个PMID匹配，每次都得做字符串拆分操作。SQL Server处理这种操作不仅慢，还没法利用索引优化，等数据量涨到10万条（1000人×100篇）之后，查询速度会直线下降。
• 数据风险高：逗号分隔的字符串很容易出现格式错误，比如多打逗号、不小心加了空格，导致PMID识别混乱，后期校验和维护都麻烦。
• 扩展性差：以后如果要给PMID加更多属性（比如文献标题、发表日期），这种存储方式根本没法扩展，只能硬改字符串格式，完全不符合数据库设计逻辑。

这是典型的多对多关系（一个成员对应多篇文献，一篇文献可能属于多个成员），正确的设计是用两张表：

1. 成员信息表（存储唯一成员数据）

CREATE TABLE [dbo].[Members] (
    MemberID INT IDENTITY(1,1) PRIMARY KEY,
    Organization NVARCHAR(255) NOT NULL,
    Email NVARCHAR(255) NOT NULL UNIQUE -- 用邮箱做唯一标识，避免重复存储成员信息
);

2. 成员-文献关联表（存储PMID和成员的对应关系）

CREATE TABLE [dbo].[MemberPublications] (
    MemberPublicationID INT IDENTITY(1,1) PRIMARY KEY,
    MemberID INT NOT NULL FOREIGN KEY REFERENCES [dbo].[Members](MemberID),
    PMID VARCHAR(20) NOT NULL, -- PMID是6-7位字符串，用VARCHAR足够
    CONSTRAINT UQ_MemberPMID UNIQUE (MemberID, PMID) -- 防止同一个成员重复关联同一篇文献
);

利用SQL Server 2016支持的STRING_SPLIT函数，快速把原表的逗号分隔PMID拆成单独行：

1. 先导入成员唯一数据：

INSERT INTO [dbo].[Members] (Organization, Email)
SELECT DISTINCT Organization, Email
FROM [dbo].[ADMIN_Publications];

2. 再拆分PMID导入关联表：

INSERT INTO [dbo].[MemberPublications] (MemberID, PMID)
SELECT 
    m.MemberID,
    TRIM(s.value) AS PMID -- 去除PMID可能带的空格
FROM [dbo].[ADMIN_Publications] ap
JOIN [dbo].[Members] m ON ap.Email = m.Email
CROSS APPLY STRING_SPLIT(ap.PMID, ',') s
WHERE TRIM(s.value) <> ''; -- 过滤空的无效PMID

现在要按PMID关联的成员数降序展示，用STRING_AGG函数把邮箱拼接成逗号分隔字符串，再和PMID组合，直接得到你想要的格式：

SELECT 
    CONCAT(STRING_AGG(m.Email, ', '), ' ', mp.PMID) AS DisplayResult
FROM [dbo].[MemberPublications] mp
JOIN [dbo].[Members] m ON mp.MemberID = m.MemberID
GROUP BY mp.PMID
ORDER BY COUNT(m.MemberID) DESC;

输出示例：

a@med.edu, c@med.edu, k@med.edu 20411915
j@med.edu, c@med.edu, g@med.edu 29092960

• 给关联表加索引：为了加速查询，给MemberPublications表建索引，让统计和匹配更快：

CREATE INDEX IX_MemberPublications_PMID ON [dbo].[MemberPublications](PMID) INCLUDE(MemberID);

• 修改存储过程：以后插入数据时，直接往Members和MemberPublications表插入，不要再用逗号分隔的方式。如果是批量导入PMID，可以用表值参数批量处理，效率更高。

如果暂时不想重构表结构，也可以直接查询原表，但这种方案只适合极小数据量（比如几十条记录），数据量一大就会慢到离谱，还容易出错。比如查询语句：

SELECT 
    CONCAT(STRING_AGG(ap.Email, ', '), ' ', TRIM(s.value)) AS DisplayResult
FROM [dbo].[ADMIN_Publications] ap
CROSS APPLY STRING_SPLIT(ap.PMID, ',') s
WHERE TRIM(s.value) <> ''
GROUP BY TRIM(s.value)
ORDER BY COUNT(ap.Email) DESC;

但这个语句每次都要拆分所有字符串，而且如果同一个PMID在不同记录里格式不一致（比如带空格），会被当成不同的PMID，统计结果不准，所以非常不推荐。

总的来说，拆分表是长期来看最合理、最高效的方案，不仅解决当前的查询困难，还能让数据维护更省心。

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