大家好, 通过以下查询,我试图从表中提取一些数据:
SELECT c.CHANGEDBY,c.CODE,c.CHANGEDAT FROM logs c WHERE c.CLIENT = ' 00' AND c.DB = ' DBNAME' AND c.CLASS = ' CLASSNAME' AND c.FUNCTION = ' FUNCNAME'该表由以下列组成 ID(int),DB(varchar ),CLIENT(varchar),CLASS(varchar),FUNCTION(varchar),CODE(varchar),CREATEDBY(varchar),CREATEDAT(datetime),CHANGEDBY(varchar),CHANGEDAT(datet) ime) 当我运行此查询时,我有输出:
CHANGEDBY 代码 CHANGEDAT John ----------- xyz ----- 2012-07-09 10:04:00 Ben ------------ abc ----- 2012-07-09 08:04:00 沃尔特--------- def-- --- 2012-07-08 07:15:00 Kate ----------- ghf ----- 2012-07-08 11:35:00---------------------------------------- -------------------------- 我想要的是在这个选择查询中我需要每天的最新日期时间。所以输出应该是这样的:
CHANGEDBY 代码 CHANGEDAT $ b $约翰----------- xyz ----- 2012-07-09 10:04:00 Kate ----------- ghf ---- -2012-07-08 11:35:00当我使用MAX(日期)时它只带1,所以有人可以帮我搞清楚。 谢谢
解决方案嘿试试下面的T-SQL代码: 创建 表 #TEMP ( ID INT , CHANGEDATE DATETIME ) INSERT INTO #TEMP VALUES ( 1 ,GETDATE()) INSERT INTO #TEMP VALUES ( 2 ,' 2013-01-09 10:04:00') INSERT INTO #TEMP VALUES ( 4 ,GETDATE()) INSERT INTO #TEMP VALUES ( 3 ,' 2013-01-09 08:11:00') SELECT DISTINCT ID,CHANGEDATE FROM ( SELECT ID,CHANGEDATE,ROW_NUMBER() OVER ( PARTITION BY CONVERT ( VARCHAR ( 10 ),CHANGEDATE, 103 ) ORDER BY CHANGEDATE DESC ) AS ROWNUM FROM #TEMP )TMP WHERE ROWNUM = 1 DROP TABLE #TEMP
SELECT C.ChangedBy,C.Code,C.ChangeDate FROM dbo.logs c WITH ( NOLOCK ) WHERE C.ChangeDate IN ( SELECT MAX(C.ChangeDate) FROM dbo.logs c WITH ( NOLOCK ) WHERE CONVERT ( DATE ,, C.ChangeDate)= CONVERT ( DATE ,, C.ChangeDate) GROUP CONVERT ( DATE ,, C.ChangeDate)) AND c.CLIENT = '00' AND c.DB ='DBNAME' AND c.CLASS ='CLASSNAME' AND c。 FUNCTION = 'FUNCNAME'
尝试这个,你将获得数据
实现这一目标的最佳方法是创建store d程序 [ ^ ],就像这样: USE YourDatabase; GO CREATE PROCEDURE GetLatestDailyChange @ client VARCHAR ( 10 ), @ db VARCHAR ( 30 ), @ class VARCHAR ( 30 ), @ function ( 30 ) AS SELECT CHANGEDBY,CODE, CHANGEDAT FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY CHANGEDATE ORDER BY CHANGEDATE DESC ) AS ID,* FROM 记录 WHERE CLIENT = @ client AND T.DB =@db AND T. CLASS = @ class AND T.FUNCTION=@functions) AS T WHERE T.ID = 1 GO
有关的更多信息CREATE PROCEDURE [ ^ ] commad。 创建SP后,您需要知道如何调用它。例如:
EXEC YourDatabase.GetLatestDailyChange @ client = N ' 00', @ db = N ' DBNAME',@ class = N ' CLASSNAME',@ function = N ' FUNCNAME';
Hello all, With the following query I am trying to pull some data from a table:
SELECT c.CHANGEDBY, c.CODE, c.CHANGEDAT FROM logs c WHERE c.CLIENT='00' AND c.DB='DBNAME' AND c.CLASS='CLASSNAME' AND c.FUNCTION='FUNCNAME'The table consists of the following columns ID(int), DB(varchar), CLIENT(varchar), CLASS(varchar),FUNCTION(varchar), CODE(varchar), CREATEDBY(varchar), CREATEDAT(datetime), CHANGEDBY(varchar), CHANGEDAT(datetime) When I run this query I have the output:
CHANGEDBY CODE CHANGEDAT John-----------xyz-----2012-07-09 10:04:00 Ben------------abc-----2012-07-09 08:04:00 Walter---------def-----2012-07-08 07:15:00 Kate-----------ghf-----2012-07-08 11:35:00------------------------------------------------------------------ What I want is I need the latest datetime for each day in this select query. So the output should be like:
CHANGEDBY CODE CHANGEDAT John-----------xyz-----2012-07-09 10:04:00 Kate-----------ghf-----2012-07-08 11:35:00When I use MAX(Date) it only brings 1 so can someone help me figure it out. Thanks
解决方案 hey try below T-SQL Code : CREATE TABLE #TEMP ( ID INT, CHANGEDATE DATETIME ) INSERT INTO #TEMP VALUES(1,GETDATE()) INSERT INTO #TEMP VALUES(2,'2013-01-09 10:04:00') INSERT INTO #TEMP VALUES(4,GETDATE()) INSERT INTO #TEMP VALUES(3,'2013-01-09 08:11:00') SELECT DISTINCT ID, CHANGEDATE FROM ( SELECT ID,CHANGEDATE,ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(10),CHANGEDATE,103) ORDER BY CHANGEDATE DESC) AS ROWNUM FROM #TEMP ) TMP WHERE ROWNUM = 1 DROP TABLE #TEMPSELECT C.ChangedBy, C.Code, C.ChangeDate FROM dbo.logs c WITH(NOLOCK) WHERE C.ChangeDate IN ( SELECT MAX(C.ChangeDate) FROM dbo.logs c WITH(NOLOCK) WHERE CONVERT(DATE, , C.ChangeDate) = CONVERT(DATE, , C.ChangeDate) GROUP CONVERT(DATE, , C.ChangeDate) ) AND c.CLIENT = ‘00’ AND c.DB = ‘DBNAME’ AND c.CLASS = ‘CLASSNAME’ AND c.FUNCTION = ‘FUNCNAME’try this, you will get the data
The best way to achieve that is to create stored procedure[^], like this one: USE YourDatabase; GO CREATE PROCEDURE GetLatestDailyChange @client VARCHAR(10), @db VARCHAR(30), @class VARCHAR(30), @function (30) AS SELECT CHANGEDBY, CODE, CHANGEDAT FROM( SELECT ROW_NUMBER() OVER(PARTITION BY CHANGEDATE ORDER BY CHANGEDATE DESC) AS ID, * FROM logs WHERE CLIENT=@client AND T.DB=@db AND T.CLASS=@class AND T.FUNCTION=@functions) AS T WHERE T.ID = 1 GOMore about CREATE PROCEDURE[^] commad. After SP creation, you need to know how to call it. For example:
EXEC YourDatabase.GetLatestDailyChange @client = N'00', @db = N'DBNAME', @class=N'CLASSNAME', @function=N'FUNCNAME';更多推荐
在给定时间内每天获取最新的日期时间
发布评论