选择具有相应值的不同列名称

I have table as

我有桌子

P_Id | userid | year | month | day
-----+--------+------+-------+------
 3   |      3 | 2011 |     2 |    2
 5   |      1 | 2011 |     2 |    3
16   |      8 | 2011 |     3 |    4
 5   |      3 | 2011 |     4 |    4
17   |     1  | 2011 |     4 |    6
 8   |      4 | 2011 |     7 |    7
 9   |      3 | 2011 |     8 |    8
10   |      8 | 2011 |     9 |    9

I want to select distinct column i.e userid but also the respective value of year month and year which were encountered first.

我想选择不同的列,即userid,但也要选择首先遇到的年月和年的相应值。

For given above table following should be output

对于上面给出的表,应输出以下内容

P_Id | userid | year | month | day
-----+--------+------+-------+------
   3 |      3 | 2011 |     2 |    2
   5 |      1 | 2011 |     2 |    3
  16 |      8 | 2011 |     3 |    4
   8 |      4 | 2011 |     7 |    7

or If i am ordering the table by year,month and day userid which is encountered first must only be selected and rest must be not be selected

或者如果我按年,月和日订购表,则必须先选择首先遇到的用户ID,并且必须不选择休息

3 个解决方案

#1

Put year, month and day to native date column and do this:

将年,月和日放入本机日期列并执行以下操作:

select p_id, userid, min(the_date) from table group by p_id, userid

It will provide the fastest result.

它将提供最快的结果。

If you cant modify your table and should use year+month+day then you can convert this values to date and still use min function.

如果你不能修改你的表并且应该使用年+月+日,那么你可以将这个值转换为日期并仍然使用min函数。

#2

SELECT ta.*
FROM
    ( SELECT DISTINCT userid
      FROM tableX
    ) AS di
  JOIN
    tableX AS ta
      ON ta.P_id = 
         ( SELECT ti.P_id
           FROM tableX AS ti
           WHERE ti.userid = di.userid
           ORDER BY ti.year, ti.month, ti.day
           LIMIT 1
         )

#3

Your query is as follows;

您的查询如下;

select * from (select min(p_id)p_id,userid, min(year)year,min(month)month,min(day)day from tsil group by userid) t order by p_id;

and here is the test;

这是测试;

create table tsil(p_id int, userid int, year int, month int, day int);
insert into tsil values (3,3,2011,2,2)
                                ,(5,1,2011,2,3)
                                ,(16,8,2011,3,4)
                                ,(5,3,2011,4,4)
                                ,(17,1,2011,4,6)
                                ,(8,4,2011,7,7)
                                ,(9,3,2011,8,8)
                                ,(10,8,2011,9,9);
commit;
select * from (select max(p_id)p_id,userid, min(year)year,min(month)month,min(day)day from tsil group by userid) t order by p_id;
drop table tsil;

and here is the result; what you expected.

这是结果;你的期望。

+------+--------+------+-------+------+
| p_id | userid | year | month | day  |
+------+--------+------+-------+------+
|    3 |      3 | 2011 |     2 |    2 |
|    5 |      1 | 2011 |     2 |    3 |
|    8 |      4 | 2011 |     7 |    7 |
|   16 |      8 | 2011 |     3 |    4 |
+------+--------+------+-------+------+

#1