mysql -使用不同的别名在一个查询中运行多个查询

5jvtdoz2  于 2023-03-17  发布在  Mysql
关注(0)|答案(1)|浏览(125)

我有这3个疑问:

SELECT sum(sales) AS salesTotal FROM reporting WHERE date = '2023-01-10';
SELECT sum(traffic) AS trafficTotal FROM reporting WHERE date = '2023-02-15';
SELECT sum(transaction) AS transactionTotal FROM reporting WHERE date = '2023-03-05';

现在,我想在一个带有别名的查询中运行这3个查询。
例如,我尝试的是:

TotalSalesValue AS SELECT sum(sales) AS salesTotal FROM reporting WHERE date = '2023-01-10',
TotalTrafficValue AS SELECT sum(traffic) AS trafficTotal FROM reporting WHERE date = '2023-02-15',
TotalTransactionValue AS SELECT sum(transaction) AS transactionTotal FROM reporting WHERE date = '2023-03-05';

我尝试了上述查询,但它给我语法错误说:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TotalSalesValue AS SELECT

我所期待的回报是这样的:

{
    "TotalSalesValue": 200,
    "TotalTrafficValue": 150,
    "TotalTransactionValue": 10
}

有人能帮我纠正语法吗?

busg9geu

busg9geu1#

使用条件聚合:

SELECT SUM(CASE WHEN date = '2023-01-10' THEN sales ELSE 0 END) AS TotalSalesValue, 
       SUM(CASE WHEN date = '2023-02-15' THEN traffic ELSE 0 END) AS TotalTrafficValue,
       SUM(CASE WHEN date = '2023-03-05' THEN transaction ELSE 0 END) AS TotalTransactionValue
FROM reporting;

相关问题