SQL 为 usercolumn 中的每个字段选择与 usercolumn 不同的日期和最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1021973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select distinct from usercolumn and minimum value of dates for each field in usercolumn
提问by brian-brazil
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 中获得不同的数据，并为每个用户提供最小值，如下所示：
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 适配器执行此操作，谢谢。
回答by brian-brazil
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 子句中或跨行保持不变。否则结果将是不确定的。
回答by SO User
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.
即使您需要在选择中包含多列，它也会给出正确的结果。
回答by Dmitri Kouminov
Something like this should do the tick
像这样的事情应该做勾号
SELECT usercolumn
, MIN(datecolumn)
FROM YouTable
GROUP BY usercolumn
, MIN(datecolumn)
回答by Alex Martelli
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!-)
您可以将其读作“发出表格的那些行，以便表格中没有其他行具有相同的用户和严格的日期”。如果给定用户的最小日期可以多次出现，这将为该用户提供尽可能多的行——如果这对你来说是个问题，那么也有解决方案......但我会等你澄清你的问题在我对此进行更多工作之前，请先了解更多！-)
回答by mrt181
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#，在那里帮不了你
回答by mrt181
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 WHERE A.DATE=(SELECT MIN(DATE) FROM TABLENAME WHERE USERNAME=A.USERNAME)
