单行输出 在 MySQL 中具有多列，我可以在多行中填充这些列吗?

Question

SELECT 
count(case when a.Rank between 0 and  3000 then a.ProductId end ) as SKUsNotAvailable,
count(case when a.Rank between 3001 and  5000 then a.ProductId end ) as SKUsNotAvailable,
count(case when a.Rank between 5001 and  10000 then a.ProductId end ) as SKUsNotAvailable,
count(case when a.SOH<a.ReserveQty and a.Rank between 0 and  3000 then a.ProductID end) as SKUslessthanResQty,
count(case when a.SOH<a.ReserveQty and a.Rank between 3001 and 5000 then a.ProductID end) as SKUslessthanResQty,
count(case when a.SOH<a.ReserveQty and a.Rank between 5001 and  10000 then a.ProductID end) as SKUslessthanResQty
FROM
pos_summary.tbl_city_product_rank_soh_resqty a
inner join pos.tbl_store b

SKUsNotAvailable SKUsNotAvailable SKUsNotAvailable SKUslessthanResQty SKUslessthanResQty SKUslessthanResQty
27               35               159              22                 30                 124

"我在一行中获取此输出，如何在两列中获取它"，例如

SKUsNotAvailable    SKUslessthanResQty
27                   22
35                   30
159                  124

Answer 1

你需要类似的东西

SELECT count(a.ProductId) as SKUsNotAvailable,
count(case when a.SOH<a.ReserveQty then a.ProductID end) as SKUslessthanResQty,
FROM pos_summary.tbl_city_product_rank_soh_resqty a
/* INNER JOIN pos.tbl_store b ON ?????? */
CROSS JOIN pos.tbl_store b
WHERE a.Rank between 0 and  10000
GROUP BY a.Rank between 0 and  3000, 
a.Rank between 3001 and  5000

Answer 2

">

我们也可以这样">

SELECT
(case
when a.Rank between 1 and 3000 then '1-3000'
when a.Rank between 3001 and 5000 then '3001-5000' 
when a.Rank between 5001 and 10000 then '5001-10000' end) as Ranks,
count(case when a.Category= 'Z' then a.ProductId end) as SKUsNotAvailable,
sum(case when a.Category='Z' then c.AvgSaleValue else 0 end) as LossSaleValue,
count(case when a.Category= 'R' then a.ProductID end) as SKUslessthanResQty,
sum(case when a.Category='R' then c.AvgSaleValue else 0 end) as LossSaleValue_ResQty
FROM
pos_summary.tbl_city_product_rank_soh_resqty a
inner join pos_summary.tbl_store b on a.StoreId = b.StoreID and b.Region_2 = 'TG'
inner join pos_summary.tbl_city_sku_avg_sale_detail c on a.ProductID=c.ProductID and a.City=c.City
WHERE
a.Rank between 0 and 10000
group by
Ranks;