mysql - 如何为 FIlter 国家/地区编写 sql 查询作为状态明智

Question

这里我有一个简单的表格。我想最终得到一张像给定的那样的 table 。编写 sql 语句来实现此目的的最佳方法是什么？

表1

 Id    Name           Approved
 1     Australia      3
 2     UAE            1
 3     India          2

表2

Id    Status
1     Submit
2     In-Progress
3     Pending

结果显示为

Submitted    In-Progress     Pending
UAE           India         Australia

Answer 1

请尝试以下查询:

create table #country
(
    ID int identity(1,1),
    Name varchar(30),
    Approved int
)

create table #status
(
    ID int,
    Status varchar(30)
)

insert into #country (Name, Approved) values ('Australia',3), ('UAE',1), ('India',2)
insert into #status (ID, Status) values (1,'Submit'), (2, 'In-Progress'), (3,'Pending')

select Submit, [In-Progress],[Pending]
from (
    select t1.Name, t2.Status
    from #country t1
    inner join #status t2 on t1.Approved = t2.ID
)dd1
pivot (
    max(Name) for Status in ([Submit], [In-Progress],[Pending])
) piv

drop table #country
drop table #status

此查询的输出: