php - 父行上带有 JOIN ... LIMIT 的 LIMIT 记录不适用于子行

Question

我必须对我的数据应用分页。我通过复杂的连接查询获取数据，没有任何子查询，只有简单的连接。

假设这个查询[这是一个非常简单的查询，我有一个比这个复杂的查询]

SELECT 
   states.states_name , 
   countries.countrty_name , 
   states.state_id , 
   countries.country_id 
FROM  countries 
INNER JOIN   states ON     countries.country_id =  states.country_id
WHERE  countries.status = "1"  AND  states.status = "1" 

这里的状态可能会因不同国家而异。意味着美国可以有 30 个州，印度可以有 26 个州，巴基斯坦可以有 15 个州....

我想要什么现在我想以某种方式查询，如果我想第一次获取前 5 个国家/地区的数据...再次获取另外 5 个国家/地区的数据意味着 LIMIT 5,10 ....之后再获取另外 5 个国家/地区。

如果您遇到我的问题，那么有什么想法......？如何实现...？或者无法通过 MySQL 实现？

表示仅限制父级...不限制子级...

示例

Countries    No of states
  USA            30
  INDIA          25
  UK             10
  FRANCE         18  
  CHINA          48     // so for the first time it will return this much rows [SUM OF THIS 5 COUNTRIES row]  ...  but only top 5 country 


  JAPAN          11
  UKRAINE        05
  RUSSIA         70
  GERMANY        10 
  SPAIN          06   // so for the second time it will return this much rows [SUM OF THIS 5 COUNTRIES row]  ...  but only seconmd top 5 country 


  ARGENTINA      03
  BRAZIL         23
  NORTH KOREA    14
  SOUTH KOREA    12  // so for the second time it will return this much rows [SUM OF THIS 5 COUNTRIES row]  ...  but only last top 5[available all if not 5] country 




 select    *
                         FROM saved_card sc
                         inner join redeem_code redeem On redeem.redeem_id = sc.redeem_id And redeem.status = '1'
                         inner join cards card On card.card_id = redeem.card_id And card.status = '1' And card.expiry_date >= '".$today."'
                         left join card_additional_info cinfo on cinfo.card_id = card.card_id And cinfo.status = '1'
                         where sc.user_id = '".$userId."'
                           And sc.status = '1'
                           And card.what_to_broadcast = '".$type."'
                           And redeem.used_coupon = '1'
                         ORDER BY redeem.redeem_id DESC, card.card_id ,cinfo.card_additional_info_id

这里一张卡有多个保存的卡，每个用户和每个兑换优惠券。一张卡将有多个附加信息。

我想要保存卡的数据...意味着保存卡的数量应该限制为 5 张，另外 5 张和另外 5 张.....

在每个saved_card中[它将与redeem_code建立一对一的连接]，并且仅连接到一张卡[因为卡是父卡]，但可以有多个card_additional_info字段[因为父卡是卡，它可以有多个子卡。]

Answer 1

你可以尝试使用子查询:

SELECT 
    states.states_name, 
    countries.countrty_name, 
    states.state_id, 
    countries.country_id 
FROM
    countries 
INNER JOIN
    states ON countries.country_id =  states.country_id
WHERE
    countries.status = "1"
    AND states.status = "1"
    AND countries.country_id IN (
        SELECT country_id FROM countries LIMIT 5, 5)

显然，LIMIT 的值必须替换为您的分页逻辑。

或者，如果您想要像您提供的示例中那样:

SELECT 
    c.countrty_name, 
    COUNT(s.state_id) AS "states" 
FROM
    countries AS c
LEFT JOIN
    states AS s ON c.country_id =  s.country_id
WHERE
    c.status = "1"
    AND s.status = "1"
GROUP BY
    c.country_id
LIMIT
    5, 5

应该可以了。