mysql - 从 1 个提供程序表中显示 2 个单独的提供程序名称

问题描述

我希望我的输出如下:

我的要求如下:

• 最新的服务 ID
• 默认 = 'Y'

我应该如何编写查询?有人可以帮我吗?

How should I write the query? Can someone help me?

推荐答案

你可以使用这个:

select se.svcid, p1.pname as serviceprovider, p2.pname as tmidprovider
from tmid t
inner join (select sno, max(svcid) as maxsvcid from service group by sno) s 
  on t.sno = s.sno and t.`default` = 'Y'
inner join service se on se.svcid = s.maxsvcid
left join provider p1 on se.pid = p1.pid
left join provider p2 on t.pid = p2.pid;

表格

create table service (svcid int, sno varchar(20), pid int);
insert into service values 
(1, '11-11-11-11', 1), (2, '11-11-11-11', 2), (3, '11-11-11-12', 1), (4, '11-11-11-12', 2), (5, '11-11-11-13', NULL);

create table tmid (id int, sno varchar(20), pid int, `default` char(1));
insert into tmid values
(1, '11-11-11-11', 1, 'N'),(2, '11-11-11-11', 2, 'Y'),(3, '11-11-11-12', 1, 'N'),
(4, '11-11-11-12', 2, 'Y'),(5, '11-11-11-13', 2, 'Y'),(6, '11-11-11-13', 3, 'N');

create table provider (pid int, pname varchar(20));
insert into provider values (1, 'Ambank'), (2, 'Citybank'), (3, 'CIMB Bank');

数据

mysql> select * from service;
+-------+-------------+------+
| svcid | sno         | pid  |
+-------+-------------+------+
|     1 | 11-11-11-11 |    1 |
|     2 | 11-11-11-11 |    2 |
|     3 | 11-11-11-12 |    1 |
|     4 | 11-11-11-12 |    2 |
|     5 | 11-11-11-13 | NULL |
+-------+-------------+------+

mysql> select * from tmid;
+------+-------------+------+---------+
| id   | sno         | pid  | default |
+------+-------------+------+---------+
|    1 | 11-11-11-11 |    1 | N       |
|    2 | 11-11-11-11 |    2 | Y       |
|    3 | 11-11-11-12 |    1 | N       |
|    4 | 11-11-11-12 |    2 | Y       |
|    5 | 11-11-11-13 |    2 | Y       |
|    6 | 11-11-11-13 |    3 | N       |
+------+-------------+------+---------+

mysql> select * from provider;
+------+-----------+
| pid  | pname     |
+------+-----------+
|    1 | Ambank    |
|    2 | Citybank  |
|    3 | CIMB Bank |
+------+-----------+

结果

+-------+-----------------+--------------+
| svcid | serviceprovider | tmidprovider |
+-------+-----------------+--------------+
|     2 | Citybank        | Citybank     |
|     4 | Citybank        | Citybank     |
|     5 | NULL            | Citybank     |
+-------+-----------------+--------------+

这篇关于mysql - 从 1 个提供程序表中显示 2 个单独的提供程序名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！