Проблема с вашим опубликованным запросом заключается в том, что вы не группируете подзапрос. В результате подразумевается группа по всему набору записей и возвращается неопределенное значение для идентификатора драйвера. Это, в свою очередь, означает, что внешний запрос найдет только driver_id неопределенного значения, возвращенного подзапросом.
Дано
drop table if exists t;
create table t
(drive_id int, driver_id int, date_time date, start_state varchar(1), end_state varchar(1));
insert into t values
(1,1,'2019-03-31','a','b'),
(1,1,'2019-03-31','a','b'),
(1,1,'2019-03-31','b','c'),
(1,2,'2019-03-31','a','b'),
(1,2,'2019-03-31','a','b'),
(1,3,'2019-03-31','a','b'),
(1,3,'2019-03-31','a','b'),
(1,4,'2019-03-31','a','b');
Подзапрос
SELECT
COUNT(*) AS drive_counts,
driver_id
FROM t
ORDER BY drive_counts DESC
LIMIT 3
Возвращает
+--------------+-----------+
| drive_counts | driver_id |
+--------------+-----------+
| 8 | 1 |
+--------------+-----------+
1 row in set (0.00 sec)
Исправление подзапроса для группировки по идентификатору драйвера (и выгрузка неприятного с помощью предложение в пользу объединения)
SELECT
start_state,
end_state,
t.driver_id,
COUNT(*) AS n_drives
FROM t
JOIN (
SELECT
COUNT(*) AS drive_counts,
driver_id
FROM t
group by driver_id
ORDER BY drive_counts DESC
LIMIT 3
) AS top_three_drivers on top_three_drivers.driver_id = t.driver_id
GROUP BY start_state, end_state, t.driver_id
order by t.driver_id,start_state;
Результаты в
+-------------+-----------+-----------+----------+
| start_state | end_state | driver_id | n_drives |
+-------------+-----------+-----------+----------+
| a | b | 1 | 2 |
| b | c | 1 | 1 |
| a | b | 2 | 2 |
| a | b | 3 | 2 |
+-------------+-----------+-----------+----------+
4 rows in set (0.00 sec)
Что я и ожидал. Кстати, вопрос плохой, поскольку он не учитывает ничьи - в этом примере драйверы 2 и 3 имеют одинаковое количество дисков, поэтому, возможно, драйвер 4 также должен появиться.
У Вас есть две опции здесь - Queryable.Union
, или комбинация выражения. Я обычно одобрял бы последнего, через OrElse
- который (с LINQ-SQL, по крайней мере) можно сделать с 2 выражениями (см. ниже) - но в любом случае это должно быть составлено:
using(var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out;
Expression<Func<Customer, bool>> lhs =
x => x.Country == "UK";
Expression<Func<Customer, bool>> rhs =
x => x.ContactName.StartsWith("A");
var arr1 = ctx.Customers.Where(
lhs.OrElse(rhs)).ToArray();
var arr2 = ctx.Customers.Where(lhs)
.Union(ctx.Customers.Where(rhs)).ToArray();
}
Оба arr1
и arr2
каждый только выполняет 1 хит базы данных (хотя TSQL отличается; первое имеет OR
в WHERE
пункт; второе имеет два отдельных запроса с UNION
).
Вот дополнительный метод, который я использовал:
static Expression<Func<T, bool>> OrElse<T>(
this Expression<Func<T, bool>> lhs,
Expression<Func<T, bool>> rhs)
{
var row = Expression.Parameter(typeof(T), "row");
var body = Expression.OrElse(
Expression.Invoke(lhs, row),
Expression.Invoke(rhs, row));
return Expression.Lambda<Func<T, bool>>(body, row);
}