选择与usercolumn中每个字段的usercolumn和date的最小值不同

I have some data like this but more than 1500000 records and more than 700 users:

我有一些像这样的数据但超过1500000条记录和超过700个用户:

usercolumn   ,       datecolumn\     
a1            ,      1998/2/11\
a2            ,      1998/3/11\
a1            ,      1998/2/15\
a4            ,      1998/4/14\
a3            ,      1999/1/15\
a2            ,      1998/11/12\
a2            ,      1999/2/11\
a3            ,      2000/2/9\
a1            ,      1998/6/5\
a3            ,      1998/7/7\
a1            ,      1998/3/11\
a5            ,      1998/3/18\
a2            ,      1998/2/8\
a1            ,      1998/12/11\
a4            ,      1998/12/1\
a5            ,      1998/2/11\
....

I would like to have distinct data from usercolumn and minimum value of date for each user like this:

我想从每个用户的usercolumn和date的最小值中获得不同的数据,如下所示:

usercolumn      ,    datecolumn \        
a1              ,    1998/2/11\
a2              ,    1998/2/8\
a3              ,    1998/7/7\
a4              ,    1998/4/14\
a5              ,    1998/2/11\
....

please help me to write an SQL command to do this for oledb adapter in c#, thanks.

请帮我写一个SQL命令为c#中的oledb适配器做这个,谢谢。

6 个解决方案

#1

SELECT usercolumn, MIN(datecolumn) FROM tablename GROUP BY usercolumn;

Note that if you want other columns they must either appear in the GROUP BY clause or be constant across rows. Otherwise the result will be non-deterministic.

请注意,如果您想要其他列,则它们必须出现在GROUP BY子句中,或者在行之间保持不变。否则结果将是不确定的。

#2

This will work for SQLServer 2008 and DB2:

这适用于SQLServer 2008和DB2:

with temp as (
    select *, row_number() over (partition by usercolumn order by datecolumn) as rownum
    from table)
select * from temp 
where rownum = 1

It will give proper results even if you need to include multiple columns in the select.

即使您需要在select中包含多个列,它也会给出正确的结果。

#3

Something like this should do the tick

像这样的东西应该做的

    SELECT usercolumn
         , MIN(datecolumn)
      FROM YouTable
     GROUP BY usercolumn
         , MIN(datecolumn)

#4

If you have more than just those two columns, the best SQL to use depends a bit on what server you have at the other end of that OleDB adapter, but here's something that will work well with many (alas, not all!) such possible servers:

如果你不仅仅有这两列,那么最好使用的SQL取决于你在OleDB适配器的另一端有什么服务器,但这里有一些适用于很多(唉,不是全部!)这样的可能服务器:

SELECT t.*
FROM thetable t
LEFT JOIN thetable taux
  ON(t.usercolumn=taux.usercolumn
     AND t.datecolumn>taux.datecolumn)
WHERE taux.usecolumn IS NULL

which you could read as "emit those rows of thetable such that there is no other row of the table with the same user and a strictly-less date". If minimum date for a given user can occur multiple times this will give as many rows for that user -- if that's a problem for you, there are solutions to it, too... but I'll wait for you to clarify your question more before I work any more on this!-)

您可以将其读作“发出表格的那些行,使得表格中没有其他行具有相同的用户和严格更少的日期”。如果给定用户的最小日期可以多次出现,这将为该用户提供尽可能多的行 - 如果这对您来说是个问题,那么也有解决方案......但我会等你澄清你的问题在我继续工作之前更多! - )

#5

you could try this:

你可以试试这个:

SELECT DISTINCT a.username, a.date
FROM tablename AS a INNER JOIN tablename AS b
ON(a.username = b.username AND a.date < b.date)

As for C#, cant help you there

至于C#,无法帮助你

#6

SELECT DISTINCT USERNAME, DATE FROM TABLENAME AS A WHERE A.DATE=(SELECT MIN(DATE) FROM TABLENAME WHERE USERNAME=A.USERNAME)

SELECT DISTINCT USERNAME,DATE from TABLENAME as a a AERE A.DATE =(SELECT MIN(DATE)FROM TABLENAME WHERE USERNAME = A.USERNAME)

#1

SELECT usercolumn, MIN(datecolumn) FROM tablename GROUP BY usercolumn;