如何选择用户选择年份的所有月份的数据，即使是空的 [英] How to select data of all months of user selected year even its empty

问题描述

我有 2 张桌子，posbila 和 posbilb.字段 bill_date(以日期时间格式存储)在表 posbila 中，字段 Qty 在 posbilb 中.

I have 2 tables, posbila and posbilb. The field bill_date (store with dateTime format) is in table posbila and field Qty in posbilb.

我尝试了以下查询，但不会显示空数据的月份

I have tried the follow query but the months with empty data wont be shown

SELECT
    posbilb.stkcode,
    SUM(posbilb.qty),
    date_format(posbila.bill_date, '%m/%y')

FROM
    posbila,posbilb
WHERE
    posbila.bill_no = posbilb.RECEIVE_ID

GROUP BY 
    1, 3

如果用户选择 2015 年，我想要以下结果:

If user select year 2015 I want the following result:

月 |数量
一月 |154
二月 |00
三月 |123
四月 |00
五月 |00
六月 |60
七月 |00
八月 |99
九月 |00
十月 |00
十一月 |10
十二月 |00

Month | Qty
Janury | 154
February | 00
March | 123
April | 00
May | 00
June | 60
July | 00
August | 99
September | 00
October | 00
November | 10
December | 00

推荐答案

如果您没有日期表并且没有创建日期表的权限，您可以与我们联合.例如给定

If you don't have a dates table and don't have permission to create one you could us union. For example given

select * from tbl_loanledger;
+------+-------------+---------------+---------+---------+--------------+---------+
| id   | borrower_id | loanmaster_id | payment | balance | date_created | deleted |
+------+-------------+---------------+---------+---------+--------------+---------+
|    1 |           4 |             1 |      50 |     250 | 2016-05-28   |       0 |
|    2 |           1 |             2 |      20 |      80 | 2016-05-25   |       0 |
|    3 |           1 |             2 |      30 |      50 | 2016-06-01   |       0 |
|    4 |           2 |             3 |     100 |     400 | 2016-06-09   |       0 |
|    5 |           2 |             3 |      50 |     350 | 2016-06-10   |       0 |
|    6 |           1 |             4 |      50 |     200 | 2016-06-16   |       0 |
|    7 |           1 |             7 |      50 |     250 | 2016-06-16   |       1 |
+------+-------------+---------------+---------+---------+--------------+--------+

set @yyyy = '2016';

select      date_format(s.date_created, '%m/%y') 'mm/yy', sum(s.payment) amount
from
(
select  1 as srce,ll.payment payment, ll.date_created date_created from tbl_loanledger ll
union select 2 ,0, concat(@yyyy,'-01-31') union select 2, 0, concat(@yyyy,'-02-01') union select 2, 0, concat(@yyyy,'-03-31')
union select 2, 0, concat(@yyyy,'-04-30') union select 2, 0, concat(@yyyy,'-05-31') union select 2, 0, concat(@yyyy,'-06-30')
union select 2, 0, concat(@yyyy,'-07-31') union select 2, 0, concat(@yyyy,'-07-31') union select 2, 0, concat(@yyyy,'-09-30')
union select 2, 0, concat(@yyyy,'-10-31') union select 2, 0, concat(@yyyy,'-09-30') union select 2, 0, concat(@yyyy,'-12-31') 
) s 
group       by 1

结果

+-------+--------+
| mm/yy | amount |
+-------+--------+
| 01/16 |      0 |
| 02/16 |      0 |
| 03/16 |      0 |
| 04/16 |      0 |
| 05/16 |     70 |
| 06/16 |    230 |
| 07/16 |      0 |
| 09/16 |      0 |
| 10/16 |      0 |
| 12/16 |      0 |
+-------+--------+

这篇关于如何选择用户选择年份的所有月份的数据，即使是空的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！