22,199
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
createDate DATETIME
,[title] NVARCHAR(20)
,[count] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2019-01-01 10:20:15',N'xxx',10)
INSERT INTO dbo.[t] VALUES(N'2019-01-01 12:50:10',N'xxx',20)
INSERT INTO dbo.[t] VALUES(N'2019-01-01 23:20:19',N'xxx',30)
INSERT INTO dbo.[t] VALUES(N'2019-01-02 10:20:15',N'xxx',40)
INSERT INTO dbo.[t] VALUES(N'2019-01-05 08:20:15',N'xxx',50)
INSERT INTO dbo.[t] VALUES(N'2019-01-05 22:20:15',N'xxx',60)
INSERT INTO dbo.[t] VALUES(N'2019-02-10 10:20:15',N'xxx',70)
INSERT INTO dbo.[t] VALUES(N'2019-02-10 11:20:15',N'xxx',80)
INSERT INTO dbo.[t] VALUES(N'2019-02-10 15:20:15',N'xxx',90)
INSERT INTO dbo.[t] VALUES(N'2019-02-15 10:20:15',N'xxx',100)
GO
--
IF OBJECT_ID('dbo.Proc_QueryEveryDay') IS NOT NULL
DROP PROC dbo.Proc_QueryEveryDay
GO
-- =============================================
-- Author: yenange
-- Create date: 2019-02-25
-- Description: 根据参数,输出每天的记录
-- =============================================
CREATE PROCEDURE dbo.Proc_QueryEveryDay
@beginT DATE,
@endT DATE
AS
BEGIN
SET NOCOUNT ON;
WHILE @beginT<dateadd(DAY,1,@endT)
BEGIN
IF EXISTS(SELECT * FROM t WHERE createDate>=@beginT AND createDate<DATEADD(DAY,1,@beginT))
BEGIN
SELECT * FROM t WHERE createDate>=@beginT AND createDate<DATEADD(DAY,1,@beginT);
END
SET @beginT=DATEADD(DAY,1,@beginT);
END
END
GO
--查询
EXEC dbo.Proc_QueryEveryDay @beginT='2019-01-01',@endT='2019-02-10'
public class FData
{
public DateTime createDate;
public string title;
public int count;
public FData(DateTime pcreateDate, string ptitle, int pcount)
{
createDate = pcreateDate;
title = ptitle;
count = pcount;
}
}
DataTable dt;
Dictionary<DateTime, List<FData>> ls;
DateTime tcreateDate;
string ttitle;
int tcount;
dt = FQuery(); // 从数据库查询,返回Datatable对象, 例如执行select createDate,title,count from [表名] where [条件]
ls = new Dictionary<DateTime, List<FData>>();
foreach(DataRow dr in dt.Rows)
{
tcreateDate = Convert.ToDateTime(dr["createDate"]);
ttitle = dr["title"].ToString();
tcount = Convert.ToInt32(dr["count"]);
if (ls.ContainsKey(tcreateDate) == false)
{
ls.Add(tcreateDate, new List<FData>());
}
ls[tcreateDate].Add(new FData(tcreateDate, ttitle, tcount));
}