从每个组的第一行和最后一行获取数据

I've found many similar topics to this but none I can understand well enough to solve my specific case.

我发现了很多类似的主题,但是我无法理解我的具体案例。

A have a table with the following basic structure:

A有一个具有以下基本结构的表:

+------------------------+
| id | session ID | bal  |
+------------------------+
| 0  | 00000002 | 100    |
| 1  | 00000002 | 120    |
| 2  | 00000002 | 140    |
| 3  | 00000001 | 900    |
| 4  | 00000001 | 800    |
| 5  | 00000001 | 500    |
+------------------------+ 

I need to create a (Microsoft SQL) query which returns each unique sessionID along with the first ("start") and last ("end") bal entries based on sequencial value of the ID column. The result would look like this:

我需要创建一个(Microsoft SQL)查询,该查询根据ID列的顺序值返回每个唯一的sessionID以及第一个(“开始”)和最后一个(“结束”)bal条目。结果如下所示:

+---------------------------+
| session ID | start | end  |
+---------------------------+
| 00000002   | 100   | 140  |
| 00000001   | 900   | 500  |
+---------------------------+

How can I achieve this?

我怎样才能做到这一点?

5 个解决方案

#1

EDIT In reply to your comment, SQL Server supports window functions. One way to look up the first and last bal values per Session ID is:

编辑在回复您的评论时,SQL Server支持窗口功能。查找每个会话ID的第一个和最后一个bal值的一种方法是:

select  distinct [Session ID]
,       first_value(bal) over (partition by [Session ID] order by id) as [start]
,       first_value(bal) over (partition by [Session ID] order by id desc) as [end]
from    Table1

Example at SQL Fiddle.

SQL Fiddle的例子。

Another way (there are many) is increasing and decreasing row numbers:

另一种方式(有很多)是增加和减少行数:

select  [Session ID]
,       max(case when rn1 = 1 then bal end) as [start]
,       max(case when rn2 = 1 then bal end) as [end]
from    (
        select  row_number() over (partition by [Session ID] order by id) as rn1
        ,       row_number() over (partition by [Session ID] order by id desc) as rn2
        ,       *
        from    Table1
        ) as SubQueryAlias
group by
        [Session ID]

Example at SQL Fiddle.

SQL Fiddle的例子。

#2

You can use JOIN and Common Table Expression for readability:

您可以使用JOIN和Common Table Expression来提高可读性:

with CTE as
(
    select 
        sessionId, min(id) as firstId, max(id) as lastId
    from 
        log
    group by sessionId
)
select
    CTE.sessionId, Log1.bal as start, Log2.bal as [end]
from
    CTE
    join Log as Log1 on Log1.id = CTE.firstId
    join Log as Log2 on Log2.id = CTE.lastId

See the SQL Fiddle.

请参阅SQL小提琴。

#3

I assume bal is numeric (although it doesn't have to be in this case, as all entries have length 3)

我假设bal是数字的(虽然它不一定是这种情况,因为所有条目的长度都是3)

select sessionID
     , min(bal) as start
     , max(bal) as end
from table_name
group by sessionID

where "table_name" is the name of your table

其中“table_name”是表的名称

#4

In MySQL may be like this:

在MySQL中可能是这样的:

SELECT `session ID`, MIN(bal) AS start, MAX(bal) AS end FROM `yourtable` WHERE `session ID` IN (
    SELECT DISTINCT(`session ID`) FROM `yourtable`
);

#5

SELECT FIRST(column_name), LAST(column_name) FROM table_name; http://forums.mysql.com/read.php?65,363723,363723

SELECT FIRST(column_name),LAST(column_name)FROM table_name; http://forums.mysql.com/read.php?65,363723,363723