时间戳

首页 > 解决方案 > Mysql查询选择匹配记录加上与匹配记录具有相同cid的记录

mysql - Mysql查询选择匹配记录加上与匹配记录具有相同cid的记录

问题描述

表架构

create table info(id integer, location_id integer, amount varchar(100), cid varchar(100));

INSERT INTO info
  (id, location_id, amount, cid)
VALUES
  ('1', '2', '5000', '1360'),
  ('2', '2', '3000', '2121'),
  ('3', '2', '2000', '1360'),
  ('4', '2', '1000', '2772'),
  ('5', '2', '3500', '1360');

create table users (
  userid integer,
  type varchar(18),
  accnum varchar(8),
  name varchar(28)
);

INSERT INTO users
  (userid, type, accnum, name)
VALUES
  ('1', 'patient', '8769', 'Ron K'),
  ('1', 'guarantor', '444321', 'Jhon W'),
  ('2', 'patient', '76890', 'Frank'),
  ('2', 'guarantor', '55543', 'Joe'),
  ('3', 'patient', '65438', 'Grace'),
  ('3', 'guarantor', '33332', 'bilbo'),
  ('4', 'patient', '89786', 'Sam'),
  ('4', 'guarantor', '432432', 'Susan'),
  ('5', 'patient', '90871', 'Subir'),
  ('5', 'guarantor', '555333', 'Sumit');

"Info" => id 可以根据类型（患者、担保人）在用户表中有两条记录。现在，我需要获取与 accnum 字段匹配的所有记录，还记录那些共享该匹配记录的相同“cid”值的记录。

当前查询::

SELECT
    guarantor.accnum as guarantorId,
    patient.name AS patientName,
    guarantor.name AS guarantorName,
    patient.accnum as patientAccountNumber,
    ob.amount,
    ob.id,
    ob.cid AS cid
    FROM info AS ob
    LEFT JOIN users AS patient ON (patient.userid = ob.id AND patient.type = 'patient')
    LEFT JOIN users AS guarantor ON (guarantor.userid = ob.id AND guarantor.type = 'guarantor')
    WHERE 
    1
    AND (
    patient.name LIKE "%8769%"
    OR patient.accnum LIKE "%8769%" 
    OR ob.cid LIKE "%8769%"
    ) 
    AND ob.location_id IN(2)

当前结果 ::

guarantorId | guarantorName | patientName   | patientAccountNumber  | amount | id | cid
------------ --------------- --------------- ----------------------- -------- ---- ------
444321      | Jhon W        |   Ron K       |   8769                |  5000  | 1  | 1360

预期结果 ：：

guarantorId | guarantorName | patientName   | patientAccountNumber  | amount | id | cid
------------ --------------- --------------- ----------------------- -------- ---- ------
444321      | Jhon W        |   Ron K       |   8769                |  5000  | 1  | 1360
33332       | bilbo        |    Grace       |   65438               |  2000  | 3  | 1360
555333      | sumit        |    subir       |   90871               |  3500  | 5  | 1360

请帮忙。提前致谢。

标签： mysql

解决方案

SELECT guarantor.accnum as guarantorId,
       guarantor.name AS guarantorName,
       patient.name AS patientName,
       patient.accnum as patientAccountNumber,
       ob.amount,
       ob.id,
       ob.cid AS cid
FROM info AS o
JOIN users AS p ON (p.userid = o.id AND p.type = 'patient')
JOIN info AS ob ON o.cid = ob.cid
JOIN users AS patient ON (patient.userid = ob.id AND patient.type = 'patient')
JOIN users AS guarantor ON (guarantor.userid = ob.id AND guarantor.type = 'guarantor')
WHERE 1
  AND (    p.name LIKE "%8769%"
        OR p.accnum LIKE "%8769%" 
        OR o.cid LIKE "%8769%" ) 
  AND o.location_id IN(2);

担保人ID | 担保人姓名 | 患者姓名 | 患者帐号 | 金额 | 编号 | cid
:------------ | :------------ | :------------ | :-------------------- | :----- | -: | :---
444321 | 约翰 W | 罗恩 K | 8769 | 5000 | 1 | 1360
33332 | 比尔博 | 格蕾丝 | 65438 | 2000 | 3 | 1360
555333 | 峰会 | 苏比尔 | 90871 | 3500 | 5 | 1360

db<>在这里摆弄

推荐阅读