mysql - 获取使用的 select 语句具有不同的列数

Question

如何在单个表中获取此查询的输出

select 
  common_name, category, size, container, flower_image, price 
from 
  biocard
join 
  inventory_listing 
union 
select 
  common_name, count(*) as total_quantity 
from
  biocard 
group by 
  common_name;

Answer 1

看看这是否适合您。只需添加您自己的 ON 条件，而不是我的 biocard.pk = inventory_listing.fk。我不知道该通过什么加入你们的 table 。

SELECT
    common_name,
    category,
    size,
    container,
    flower_image,
    price,
    bio_Count.total_quantity
FROM 
    biocard join inventory_listing
ON
    biocard.pk = inventory_listing.fk LEFT JOIN (SELECT
                                                    common_name,
                                                    count(*) as total_quantity 
                                                FROM
                                                    biocard 
                                                GROUP BY 
                                                    common_name) bio_Count
ON
    bio_Count.common_name = biocard.common_name