mysql - 按年/月选择显示

Question

你能帮我从表格中选择和汇总所有值，并根据月份/今年显示它们吗？

表格如下所示"

| ID | value | reg_date            | 
| 1  | 2     | 2017-01-11 02:26:22 |
| 2  | 2     | 2017-03-12 12:22:23 |
| 3  | 2     | 2017-04-13 08:26:33 |
| 4  | 3     | 2017-04-15 12:26:16 |
| 5  | 5     | 2017-05-15 19:26:13 |
| 6  | 2     | 2017-06-14 17:12:16 |
| 7  | 6     | 2017-07-12 14:26:16 |
| 8  | 1     | 2015-09-11 13:23:16 |
| 9  | 1     | 2016-09-05 12:26:34 |
| 10 | 1     | 2017-12-11 19:11:45 |

我想得到这样的东西:

| value  | reg_date                 |
|    2   |  2017-01                 |
|    0   |  2017-02                 |
|    2   |  2017-03                 |
|    5   |  2017-04                 |
|    5   |  2017-05                 |
|    2   |  2017-06                 |
|    6   |  2017-07                 |
|    0   |  2017-08                 |
|    2   |  2017-09                 |
|    0   |  2017-10                 |
|    0   |  2017-11                 |
|    1   |  2017-12                 |

Answer 1

我尝试了以下方法来解决您的问题:

--created sample table datte which will contain following records 

create table datte (id int identity(1,1), value int, reg_date datetime);

insert into datte(value,reg_date) values (2,'2017-01-11 02:26:22');
insert into datte(value,reg_date) values (2,'2017-03-12 12:22:23');
insert into datte(value,reg_date) values (2,'2017-04-13 08:26:33');
insert into datte(value,reg_date) values (3,'2017-04-15 12:26:16');
insert into datte(value,reg_date) values (5,'2017-05-15 19:26:13');
insert into datte(value,reg_date) values (2,'2017-06-14 17:12:16');
insert into datte(value,reg_date) values (6,'2017-07-12 14:26:16');
insert into datte(value,reg_date) values (1,'2015-09-11 13:23:16');
insert into datte(value,reg_date) values (1,'2016-09-05 12:26:34');
insert into datte(value,reg_date) values (1,'2017-12-11 19:11:45');

然后我创建了一个表类型来存储年份列表

 create type [values] as table(
[year] int
);

你也可以在这里使用临时表。

    -- create temp table to store the output
create table #tempdate([year] varchar(1000), [count] int);

--create a  variable of table type values
declare @years as [values] ,
--create following variable to process the data


    @year int,  @minyear int,@maxyear int;

     --insert years in table type variable @years
    insert into @years 
select YEAR(reg_date) from datte
group by YEAR(reg_date)

 --store min year and max year value in variable for processing 
select @minyear = min([year]) from @years;
select @maxyear = max([year]) from @years;

 -- logic to create query to get desired output
while(@minyear <= @maxyear)
begin
SET @YEAR = @minyear;

IF EXISTS(SELECT 1 FROM DATTE WHERE REG_DATE LIKE '%'+CONVERT(VARCHAR,@YEAR)+'%')
BEGIN

    --insert the yearly in temp table
    insert into  #tempdate 
     select   cast(convert(varchar,@year) as varchar) + '-' +cast(m.number as varchar) , isnull(total_qty,0) as total_qty
    from (
     select number
     from    master.dbo.spt_values 
     where type = 'P' and number between 01 and 12
    ) m 
    left join (
        select   MONTH(reg_date) as mth, SUM(value) as total_qty
        from     datte
        where    YEAR(reg_date) = @year
        group by MONTH(reg_date)
    ) s on m.number = s.mth ;

    END

SET @MINYEAR = @MINYEAR + 1;
end
--print the data 
select [count],[year] from  #tempdate

希望对您有所帮助，杰