如何通过3列区分行并获取排序行

I have following table,

我有下表,

       sc_title      |      sc_date        | sc_coursecode | sc_courseno | sc_courseyear
------------------------------------------------------------------------------------------
    Open Water Diver | 2015-01-08 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-03-08 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-04-08 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-18 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-09 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-10 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-11 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-12 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2015-05-13 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-14 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2016-05-15 04:00:00 |       ow      |    07C      |    2016
        Bubble Maker | 2015-05-16 04:00:00 |       ow      |    07C      |    2015

I need those rows which title(sc_title) are like "Open Water Diver" and distinct by 3 column sc_coursecode, sc_courseno, sc_courseyear and also select only immediate future date row(sc_date). So my will be something like this,

我需要那些标题(sc_title)就像“开放水域潜水员”并且由3列sc_coursecode,sc_courseno,sc_courseyear区分的行,并且还只选择立即的未来日期行(sc_date)。所以我会这样的,

       sc_title      |      sc_date        | sc_coursecode | sc_courseno | sc_courseyear
------------------------------------------------------------------------------------------
    Open Water Diver | 2015-05-10 04:00:00 |       ow      |    05W      |    2015
    Open Water Diver | 2015-05-09 04:00:00 |       ow      |    05Z      |    2015
    Open Water Diver | 2015-05-12 04:00:00 |       ow      |    07W      |    2015
    Open Water Diver | 2016-05-15 04:00:00 |       ow      |    07C      |    2016

I have written following query which is almost works but does not satisfy my final requirement.Getting that distinct row which contains immediate future date(sc_date).

我写了下面的查询几乎可以工作,但不满足我的最终要求。获取包含即时未来日期(sc_date)的不同行。

SELECT sc_title,sc_date,sc_coursecode,sc_courseno,sc_courseyear 
FROM test_course WHERE DATE(sc_date) > DATE(NOW()) AND sc_title LIKE 'Open Water Diver%' 
GROUP BY sc_coursecode,sc_courseno,sc_courseyear ORDER BY sc_date ASC

If any one can help me then that would be great. Thanks in advance

如果任何人可以帮助我那么那将是伟大的。提前致谢

2 个解决方案

#1

Since you are looking for the immediate nearest date, use MIN on sc_date to pick the earliest one that is greater than DATE(NOW()):

由于您要查找最近的最近日期,因此请在sc_date上使用MIN来选择最早的DATE(NOW()):

SELECT
    sc_title
,   MIN(DATE(sc_date))
,   sc_coursecode
,   sc_courseno
,   sc_courseyear 
FROM test_course
WHERE DATE(sc_date) > DATE(NOW())
  AND sc_title LIKE 'Open Water Diver%' 
GROUP BY sc_coursecode, sc_courseno, sc_courseyear
ORDER BY sc_date ASC

Note that since you are not grouping by sc_title you would get an arbitrary match in the corresponding field when multiple different titles match the LIKE condition. To avoid this problem add sc_title to the GROUP BY list.

请注意,由于您没有按sc_title进行分组,因此当多个不同的标题与LIKE条件匹配时,您将在相应的字段中获得任意匹配。要避免此问题,请将sc_title添加到GROUP BY列表中。

#2

Perhapsh something like... (untested)

Perhapsh之类的......(未经测试)

SELECT A.sc_title, A.sc_date, A.sc_coursecode, A.sc_courseno, A.sc_courseyear 
FROM test_course A
INNER JOIN (SELECT min(Sc_date) SC_date, sc_title, sc_CourseCode, sc_courseno, Sc_courseYear
            FROM test_Courase
            where sc_date>=now()
            GROUP BY sc_title, sc_CourseCode, sc_courseno, Sc_courseYear) B
 on B.SC_date = A.Sc_date
and B.sc_title = A.Sc_Title
and B.Sc_CourseCode = A.SC_courseCode
and B.Sc_CourseYear = A.SC_CourseYear
WHERE A.sc_title LIKE 'Open Water Diver%'

#1