下面是两个单独运行时按预期工作的SQL查询。我试过用UNION&UNION ALL将它们合并到一个SQL中,但显然由于每个SQL返回的列数不同,我无法使用这些技术。
我使用的数据是here
下面是我试图合并的两个独立查询
查询1

SET group_concat_max_len = 100485760;
SELECT
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 50/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS aa
    ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 75/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS bb
    ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 85/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS cc
    ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 95/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS dd
    ,MAX(NUM_PRODS) AS MAAX
FROM
    SALES_INFO

问题二
SET group_concat_max_len = 100485760;
SELECT
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 75/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS ee
FROM
    SALES_INFO
WHERE
        SALE_DATE >= DATE_SUB(curdate(), INTERVAL 2 WEEK)

我可以请求帮助将这两个查询合并为一个吗?

最佳答案

您可以在select中使用子查询,如下所示:

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 50/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS 50per
       ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 75/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS 75per
       ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 85/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS 85per
       ,CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 95/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS 95per
       ,MAX(NUM_PRODS) AS MAAX
       ,(SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NUM_PRODS ORDER BY NUM_PRODS SEPARATOR ','),',', 75/100 * COUNT(*) + 1),',', -1) AS DECIMAL) AS 2weeks
         FROM SALES_INFO
         WHERE SALE_DATE >= DATE_SUB(curdate(), INTERVAL 2 WEEK)
         ORDER BY 2weeks limit 1) as 2weeks
FROM
    SALES_INFO

您没有指定这两个查询之间的关系,也没有指定它们的数据结构,因此目前它对第一个结果的限制是有限的,如果这不是您需要的,请更新您的需求,我将更新答案。

10-07 15:59
查看更多