mysql - 将两部分 SQL 查询合并为一个查询

Question

这就是我所拥有的:

查询

select
bc.short_desc as cdesc
from
blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1

结果

----------------
| |cdesc       |
----------------
|1|Top level   |
----------------
|2|Sub level   |
----------------
|3|SubSub level|
----------------

第二个查询

select
bt.short_desc as tdesc
from
blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1

第二个结果

----------------
| |tdesc       |
----------------
|1|Tag1        |
----------------
|2|Tag2        |
----------------

连接这两个查询后所需的结果

----------------------
| |cdesc       |tdesc|
----------------------
|1|Top level   |Tag1 |
----------------------
|2|Sub level   |Tag2 |
----------------------
|3|SubSub level|     |
----------------------

这可能吗？

Answer 1

尝试这个查询

SET @rn1 = 0;
SET @rn2 = 0;

SELECT t1.rId, t1.cdesc, t2.tdesc
FROM 
(select @rn1 := rn1 + 1 AS rId, bc.short_desc as cdesc
from blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1) t1, 
(select @rn2 := rn2 + 1 AS rId, bt.short_desc as tdesc
from blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1) t2
WHERE t1.rId = t2.rId;

对于 MS SQL 服务器

SELECT t1.rId, t1.cdesc, t2.tdesc
FROM 
(select row_number() over(order by bp.post_id) AS rId, bc.short_desc as cdesc
from blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1) t1, 
(select row_number() over(order by bp.post_id) AS rId, bt.short_desc as tdesc
from blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1) t2
WHERE t1.rId = t2.rId;