MySQL 从数据库返回新产品和二手产品的数量

Question

我正在使用 PHP 和 MySQL。我有这样的表格:

categories
id  |   category |
1   |   Fujifilm
2   |   Sony
3   |   Nikon

listings
 id  | title         |Condition_id  | cat_id
 1   | Fujifilm XE-1 |     1        |   1  
 2   | Fujifilm XE-1 |     1        |   1
 3   | Fujifilm XE-1 |     2        |   1
 4   | Fujifilm X70  |     1        |   1

Conditions
 id | condition
 1  |   New
 2  |   Used

如果有人搜索 Fujifilm，我需要返回:

2 Results found:

Fujifilm XE-1 (2 new available, 1 used available)
Fujifilm X70  (1 new available)

这可以在单个查询中完成吗？

到目前为止我已经:

    SELECT 
    listings.title,
    con.condition

    FROM listings

    JOIN conditions con ON listing.condition_id = con.id
    JOIN categories cat ON cat.id = listings.cat_id

    WHERE listings.cat.id = 1   

如果我按标题分组，条件就会合并。

Answer 1

使用GROUP_CONCAT

SELECT  title,
        GROUP_CONCAT(CONCAT_WS(' ', total_count, `condition`, 'available')) AS Result
FROM
    (
        SELECT 
            listings.title,
            con.condition,
            COUNT(*) total_count
        FROM listings
            JOIN conditions con ON listings.condition_id = con.id
            JOIN categories cat ON cat.id = listings.cat_id
        WHERE listings.cat_id = 1   
        GROUP BY listings.title, con.condition
    ) a
GROUP BY title

这是一个Demo .