如何获取第一行表单 SQL 组查询? [英] How To get the First Row Form SQL Group Query?

问题描述

我在编写查询时遇到问题.

I have a problem in writing a query.

我想选择分组的每组行的第一行

I'd like to select the first row of each set of rows grouped

我的表是Transactions:

userID | Date     | StoreID 
---------------------------
     1 | 8-9-2013 | 10
     1 | 9-9-2013 | 10
     1 | 10-9-2013| 20
     2 | 7-9-2013 | 30
     2 | 8-9-2013 | 10
     2 | 9-9-2013 | 20
     1 | 11-9-2013| 10
     2 | 10-9-2013| 20

我尝试使用这个 SQL 语句:

and I try to this SQL statement:

Select 
    tr.userID , Min(tr.TransactionDate) FirstDate
From 
    Transactions tr 
Group By 
    tr.userID 

我得到这个输出:

userID | Date     
------------------
     1 | 8-9-2013 
     2 | 7-9-2013

但我需要在每个第一笔交易中使用 Store ID.

But I need the Store ID in every first transaction.

我需要这样

 userID | Date    |  StoreID
-------------------------
     1 | 8-9-2013 |  10
     2 | 7-9-2013 |  30

请大家帮帮我

推荐答案

SQL Fiddle

MS SQL Server 2008 架构设置:

CREATE TABLE Transactions
    ([userID] int, [Date] datetime, [StoreID] int)
;

INSERT INTO Transactions
    ([userID], [Date], [StoreID])
VALUES
    (1, '2013-08-09 00:00:00', 10),
    (1, '2013-09-09 00:00:00', 10),
    (1, '2013-10-09 00:00:00', 20),
    (2, '2013-07-09 00:00:00', 30),
    (2, '2013-08-09 00:00:00', 10),
    (2, '2013-09-09 00:00:00', 20),
    (1, '2013-11-09 00:00:00', 10),
    (2, '2013-10-09 00:00:00', 20)
;

查询 1:

SELECT
    tr.userID , Min(tr.Date) FirstDate , tr2.storeid
FROM
    Transactions tr
inner join Transactions tr2 on tr.userid = tr2.userid and 
                              tr2.date = (select top 1 date 
                                          from transactions t 
                                          where t.userid = tr2.userid
                                          order by date asc)
GROUP BY
    tr.userID, tr2.storeid

结果:

| USERID |                     FIRSTDATE | STOREID |
|--------|-------------------------------|---------|
|      1 | August, 09 2013 00:00:00+0000 |      10 |
|      2 |   July, 09 2013 00:00:00+0000 |      30 |

这篇关于如何获取第一行表单 SQL 组查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！