如何用联合查询消除零

我有一个表,当我查询时,我得到的是

SELECT code
  ,year(prime_date) AS year
  ,month(prime_date) AS currentmonth
  ,0 AS lastmonth
  ,count(*) AS currentmonth_vol
  ,0 AS lastmonth_vol
FROM TableA
WHERE month(prime_date) = month(CURRENT_DATE)
  ,year(prime_date) = year(CURRENT_DATE)
GROUP BY code
  ,year
  ,month(prime_date)
  ,year(CURRENT_DATE)
code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    4             450                0            0          
bh    2000    4             333                0            0
SELECT code
  ,year(prime_date) AS year
  ,month(prime_date) AS currentmonth
  ,0 AS lastmonth
  ,count(*) AS currentmonth_vol
  ,0 AS lastmonth_vol
FROM TableA
WHERE month(prime_date) = month(CURRENT_DATE) - 1
  ,year(prime_date) = year(CURRENT_DATE)
GROUP BY code
  ,year
  ,month(prime_date)
  ,year(CURRENT_DATE)
code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    0               0                3            453          
bh    2000    0               0                3            673
kt    2000    0               0                3            838

我的问题是,是否可以通过联合查询来加入上述内容并消除0。?

答案应该是

code  year   currentmonth  currentmonth_vol  lastmonth    lastmonth_vol
ah    2000    4               450                3            453          
bh    2000    4               333                3            673
kt    2000    0                0                 3            838

解决方案:

试试下面的方法,未测试。

SELECT
    code,
    year,
    MAX(currentmonth) AS currentmonth,
    SUM(currentmonth_vol) AS currentmonth_vol
    lastmonth,
    lastmonth_vol
FROM
    (SELECT * FROM resulta UNION SELECT * FROM resultb) AS p
GROUP BY
    code, year, lastmonth

给TA打赏
共{{data.count}}人
人已打赏
未分类

如何在页面上多次显示相同的<div> 块?

2022-9-9 7:31:20

未分类

如何用php、js和本地存储上传图片?

2022-9-9 7:31:22

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索