根据特定条件选择唯一行-Selectinguniquerowsonbasisofcertaincriteria

2  

You could group by on StartDate, EndDate, and select two ID's for the different color priorities. Then join back to the original table, preferring the high priority:

您可以在StartDate,EndDate上进行分组,并为不同的颜色优先级选择两个ID。然后加入到原始表中,优先选择高优先级:

select  b1.*
from    Trainings b1
join    (
        select  max(case when Color in (1,18) then Id end) as HighPrioID
        ,       max(case when Color not in (1,18) then Id end) as LowPrioID
        from    Trainings
        group by
                StartDate
        ,       EndDate
        ) b2
on      b1.Id = COALESCE(b2.HighPrioID, b2.LowPrioID);

Test data:

drop table if exists Trainings;
create table Trainings (id int, StartDate datetime, EndDate datetime, Color int);

insert Trainings values
(1,'2011-03-18','2011-03-19', 3),
(2,'2011-03-18','2011-03-19', 1),
(3,'2011-03-20','2011-03-21',18),
(4,'2011-03-22','2011-03-23', 3),
(5,'2011-03-20','2011-03-21', 3);

0  

SELECT DISTINCT CONCAT(StartDate, EndDate) FROM trainings

If I understood it right.

如果我明白了。

0  

Is it your mean

这是你的意思吗?

SELECT * FROM trainings WHERE color IN (1,18) GROUP BY StartDate,EndDate

0  

Assuming StartDate, EndDate and Colors results in unique records ....

假设StartDate,EndDate和Colors会产生独特的记录....

SELECT * FROM
From Trainings T
(
  SELECT
   StartDate,
   EndDate,
   MAX(CASE WHEN Color = 3 THEN 0 ELSE Color END) Color
  From Trainings
  GROUP By StartDate, EndDate
) T1 on T.StartDate = T1.StartDate AND T.EndDate = T1.EndDate AND T.Color = T1.Color

0  

You can do something like this :

你可以这样做:

select 
    t1.*,
    case
        when t2.Id is null then 1
        when t1.color in (1,18) then 2
        else 3
    end as priority
from trainings as t1
left join trainings as t2 on
    t1.StartDate = t2.StartDate and
    t1.EndDate = t2.EndDate and
    t1.Id != t2.Id
order by priority

The value of priority will help you find what you want :

优先级的值将帮助您找到您想要的东西:

• rows with priority 1 have unique start and end date

优先级为1的行具有唯一的开始和结束日期

• rows with priority 2 have 1 or 18 has color

优先级为2的行有1或18个颜色

• all other rows have priority 3

所有其他行的优先级为3