sql同一个表多条件查询

如图，zyh为每个病号的唯一标识，这个表存储的是病号的诊断信息，zdlb=3为主诊断，zdlb=4且zdxh=2为第二诊断，icdm是诊断编码，现在我想查询同一个zyh的前提下，主诊断的icdm是I20且第二次诊断为I50的数据，应该怎么写语句呢

有帮助的话 采纳一下

SELECT *
FROM diagnosis_table t1
INNER JOIN diagnosis_table t2 ON t1.zyh = t2.zyh
WHERE t1.zdlb = 3 AND t1.icdm = 'I20'
AND t2.zdlb = 4 AND t2.zdxh = 2 AND t2.icdm = 'I50'

你的表明是A的话：

select a.zyh, a.icdm as '主诊断', b.icdm as '第二诊断'
from A a 
join A b 
on a.zyh = b.zyh 
where a.zdlb = 3 and a.icdm = 'I20' and b.zdlb = 4 and b.zdxh = 2 and b.icdm = 'I50'

就是用join关键字把一张表中的别名a跟b连一起，where就是过滤信息

SELECT zyh FROM 表
WHERE zdlb = 3 AND icdm = 'I20'
GROUP BY zyh
HAVING COUNT(*) = 1
AND zyh IN (
    SELECT zyh FROM 表
    WHERE zdlb = 4 AND zdxh = 2 AND icdm = 'I50'
    GROUP BY zyh
    HAVING COUNT(*) = 1
)

• 以下回答由chatgpt基于相关博客总结生成:

  根据提供的参考资料，问题没有提供具体的需求或问题描述。所以无法给出具体的解决方案。

这个你给的信息很全面，我写一个伪代码你参考下吧，

SELECT *
FROM 表名
WHERE zyh IN (
  SELECT zyh
  FROM 表名
  WHERE zdlb = 3
    AND icdm = 'I20'
) AND zyh IN (
  SELECT zyh
  FROM 表名
  WHERE zdlb = 4
    AND zdxh = 2
    AND icdm = 'I50'
)

如果运行有问题，联系我修改

分两步操作：
1、第一步：查询出 icdm 是 120 的 zyh
2、第二步：再使用第一步查询出的 zyh ，再去表里面查出 icdm 是 150 的数据

SELECT d1.zyh
FROM diagnosis d1
JOIN diagnosis d2 ON d1.zyh = d2.zyh
WHERE d1.zdlb = 3 AND d1.icdm = 'I20'
  AND d2.zdlb = 4 AND d2.zdxh = 2 AND d2.icdm = 'I50';