右连接子查询中的Mysql Access主表 [英] Mysql access main table inside right joined subquery

问题描述

SELECT id,region FROM ads     
RIGHT JOIN (SELECT * FROM ads a2 WHERE a2.region=ads.region LIMIT 4) AS regions    
ON regions.id=ads.id

说"unknown column ads.region".如何访问主表列区域?

Says "unknown column ads.region". How to access main table column region?

我需要为每个区域获取4行

I need to fetch 4 rows for each region

推荐答案

使用:

    SELECT a.id,
           a.region 
      FROM ads a    
RIGHT JOIN (SELECT t.*,
                   CASE 
                      WHEN @region = t.region THEN @rownum := @rownum + 1
                      ELSE @rownum := 1
                   END as rank,
                   @region := t.region
              FROM ADS t
              JOIN (SELECT @rownum := 0, @region := 1) r
          ORDER BY t.region) b ON b.id = a.id
                              AND b.region = a.region
                              AND b.rank <= 4

这篇关于右连接子查询中的Mysql Access主表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！