php - Mysql - 带计数项的子查询

Question

我有 2 个表 .. 类别和交易。最后，我想执行以下操作:

// foreach category
    // Display category title
    // show 3 entries in deals where it matches category title above, along with a link saying: "View (count) More Deals

现在，我有几种方法可以做到这一点:

// The "bad" way:
// foreach category
    // fetch count of total deals in this category
    // Display category title (once) 
        // Fetch & Display 3 deals
    // Display "View (count) more deals
// This is just quite a few queries for one page

“其他”方式:

    SELECT count(deals.id),
           category.name as category_name,
           deal.name as deal_name
      FROM $db[ddd_deals] as deals
      JOIN $db[ddd_categories] 
        ON $db[ddd_categories].id = $db[ddd_deals].category_id
     WHERE deals.city_id = '$_SESSION[city_id]'
  ORDER BY $db[ddd_categories].name

除了显示每个类别中的交易数量，上面的所有内容都做了，并且每个类别只显示 3 个交易

-我应该从交易表中选择(还是从类别表中选择，然后获取交易？)

// I also tried the following. It returns what category and deal, but the count is off (it is only displaying 1):

  SELECT count(deals.id) as cc, 
         cat.name as cat_name, 
         deals.name as name 
    FROM ddd_categories as cat
    JOIN ddd_deals as deals 
      ON deals.category_id=cat.id
GROUP BY deals.id
ORDER BY cat.id

Answer 1

快点，无论您是按 deals.id 还是 .. 分组，顺序依据都在错误的位置。

select cc, cat_name, name from (
   SELECT count(deals.id) as cc, cat.name as cat_name, deals.name as name 
   FROM ddd_categories as cat
   JOIN ddd_deals as deals on deals.category_id=cat.id
   GROUP BY deals.id
)
ORDER BY xxx;