实现一句查询SQL,4列显示,麻烦各位大神赐教

Question

实现按照不同类型显示价格,最后结果是三个类型一个价格,表现的应该是4列三行
我是写不下去了,麻烦大神赐教

SELECT 
amount_description as Principal
amount_description as FBAPerUnitFulfillmentFee
amount_description as Commission
amount as 价格,

FROM
    tongji

Answer 1

SELECT 
    sku_p as aa,  
    COUNT(sku_p) as bb,
    amount as cc,
    SUM(amount) as dd

FROM
tongji 
WHERE amount_description='Principal'
GROUP BY sku_p
HAVING COUNT(sku_p) >= 1;

SELECT 
    sku_p as aa,  
    COUNT(sku_p) as bb,
    amount as cc,
    SUM(amount) as dd

FROM
tongji 
WHERE amount_description='FBAPerUnitFulfillmentFee'
GROUP BY sku_p
HAVING COUNT(sku_p) >= 1;

SELECT 
    sku_p as aa,  
    COUNT(sku_p) as bb,
    amount as cc,
    SUM(amount) as dd

FROM
tongji 
WHERE amount_description='Commission'
GROUP BY sku_p
HAVING COUNT(sku_p) >= 1;

我写了上次 输出不同类型的价格,但是能否实现一句sql达到输出类型,where or 是不行的

已经解决问题了,我这样写的

SELECT 
    sku_p as SKU, 
    fulfillment_id AS 配送方式, 
    COUNT(sku_p) as 销售数量,
    amount as 销售单价,
    SUM(case amount_description when 'Principal' then amount else 0 end) 销售金额,
    SUM(case amount_description when 'Commission' then amount else 0 end) 销售佣金,
    SUM(case amount_description when 'FBAPerUnitFulfillmentFee' then amount else 0 end) 销售尾程物流费,    
    SUM(case amount_description when 'Principal' then amount else 0 end)+SUM(case amount_description when 'FBAPerUnitFulfillmentFee' then amount else 0 end)  
     +SUM(case amount_description when 'Commission' then amount else 0 end) 结算销售金额
FROM
    tongji 

GROUP BY sku_p
HAVING COUNT(sku_p) >= 1;