SQL divide many records by day

问题: my SQL table's struct is very simple,only contains 3 fields: createDate(Date): time when record inserted; title(String): title for record; count(Integer32): count for rec...

问题:

my SQL table's struct is very simple,only contains 3 fields:

createDate(Date): time when record inserted;
title(String): title for record;
count(Integer32): count for record;

There 10w+ records in the table! Represents records inserted in one year:

  • Any day could inserted any number records(include 0 record)

So,How could I divide records by days???

eg: There 10 records in the table:

1. 2019-01-01 10:20:15 xxx
2. 2019-01-01 12:50:10 xxx
3. 2019-01-01 23:20:19 xxx

4. 2019-01-02 10:20:15 xxx

5. 2019-01-05 08:20:15 xxx
6. 2019-01-05 22:20:15 xxx

7. 2019-02-10 10:20:15 xxx
8. 2019-02-10 11:20:15 xxx
9. 2019-02-10 15:20:15 xxx

10. 2019-02-15 10:20:15 xxx

I want result : divide to 5 "collections"

collection "2019-01-01" (contain 3 records):

- 2019-01-01 10:20:15 xxx
- 2019-01-01 12:50:10 xxx
- 2019-01-01 23:20:19 xxx

collection "2019-01-02" (contain 1 record):

- 2019-01-02 10:20:15 xxx

collection "2019-01-05" (contain 2 records):

- 2019-01-05 08:20:15 xxx
- 2019-01-05 22:20:15 xxx

collection "2019-02-10" (contain 3 records):

- 2019-02-10 10:20:15 xxx
- 2019-02-10 11:20:15 xxx
- 2019-02-10 15:20:15 xxx

collection "2019-02-15" (contain 1 record):

- 2019-02-15 10:20:15 xxx

回答1:

If my table schema is correct then this would be your possible solution.

    GO

    CREATE TABLE #tempRequestForMeList   
    (   
         createDate datetime,
         title nvarchar(50),
         [count] int
    )  

    GO

    insert into #tempRequestForMeList ( createDate, title, [count] )
    values ( '2016-09-20 17:17:04.840', 'dd', 0 )
    , ( '2016-09-20 17:17:04.840', 'dd', 1 )
    , ( '2016-09-20 07:17:04.840', 'dd', 1 )
    , ( '2016-09-20 05:17:04.840', 'dd', 1 )
    , ( '2016-09-20 13:17:04.840', 'dd', 1 )
    , ( '2016-09-19 12:17:04.840', 'dd', 1 )
    , ( '2016-09-19 02:17:04.840', 'dd', 1 )
    , ( '2016-09-19 01:17:04.840', 'dd', 1 )
    , ( '2016-09-18 02:17:04.840', 'dd', 1 )
    , ( '2016-09-18 03:17:04.840', 'dd', 1 )
    , ( '2016-09-18 05:17:04.840', 'dd', 1 )
    , ( '2016-09-18 07:17:04.840', 'dd', 1 )

    GO
    ; with cte as (
    select cast(createdate as date) as Date1, * from #tempRequestForMeList )
    update dd set dd.[count] = ct.co from #tempRequestForMeList as dd inner join (select count(date1) as co, date1 from cte group by Date1) as ct on cast(dd.createDate as DATE) = ct.Date1

    select * from #tempRequestForMeList  --- if require count with each row

    go

    drop table #tempRequestForMeList
    go

If this doesn't work then show your table schema and expected output.

Note: This is for SQL server


回答2:

Try to use COUNT by PARTITION:

SELECT 
t.*
, count( CONVERT(date, t.createDate)) OVER (PARTITION BY CONVERT(date, t.createDate) 
    ORDER BY CONVERT(date, t.createDate)) CountByDate    
FROM 
@tempRequestForMeList t

Let me show an example(Thanks to @DarkRob for sample data):

DECLARE @tempRequestForMeList TABLE
(
    createDate DATETIME,
    title NVARCHAR(50),
    [count] INT
);



INSERT INTO @tempRequestForMeList
(
    createDate,
    title,
    count
)
VALUES
('2016-09-20 17:17:04.840', 'dd', 0),
('2016-09-20 17:17:04.840', 'dd', 1),
('2016-09-20 07:17:04.840', 'dd', 1),
('2016-09-20 05:17:04.840', 'dd', 1),
('2016-09-20 13:17:04.840', 'dd', 1),
('2016-09-19 12:17:04.840', 'dd', 1),
('2016-09-19 02:17:04.840', 'dd', 1),
('2016-09-19 01:17:04.840', 'dd', 1),
('2016-09-18 02:17:04.840', 'dd', 1),
('2016-09-18 03:17:04.840', 'dd', 1),
('2016-09-18 05:17:04.840', 'dd', 1),
('2016-09-18 07:17:04.840', 'dd', 1),
('2016-10-20 17:17:04.840', 'dd', 0);

and query:

SELECT 
t.*
, count( CONVERT(date, t.createDate)) OVER (PARTITION BY CONVERT(date, t.createDate) 
    ORDER BY CONVERT(date, t.createDate)) CountByDate    
FROM 
@tempRequestForMeList t

OUTPUT:

createDate                 title    count   CountByDate
2016-09-18 02:17:04.840     dd        1        4
2016-09-18 03:17:04.840     dd        1        4
2016-09-18 05:17:04.840     dd        1        4
2016-09-18 07:17:04.840     dd        1        4
2016-09-19 12:17:04.840     dd        1        3
2016-09-19 02:17:04.840     dd        1        3
2016-09-19 01:17:04.840     dd        1        3
2016-09-20 17:17:04.840     dd        0        5
2016-09-20 17:17:04.840     dd        1        5
2016-09-20 07:17:04.840     dd        1        5
2016-09-20 05:17:04.840     dd        1        5
2016-09-20 13:17:04.840     dd        1        5
2016-10-20 17:17:04.840     dd        0        1
  • 发表于 2019-03-01 01:49
  • 阅读 ( 348 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除