SQL，查找两个给定名称在一列中是否具有相同编号的查询

Question

我已经尝试解决这个问题大约一周了。我真的需要别人的帮助。

我将得到两三个"LN"，我必须检查它们在"FODEX"列中是否有相同的编号。然后，如果它们具有相同的FODEX则添加一列并打印一列，如果没有，则打印零。

我写了这个查询：

SELECT  MSTR.LN , LINK.SEQNO, MMA5_MSTR.FODEX, MMA5_MSTR.DES, MSTR.SL
FROM  (((MSTR INNER JOIN LINK
ON MSTR.SEQNO = LINK.SEQNO) INNER JOIN MMA5_MSTR
ON LINK.FODEX = MMA5_MSTR.FODEX))
WHERE (MSTR.LN LIKE "%NOVOLLL%" OR MSTR.LN LIKE "%TRES%" OR MSTR.LN LIKE 
"%ENBREL%")
GROUP BY SEQNO, SL
ORDER BY FODEX;

这是我得到的输出，

LN       || SEQNO       ||   FODEX  ||     DES     ||  SL
----------------------------------------------------------------   
TRES      || 71842       ||    13    ||  ANTIDIABE  ||  12
NOVOLLL   || 44340       ||    13    ||  ANTIDIABE  ||  12
NOVOLLL   || 44340       ||   1059   ||  ANTIDIABE  ||  55
TRES      || 71842       ||   1059   ||  ANTIDIABE  ||  55
TRES      || 71842       ||   1317   ||  ANTIDIABE  ||  66
NOVOLLL   || 44340       ||   1317   ||  ANTIDIAB   ||  66
ENBREL    || 40869       ||   1722   ||  TNe        ||  90
ENBREL    || 40869       ||   29359  ||  IMMUNOSUPP ||  103

这是我试图获得的输出，

LN       || SEQNO       ||   FODEX  ||     DES     ||  SL || Y/N
----------------------------------------------------------------   
TRES      || 71842       ||    13    ||  ANTIDIABE  ||  12 || 1
NOVOLLL   || 44340       ||    13    ||  ANTIDIABE  ||  12 || 1
NOVOLLL   || 44340       ||   1059   ||  ANTIDIABE  ||  55 || 1
TRES      || 71842       ||   1059   ||  ANTIDIABE  ||  55 || 1
TRES      || 71842       ||   1317   ||  ANTIDIABE  ||  66 || 1
NOVOLLL   || 44340       ||   1317   ||  ANTIDIAB   ||  66 || 1
ENBREL    || 40869       ||   1722   ||  TNe        ||  90 || 0
ENBREL    || 40869       ||   29359  ||  IMMUNOSUPP ||  103|| 0

Answer 1

我认为你可以用聚合做你想做的事：

SELECT MSTR.LN,
MAX(MMA5_MSTR.FODEX) = MIN(MMA5_MSTR.FODEX) as is_same_flag
FROM MSTR INNER JOIN
LINK
ON MSTR.SEQNO = LINK.SEQNO INNER JOIN
MMA5_MSTR
ON LINK.FODEX = MMA5_MSTR.FODEX
WHERE MSTR.LN LIKE '%NOVOLLL%' OR
MSTR.LN LIKE '%TRES%' OR
MSTR.LN LIKE '%ENBREL%'
GROUP BY LN;

这不会添加额外的列，但它似乎可以满足您的需求。

Answer 2

也许是一种略有不同的方法，其中子查询确定有多少个合格的 fodex，主查询会检查它们。请注意，南瓜与一些符合条件的 lns 共享一个 fodex，但与 1722 一样获得值 0，因为只有 1 ln

drop table if exists t;
create table t
(LN  varchar(20), SEQNO int,  FODEX  int);
insert into t values
('TRES'      , 71842       ,    13    ),
('NOVOLLL'   , 44340       ,    13    ),
('NOVOLLL'   , 44340       ,   1059   ),
('TRES'      , 71842       ,   1059   ),
('TRES'      , 71842       ,   1317   ),
('NOVOLLL'   , 44340       ,   1317   ),
('ENBREL'    , 40869       ,   1722   ),
('ENBREL'    , 40869       ,   29359 ),
('tres'    , 40869       ,     29359 ),
('pumpkin'    , 40869       ,  29359)  ;

select ln,seqno,cntin,t.fodex,
case when ln in ('tres','novolll','enbrel') and cntin > 1 then 1 
when ln not in ('tres','novolll','enbrel') then 0
when cntin <= 1 then 0
end as 'y/n'
from t
join
(select fodex, sum(case when ln in ('tres','novolll','enbrel') then 1 else 0 end) as cntin 
from t 
group by fodex) s
on t.fodex = s.fodex
order by t.fodex
;
+---------+-------+-------+-------+------+
| ln      | seqno | cntin | fodex | y/n  |
+---------+-------+-------+-------+------+
| TRES    | 71842 |     2 |    13 |    1 |
| NOVOLLL | 44340 |     2 |    13 |    1 |
| NOVOLLL | 44340 |     2 |  1059 |    1 |
| TRES    | 71842 |     2 |  1059 |    1 |
| NOVOLLL | 44340 |     2 |  1317 |    1 |
| TRES    | 71842 |     2 |  1317 |    1 |
| ENBREL  | 40869 |     1 |  1722 |    0 |
| ENBREL  | 40869 |     2 | 29359 |    1 |
| tres    | 40869 |     2 | 29359 |    1 |
| pumpkin | 40869 |     2 | 29359 |    0 |
+---------+-------+-------+-------+------+
10 rows in set (0.00 sec)