当前位置： 文江博客话题详情

SQL 查询特定日期的最高值和最低值

发布于 2024-07-23 08:57:16 字数 2366 浏览 8 评论 0原文

SQL Server 2000

我的表：

CARDNO  CARDEVENTDATE   CARDEVENTTIME
121 20090610    025050
 121    20090611    040000
121 20090611    050000
121 20090611    020000
122 20090611    030001
122 20090611    030000
123 20090611    080000
123 20090611    100000
123 20090611    132449
123 20090611    025959
124 20090610    030000
124 20090612    030001
125 20090611    030002
125 20090612    040000

Cardno 是单独的表 Cardeventdate，cardeventtime是单独的表

从上表中我想获取特定cardeventdate和Cardno的顶部时间和底部时间

对于121, 20090611，顶部时间是040000，底部时间是020000 对于 123、20090611，顶部时间是 080000，底部时间是 025959 … 我需要这样的。

我使用了Min（时间）和Max（时间），但它显示如下。

卡号 – 121 卡片事件日期 – 20090611 最短时间 – 020000 最大时间 – 040000

我不想获得最小值和最大值，我只需要特定日期和卡号的顶部和底部（或）第一个和最后一个时间值。

我使用了这个查询

SELECT     RowNumber = IDENTITY (int, 1, 1), CARDNO, CARDEVENTDATE, CARDEVENTTIME INTO #Table1 FROM T_CARDEVENT SELECT     t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  FROM #Table t1 INNER JOIN (SELECT     RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO  FROM  #Table1 t  WHERE      (cardeventdate > 20090601) GROUP BY cardno, cardeventdate  UNION ALL  SELECT     MAX(RowNumber), CARDEVENTDATE, CARDNO FROM #Table1 t WHERE     (cardeventdate > 20090601) GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber

输出：

ROWNUMBER   CARDNO  CARDEVENTDATE   CARDEVENTTIME
335 0121               20090611     040000
1099    0121               20090611     050000
1100    0121               20090611     025050
336 0121               20090612     020000
337 0122               20090611     030001
338 0122               20090612     030000
339 0123               20090611     080000
1101    0123               20090611     100000
1102    0123               20090611     132449
340 0123               20090612     025959
341 0124               20090611     030000
342 0124               20090612     030001
343 0125               20090611     030002
344 0125               20090612     040000

所以这里为所有列创建行号，从中我必须如何获取特定日期的第一次和最后一次。

期望输出

CARDNO  CARDEVENTDATE   CARDEVENTTIME   Expecting
0121    20090611    040000  Top Value
0121    20090611    020000  No Need
0121    20090611    025050  Bottom Value

……等等

需要查询帮助。

原文

SQL Server 2000

My Table:

CARDNO  CARDEVENTDATE   CARDEVENTTIME
121 20090610    025050
 121    20090611    040000
121 20090611    050000
121 20090611    020000
122 20090611    030001
122 20090611    030000
123 20090611    080000
123 20090611    100000
123 20090611    132449
123 20090611    025959
124 20090610    030000
124 20090612    030001
125 20090611    030002
125 20090612    040000

Cardno is Separate Table
Cardeventdate, cardeventtime is separate table

From the above table I want to get Top Time and Bottom Time for the Particular cardeventdate and Cardno

For the 121, 20090611, Top Time is 040000, Bottom Time is 020000
For 123, 20090611, Top Time is 080000, Bottom Time is 025959 …
Like this I need.

I used Min (time) and Max (time), But it showing like this.

For CardNo – 121
Cardeventdate – 20090611
Min Time – 020000
Max Time – 040000

I don’t want to get min and Max, I need only top and Bottom (or) First and Last time value of the particular Date and Cardno.

I used this Query

SELECT     RowNumber = IDENTITY (int, 1, 1), CARDNO, CARDEVENTDATE, CARDEVENTTIME INTO #Table1 FROM T_CARDEVENT SELECT     t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  FROM #Table t1 INNER JOIN (SELECT     RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO  FROM  #Table1 t  WHERE      (cardeventdate > 20090601) GROUP BY cardno, cardeventdate  UNION ALL  SELECT     MAX(RowNumber), CARDEVENTDATE, CARDNO FROM #Table1 t WHERE     (cardeventdate > 20090601) GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber

Output:

ROWNUMBER   CARDNO  CARDEVENTDATE   CARDEVENTTIME
335 0121               20090611     040000
1099    0121               20090611     050000
1100    0121               20090611     025050
336 0121               20090612     020000
337 0122               20090611     030001
338 0122               20090612     030000
339 0123               20090611     080000
1101    0123               20090611     100000
1102    0123               20090611     132449
340 0123               20090612     025959
341 0124               20090611     030000
342 0124               20090612     030001
343 0125               20090611     030002
344 0125               20090612     040000

So Here Row Number is created for all columns, from that how I have to take First Time and Last Time for the Particular Date.

Expecting Output

CARDNO  CARDEVENTDATE   CARDEVENTTIME   Expecting
0121    20090611    040000  Top Value
0121    20090611    020000  No Need
0121    20090611    025050  Bottom Value

……… so on

Need Query Help.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

喜爱纠缠 2024-07-30 08:57:16

好吧，除非您有一些额外的字段来建立订单，否则这是不确定的。 给定 23-04-2009 的三个值 - 为什么 APPLE 是第一个而 ROSE 是最后一个？ 如果所有三个条目的 ID 和日期都相同，则没有定义过滤“GRAPHE”的顺序......

Marc

更新：我对 Lieven 的想法进行了一些扩展，并在我的设置中得到了这个工作：

DECLARE @TempTable TABLE (RowNumber INT IDENTITY(1,1), 
                          DayNumber INT, 
                          ID VARCHAR(3), DateField DATETIME, Value VARCHAR(32))

INSERT INTO @TempTable(DayNumber, id, datefield, value)
    SELECT DATEPART(DAYOFYEAR, DateField), ID, DateField, Value
    FROM @Table

SELECT * 
FROM @TempTable t
INNER JOIN 
   (SELECT RowNumber = MIN(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID
    UNION ALL 
    SELECT MAX(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID) t2 
  ON t2.RowNumber = t.RowNumber
GO

我'我基本上创建了一个带有附加信息的临时表 - 一个人工“RowNumber”来创建一些订单，“DayNumber”来获取仅按日期分组的日期（没有时间）。

似乎对我有用 - 对你也有用吗？

Well, unless you have some additional fields to establish an order, this is non deterministic. Given the three values for the 23-04-2009 - how is it that APPLE is the first and ROSE is the last? If the ID and the DATE are the same for all three entries, there's no order defined to filter out "GRAPHE"......

Marc

UPDATE: I expanded on Lieven's idea a bit and got this working in my setup:

DECLARE @TempTable TABLE (RowNumber INT IDENTITY(1,1), 
                          DayNumber INT, 
                          ID VARCHAR(3), DateField DATETIME, Value VARCHAR(32))

INSERT INTO @TempTable(DayNumber, id, datefield, value)
    SELECT DATEPART(DAYOFYEAR, DateField), ID, DateField, Value
    FROM @Table

SELECT * 
FROM @TempTable t
INNER JOIN 
   (SELECT RowNumber = MIN(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID
    UNION ALL 
    SELECT MAX(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID) t2 
  ON t2.RowNumber = t.RowNumber
GO

I'm basically creating a temp table with additional info - an artificial "RowNumber" to create some order, and the "DayNumber" to get dates grouped by date only (without time).

Seems to work ok for me - does it work for you, too?

回复 原文
像极了他 2024-07-30 08:57:16

Jash，如果您执行这个脚本，它会给出您期望的结果吗？

CREATE TABLE #T_Cardevent (CARDNO VARCHAR(3), CARDEVENTDATE VARCHAR(8), CARDEVENTTIME VARCHAR(8))

INSERT INTO #T_Cardevent VALUES ('121', '20090610', '025050')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '040000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '050000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '020000')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030001')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '080000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '100000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '132449')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '025959')
INSERT INTO #T_Cardevent VALUES ('124', '20090610', '030000')
INSERT INTO #T_Cardevent VALUES ('124', '20090612', '030001')
INSERT INTO #T_Cardevent VALUES ('125', '20090611', '030002')
INSERT INTO #T_Cardevent VALUES ('125', '20090612', '040000')

SELECT     
  RowNumber = IDENTITY (int, 1, 1)
  , CARDNO
  , CARDEVENTDATE
  , CARDEVENTTIME 
INTO #Table
FROM #T_CARDEVENT 

SELECT t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  
FROM #Table t1 
     INNER JOIN (
        SELECT RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t  
        GROUP BY cardno, cardeventdate  
        UNION ALL SELECT MAX(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t 
        GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber
ORDER BY 1, 2, 3

DROP TABLE #Table
DROP TABLE #T_Cardevent

Jash, if you execute this script, does it give you the results you'd expect?

CREATE TABLE #T_Cardevent (CARDNO VARCHAR(3), CARDEVENTDATE VARCHAR(8), CARDEVENTTIME VARCHAR(8))

INSERT INTO #T_Cardevent VALUES ('121', '20090610', '025050')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '040000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '050000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '020000')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030001')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '080000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '100000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '132449')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '025959')
INSERT INTO #T_Cardevent VALUES ('124', '20090610', '030000')
INSERT INTO #T_Cardevent VALUES ('124', '20090612', '030001')
INSERT INTO #T_Cardevent VALUES ('125', '20090611', '030002')
INSERT INTO #T_Cardevent VALUES ('125', '20090612', '040000')

SELECT     
  RowNumber = IDENTITY (int, 1, 1)
  , CARDNO
  , CARDEVENTDATE
  , CARDEVENTTIME 
INTO #Table
FROM #T_CARDEVENT 

SELECT t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  
FROM #Table t1 
     INNER JOIN (
        SELECT RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t  
        GROUP BY cardno, cardeventdate  
        UNION ALL SELECT MAX(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t 
        GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber
ORDER BY 1, 2, 3

DROP TABLE #Table
DROP TABLE #T_Cardevent
回复 原文
謌踐踏愛綪 2024-07-30 08:57:16

我还没有尝试过，但也许这样的事情可以做到：

SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date ASC
UNION
SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date Desc

I havn't tried it, but maybe something like this might do it:

SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date ASC
UNION
SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date Desc
回复 原文
陌伤浅笑 2024-07-30 08:57:16

为了作弊，你可以添加一个自动编号字段
自动识别日期值
1 001 23:04:2009 苹果
2 001 23:04:2009 石墨
3 001 23:04:2009 玫瑰
4 001 24:04:2009 浆果
5 001 24:04:2009 蒂芙尼
6 001 24:04:2009 器官
7 001 24:04:2009 SILVER

然后你可以对其进行最小值和最大值

我通常使用插入到用 ID 定义的临时表中，从技术上讲，插入顺序不受保证。

to cheat you can add an autonumber feild
AUtoID ID DATE VALUE
1 001 23:04:2009 APPLE
2 001 23:04:2009 GRAPHE
3 001 23:04:2009 ROSE
4 001 24:04:2009 BERRY
5 001 24:04:2009 TIFFANY
6 001 24:04:2009 ORGANE
7 001 24:04:2009 SILVER

You can then do min and max against it

I usually use insert into a tempory table which is defined with the ID, technically the insertion order isn't garenteed going in.

回复 原文
白色秋天 2024-07-30 08:57:16

这是使用内联视图的解决方案：

with 
tempFirst as (
    select id, date, value, 
     row_number() over (partition by id, date order by date asc) as rownum1
    from table1
),
tempLast as ( 
    select *, 
     row_number() over (partition by id, date order by rownum1 desc) as rownum2 
    from tempFirst
)
select id, date, value from tempFirst where rownum1 = 1
union
select id, date, value from tempLast where rownum2 = 1

我已经测试了输出，即：

ID  DATE       VALUE
001 2009-04-23 APPLE
001 2009-04-23 ROSE
001 2009-04-24 BERRY
001 2009-04-24 SILVER

Here's the solution using inline views:

with 
tempFirst as (
    select id, date, value, 
     row_number() over (partition by id, date order by date asc) as rownum1
    from table1
),
tempLast as ( 
    select *, 
     row_number() over (partition by id, date order by rownum1 desc) as rownum2 
    from tempFirst
)
select id, date, value from tempFirst where rownum1 = 1
union
select id, date, value from tempLast where rownum2 = 1

I have tested the output, which is:

ID  DATE       VALUE
001 2009-04-23 APPLE
001 2009-04-23 ROSE
001 2009-04-24 BERRY
001 2009-04-24 SILVER
回复 原文
~没有更多了~

关于作者

桜花祭

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

淡笑忘祈一世凡恋

文章 0 评论 0

我们的影子

文章 0 评论 0

素年丶

文章 0 评论 0

南笙

文章 0 评论 0

18215568913

文章 0 评论 0

qq_xk7Ean

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文