代码之家 › 专栏 › 技术社区 › Pepito

从一个选择中使用别名,以便在另一个选择中将其使用

ms-access sql

Pepito · 技术社区 · 7 月前

问这个琐碎的问题我觉得有点可笑,但我的SQL知识不是很好!

好吧,我的桌子看起来像这样

TransactionID     Establishment    Client

TR001             Estab1           XXX
TR002             Estab1           YYY
...
TR00X             EstabN           XXX

我需要统计每家机构向特定客户(XX、YY和ZZ)的销售额,以及该数字占该机构所有销售额的百分比。结果应该是这样的:

Establishment    Client    Nbr_Sales    %Total_sales

Estab1           XXX       64           0.35%
Estab1           YYY       321          0.17%
Estab2           XXX       2325         25%
...

我写了一份请求,要求获得每家店的销售总数:

SELECT Establishment, Count(*) AS tot_sales_by_estab
FROM data_list 
GROUP BY Establishment;

此请求为我提供了每家公司向“XX”、“YY”和“ZZ”客户完成的销售总数

SELECT Establishment, Client, Count(*) as Nbr_Sales
FROM data_list 
WHERE Client = 'XX' OR Client = 'YY' OR Client = 'ZZ'
GROUP BY Establishment, Client;

我只需要使用前面的别名“tot_sales_by_estab”向此请求添加另一列,并执行以下操作

ROUND((Nbr_Sales / tot_sales_by_estab)*100, 4) AS %Total_sales

但我不知道该怎么做

1 回复 | 直到 7 月前

samhita 7 月前

如果您提供了示例输入和输出,那么我们就可以进行测试了。

为了连接两个结果集,我们通常使用JOIN,在结果集之间使用一个公共列。

结果集中的公共列是Establishment,因此我们可以使用Establishment进行INNER JOIN。

total_sales 和 client_sales 是CTE,它基本上包含您提供的代码。

WITH total_sales AS (
    SELECT 
        Establishment, 
        COUNT(*) AS tot_sales_by_estab
    FROM 
        data_list 
    GROUP BY 
        Establishment
),

client_sales AS (
    SELECT 
        Establishment, 
        client, 
        COUNT(*) AS Nbr_Sales
    FROM 
        data_list 
    WHERE 
        client IN ('XXX', 'YYY', 'ZZZ')
    GROUP BY 
        Establishment, client
)

SELECT 
    cs.Establishment,
    cs.Client,
    cs.Nbr_Sales,
    ROUND((cs.Nbr_Sales * 100.0 / ts.tot_sales_by_estab), 2) AS %Total_sales
FROM 
    client_sales cs
INNER JOIN 
    total_sales ts ON cs.Establishment = ts.Establishment
ORDER BY 
    cs.Establishment, cs.Client;

编辑 :无CTE

SELECT 
    dl.Establishment,
    dl.Client,
    COUNT(*) AS Nbr_Sales,
    ROUND((COUNT(*) * 100.0 / total_sales.tot_sales_by_estab), 2) AS %Total_sales
FROM 
    data_list dl
JOIN 
    (SELECT Establishment, COUNT(*) AS tot_sales_by_estab
     FROM data_list
     GROUP BY Establishment) AS total_sales
ON 
    dl.Establishment = total_sales.Establishment
WHERE 
    dl.Client IN ('XXX', 'YYY', 'ZZZ')
GROUP BY 
    dl.Establishment, dl.Client
ORDER BY 
    dl.Establishment, dl.Client;