这是我的表:
CREATE TABLE [dbo].[InfoTable]
(
[Id] INT IDENTITY (1, 1) NOT NULL,
[TimeSpanColumn] NVARCHAR(50) NULL,
[TimeStampColumn] DATETIME NULL,
[TimeStringColumn] NVARCHAR(50) NULL,
[TotalSecColumn] NVARCHAR(50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
)
我想在两个给定日期之间选择时间spans
,并将其子串到另一个表中,如下所示:
Col1 Col2 col3
[hh] | [mm] | [ss]
----------------------
02 55 36
54 32 41
我的存储过程:
CREATE PROCEDURE sumBetweenDates
@QueryFromDate DateTime,
@QueryToDate DateTime
AS
SELECT
SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh
FROM
InfoTable
WHERE
TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
GROUP BY
TimeStampColumn
SELECT
SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) AS mm
FROM
InfoTable
WHERE
TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
GROUP BY
TimeStampColumn
SELECT
SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) AS ss
FROM
InfoTable
WHERE
TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate
GROUP BY
TimeStampColumn
它只返回第一个查询的结果。例如:
[hh]
02
54
我怎么能做到我说的?
我无法将变量分配给那些select语句,因为在它们被timestampcolumn
分组之后,它们可能有多个值
给定您所说的期望结果,我认为您只需要一个具有所有3个聚合的SELECT
:
SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh,
SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm,
SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
FROM InfoTable
where TimeStampColumn between @QueryFromDate and @QueryToDate
GROUP BY TimeStampColumn
但是你的评论:
我只是在c#程序中执行了存储过程,并用只显示一列的结果填充了我的datagridview。
如果您有多个SELECT
语句,则必须格外注意如何阅读它们。如果您使用的是SqlDataReader
,则有NextResult
方法用于从一个结果集前进到下一个结果集中。如果使用某种形式的数据适配器或类似的适配器,则需要注意它如何描述如何使用多个结果集。例如,您正在寻找填充DataSet
或DataTable
的内容。
如果要计算列之间的数据,只需创建一个临时表来存储存储过程的结果并计算所需的值。
让我举一个例子。
-
创建存储过程
CREATE PROCEDURE sumBetweenDates @QueryFromDate DateTime, @QueryToDate DateTime AS SELECT DISTINCT * FROM (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9), (10, 11, 12)) AS X(a, a1, a2) /* --Insert your query here instead of above test values. SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh, SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm, SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss FROM InfoTable WHERE TimeStampColumn between @QueryFromDate and @QueryToDate GROUP BY TimeStampColumn */
-
创建一个表变量来存储存储过程的结果,然后插入存储过程
sumBetweenDates '2018-12-1', '2018-12-1'
中的数据。然后在SELECT
语句中,你可以做任何你想做的事情:Declare @T Table (col1 INT, col2 INT, col3 int) Insert @T Exec sumBetweenDates '2018-12-1', '2018-12-1' SELECT t.col1 , t.col2 , t.col3 , t.col3 - t.col1 CalculatedColumn FROM @T t
这里是@T
是一个表变量。Declare @T Table (col1 INT, col2 INT, col3 int)
。你可以通过阅读这篇很酷的文章来了解它们之间的区别。
这个答案将帮助您如何使用exec stored procedure
将数据插入表中。
此外,您还可以阅读MSDN上关于使用EXEC语句进行INSERT的文章。
您的存储过程只包含第一个SELECT
语句,因为您没有使用BEGIN
/END
:
CREATE PROCEDURE sumBetweenDates (
@QueryFromDate DateTime,
@QueryToDate DateTime
) AS
BEGIN
SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh
FROM InfoTable
WHERE TimeStampColumn between @QueryFromDate and @QueryToDate
GROUP BY TimeStampColumn;
SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm
FROM InfoTable
WHERE TimeStampColumn BETWEEN @QueryFromDate and @QueryToDate
GROUP BY TimeStampColumn;
SELECT SUM(CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
FROM InfoTable
WHERE TimeStampColumn BETWEEN @QueryFromDate and @QueryToDate
GROUP BY TimeStampColumn;
END; -- sumBetweenDates
我不太确定你想要什么。根据样本数据,您似乎想要:
CREATE PROCEDURE sumBetweenDates (
@QueryFromDate DateTime,
@QueryToDate DateTime
) AS
BEGIN
SELECT DISTINCT CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2))) as hh,
CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2))) as mm,
CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2))) as ss
FROM InfoTable
WHERE TimeStampColumn BETWEEN @QueryFromDate AND @QueryToDate;
END;
然而,这似乎并不是特别有用,人们可能会猜测你想总结一些东西。