文章/答案/技术大牛

发布

社区首页 >问答首页 >mysql中的Concat 2查询库存报告

问mysql中的Concat 2查询库存报告
EN

Stack Overflow用户

提问于 2018-05-02 02:18:52

回答 1查看 50关注 0票数 0

我的sql中有两个查询：

查询1

SELECT
    product_id, 
    product_name, 
    SUM(case when type = "I" then quantity else 0 end) as import, 
    SUM(case when type = "O" then quantity else 0 end) as export, 
    SUM(case when type = "R" then quantity else 0 end) as refund, 
    (
        SUM(case when type = "R" then quantity else 0 end)
        - SUM(case when type = "O" then quantity else 0 end)
        +SUM(case when type = "I" then quantity else 0 end)
    ) as available

FROM
    `i1ntl_ldn_soft_inventory_detail`
WHERE 
    product_id = 2772 
    and date(refdate) < "2018-03-09" 
    and date(refdate)>= "2018-03-01"
GROUP by 
    product_id
ORDER BY 
    `import` DESC

结果是

查询2

SELECT 
    product_id, 
    product_name, 
    (
        SUM(case when type = "R" then quantity else 0 end) 
        - SUM(case when type = "O" then quantity else 0 end)
        +SUM(case when type = "I" then quantity else 0 end)
    ) as available_start
FROM
    `i1ntl_ldn_soft_inventory_detail` 
WHERE 
    product_id = 2772 
    and date(refdate) < "2018-03-01"
GROUP by
    product_id

结果是：

我能把两个查询合并成一个来得到结果吗？

Product_id, product_name, available_start, import, export, refund, available

sql

mysql

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-05-02 02:26:39

使用条件聚合，并将对refdate的检查移到CASE表达式中。然后，您可以使用一个查询。

SELECT
    product_id,
    product_name,
    SUM(CASE WHEN type = 'I' AND DATE(refdate) >= '2018-03-01' AND
             DATE(refdate) < '2018-03-09' THEN quantity ELSE 0 END) AS import,
    SUM(CASE WHEN type = 'O' AND DATE(refdate) >= '2018-03-01' AND
             DATE(refdate) < '2018-03-09' THEN quantity ELSE 0 END) AS export,
    SUM(CASE WHEN type = 'R' AND DATE(refdate) >= '2018-03-01' AND
             DATE(refdate) < '2018-03-09' THEN quantity ELSE 0 END) AS refund,
    SUM(CASE WHEN type IN ('R', 'I') AND DATE(refdate) >= '2018-03-01' AND
             DATE(refdate) < '2018-03-09' THEN quantity ELSE 0 END) -
        SUM(CASE WHEN type = 'O' AND DATE(refdate) >= '2018-03-01' AND
                 DATE(refdate) < '2018-03-09' THEN quantity ELSE 0 END) AS available,
    SUM(CASE WHEN type IN ('R', 'I') AND DATE(refdate) < '2018-03-01'
             THEN quantity ELSE 0 END) -
        SUM(CASE WHEN type = 'O' AND DATE(refdate) < '2018-03-01'
                 THEN quantity ELSE 0 END) AS available_start
FROM i1ntl_ldn_soft_inventory_detail
WHERE product_id = 2772
GROUP by product_id
ORDER BY import DESC

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/50126280

复制

相似问题

问mysql中的Concat 2查询库存报告
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql中的Concat 2查询库存报告EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql中的Concat 2查询库存报告
EN