mysql查询从不同的表中提取多行到相应列中的行

x7rlezfr 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(537)

我们有两张表格如下：

Table-1 Name: apprecord
---------------------------------------
appid       |  desc            | status
ALT01       |   this is first  | Open
ALT02       |   this is second | Open
ALT03       |   this is third  | Closed
---------------------------------------

Table-2 Name: question
-----------------------------------------------
appid | questionseq |   ques               | ans
ALT01 | 1           | how are you          | good
ALT01 | 2           | are you fine         | yes
ALT02 | 1           | how was your day     | great
ALT02 | 2           | are you coming today | yes
ALT03 | 1           | where are you        | at home
ALT03 | 2           | are you fine         | yes
--------------------------------------------------

如何编写mysql查询，得到如下结果：

----------------------------------------------------------------------------------------
appid | desc          | status| QUES1           |   ANS1   | QUES2                | ANS2
ALT01 | this is first | Open  | how are you     |   good   | are you fine         | yes
ALT02 | this is second| Open  | how was your day|   great  | are you coming today | no
ALT03 | this is third | Closed| where are you   |   at home| are you fine         | yes
---------------------------------------------------------------------------------------

这里ques1、ans1、ques2、ans2是硬编码/固定列标题。

mysql

来源：https://stackoverflow.com/questions/51421445/mysql-query-to-fetch-rows-from-different-table-with-multiple-rows-into-respectiv

1条答案

按热度按时间

rkue9o1l1#

试试这个：
样本数据：

create table apprecord(appid varchar(10),desc varchar(100),status varchar(10));
insert into apprecord values
('ALT01','this is first','Open'),
('ALT02','this is second','Open'),
('ALT03','this is third','Closed');
create table question(appid varchar(10),questionseq int,ques varchar(100),ans varchar(10));
insert into question values
('ALT01',1,'how are you','good'),
('ALT01',2,'are you fine','yes'),
('ALT02',1,'how was your day','great'),
('ALT02',2,'are you coming today','yes'),
('ALT03',1,'where are you','at home'),
('ALT03',2,'are you fine','yes');

t-sql：

select ar.appid,
       `desc`, 
       `status`, 
       q1.ques ques1, 
       q1.ans ans1, 
       q2.ques ques2, 
       q2.ans ans2
from apprecord ar
left join (select appid, ques, ans from question where questionseq = 1) q1
  on ar.appid = q1.appid
left join (select appid, ques, ans from question where questionseq = 2) q2
  on ar.appid = q2.appid

这是标准的旋转，尽管它可以是丹麦如上所述，使用两个连接：）

赞(0）回复(0）举报 2021-06-20

我来回答

mysql查询从不同的表中提取多行到相应列中的行

1条答案

相关问题

热门标签

最新问答