# 如何用联合查询消除零

``````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
``````

``````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
``````