Есть ли способ показать предложение WHERE только для одного поля в MySQL?

У меня есть несколько курсов, на которые студенты могут зарегистрироваться. Существует таблица пользователей (usuarios), таблица курсов (cursos) и третья таблица, в которой показана каждая учащаяся учащихся (cursosUsuarios), например:

CREATE TABLE usuarios( userID int unsigned not null auto_increment primary key, userName char(50) null, )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE cursos ( cursoID int unsigned not null auto_increment primary key, nombreCurso char(100) not null, estadoCurso char(50) not null, )ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE cursosUsuarios ( cursosUsuariosID int unsigned not null auto_increment primary key, userID int not null, cursoID int not null, userHabilitado int(1) not null DEFAULT '0', )ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Каждый пользователь может быть зарегистрирован в каждом курсе, но до тех пор, пока он не будет включен администратором, он не сможет получить доступ к классу. Включение означает, что поле «userHabilitado» равно «1», а не по умолчанию «0».

Теперь я пытаюсь показать список всех доступных курсов в профиле пользователя, чтобы администратор мог видеть, в каких из них пользователь зарегистрировал и изменил значение «0» на «1» в поле «userHabilitado».

Это мой запрос:

 SELECT cursos.cursoID AS idcurso, cursos.estadoCurso, cursosUsuarios.userHabilitado AS 'ok', GROUP_CONCAT(cursosUsuarios.userID SEPARATOR ',') AS 'usuarios' FROM cursos LEFT JOIN cursosUsuarios ON cursos.cursoID = cursosUsuarios.cursoID LEFT JOIN usuarios ON cursosUsuarios.userID = usuarios.userID WHERE cursos.estadoCurso='abierto' GROUP BY cursos.cursoID; 

Это результат, который у меня есть:

введите описание изображения здесь

Теперь, когда мне нужно показать все открытые курсы (курсы, в которых для поля estadoCurso установлено значение «abierto»), я не добавил предложение WHERE для фильтрации результатов пользователем.

То, что я делаю, – это взять идентификатор пользователя из URL-адреса и взорвать поле «usuarios», чтобы найти пользователя, которого я ищу, например:

  echo '<div class="checkbox">'; while ($curso=mysqli_fetch_assoc($buscarCurso)) { $usuario = $_GET['userID']; $usuarios = explode(',', $curso['usuarios']); ?> <input type="checkbox" <?php if (in_array($usuario, $usuarios)) {echo 'checked';}?> name="cursoID[]" value="<?php echo $curso['idcurso'];?>"> <?php echo '<b>'.$curso['nombreCurso'].'</b> ';?> <?php echo 'Id. curso: '.$curso['idcurso'].' <b>Estado usuario:</b> <input type="text" name="ok['.$curso['idcurso'].']" value ="'.$curso['ok'].'">';?> 

Итак, если идентификатор пользователя находится в массиве $ usuarios, флажок установлен, так как это означает, что пользователь зарегистрирован.

Моя проблема заключается в том, как показать поле userHabilitado в этом списке. На изображении выше пользователь 70 зарегистрирован в трех курсах: идентификаторы 15, 16 и 18 курса. В каждом случае поле userHabilitado установлено в «1», но результат там неправильный.

Я хочу заменить cursosUsuarios.userHabilitado AS 'ok' на что-то вроде: cursosUsuarios.userHabilitado WHERE usuarios.userID = 70 , но внутри этого поля.

Возможно ли это в MySQL?

До MySQL 5.7 по умолчанию было разрешено не FULL group by . Это означает, что вы можете иметь группу с помощью (которая использует агрегированные функции, такие как sum и max и count и group_concat ), с другими неагрегированными столбцами (назовем их NON AGGS ), так как ваши первые 3 показаны не всей частью вашего предложения group by . Это позволило, но результаты, как правило, будут работать следующим образом:

  • Он отлично работал, потому что вы хорошо знаете свои данные и пытаетесь достичь

  • Это получилось ужасно, потому что это был snafu

До 5.7, ONLY_FULL_GROUP_BY существовал, но по умолчанию он был отключен.

Таким образом, в MySQL 5.7 по умолчанию ONLY_FULL_GROUP_BY умолчанию включен. Как таковой, если вы NON AGGS создать группу, но с не всеми NON AGGS в предложении group by вы получите сообщение об ошибке.

Рассмотрим следующую проблему в п. 5.6 ниже:

 create table thing ( col1 int not null, col2 int not null, age int not null ); insert thing(col1,col2,age) values (1,2,10), (1,3,20), (2,3,20), (2,2,10); select col1,col2,max(age) from thing group by col1; +------+------+----------+ | col1 | col2 | max(age) | +------+------+----------+ | 1 | 2 | 20 | | 2 | 3 | 20 | +------+------+----------+ 

То, что происходит выше, – это не все NON AGGS в group by . Он возвращает max (возраст) на col1. Но так как col2 не был в group by , он использовал индекс кластеров или физический порядок и приносил его, непреднамеренно, возможно (snafu, ошибка), неправильное значение для col2. В зависимости от ваших намерений или знания ваших данных или даже заботы. Двигателю все равно. возможно, вы это делаете.

Чтобы избежать этих распространенных ошибок или непреднамеренных данных, MySQL 5.7 по умолчанию ONLY_FULL_GROUP_BY .

В вашем случае неправильные строки составляют ваши результаты, предположительно, для столбцов 2 и 3.

См. «Ручная страница», озаглавленная « Обработка MySQL GROUP BY» .


Пример 2.

 -- drop table if exists person; create table person ( id int auto_increment primary key, firstName varchar(100) not null, lastName varchar(100) not null ); -- drop table if exists fruitConsumed; create table fruitConsumed ( id int auto_increment primary key, theDate date not null, fruitId int not null, -- does not really matter. Say, 1=apple, 2=orange from some other table personId int not null, qty int not null ); -- truncate table person; insert person (firstName,lastName) values ('Dirk','Peters'), ('Dirk','Smith'), ('Jane','Billings'); -- truncate table fruitConsumed; insert fruitConsumed (theDate,fruitId,personId,qty) values ('2016-10-31',1,1,2), ('2016-10-31',2,1,5), ('2016-10-31',2,2,12), ('2016-11-02',2,2,3); 

Запрос:

 select p.firstName,p.lastName,sum(fc.qty) from person p join fruitConsumed fc on fc.personId=p.id group by p.firstName,p.lastName; +-----------+----------+-------------+ | firstName | lastName | sum(fc.qty) | +-----------+----------+-------------+ | Dirk | Peters | 7 | | Dirk | Smith | 15 | +-----------+----------+-------------+ 

Вышеизложенное отлично работает на MySQL 5.6 и 5.7 независимо от установки ONLY_FULL_GROUP_BY

теперь рассмотрим

 select p.firstName,p.lastName,sum(fc.qty) from person p join fruitConsumed fc on fc.personId=p.id group by p.firstName; +-----------+----------+-------------+ | firstName | lastName | sum(fc.qty) | +-----------+----------+-------------+ | Dirk | Peters | 22 | +-----------+----------+-------------+ 

Вышеприведенное часто бывает приемлемым для MySQL 5.6 без ONLY_FULL_GROUP_BY и сбой на 5.7 с включенным ONLY_FULL_GROUP_BY (ошибка 1055). Вышеуказанный результат в основном тарабарщина. Но ниже это объясняется несколько:

Мы знаем, что Дирк, Дирк, только один Дирк, единственный, кто выжил во внутреннем соединении. Есть 2 Дирка. Но из-за group by p.firstName у нас остается только один Dirk. Нам нужно lastName . Из-за несоответствия
SQL Standard, MySQL может разрешить это с ONLY_FULL_GROUP_BY . Поэтому он просто выбирает любое старое lastName. Ну, первый, который он находит, и это либо в кеше, либо в физическом порядке.

И он пошел с Петерсом. Сумма счетчика фруктов для всех Дирков.

Поэтому, если вы ONLY_FULL_GROUP_BY такой код, несоответствие не ONLY_FULL_GROUP_BY дает вам тарабарщину.

И, как было сказано, MySQL 5.7 кораблей по умолчанию не допускает этого. Но он выбирается по-старому, если вы выберете.

Настоятельно рекомендуется исправить ваши запросы и оставить ONLY_FULL_GROUP_BY включенным.

Ваш запрос противоречит стандарту sql, потому что у вас есть поля в списке выбора, которые не входят в предложение group by, и не являются агрегированными функциями.

В частности, у вас есть cursosUsuarios.userHabilitado AS 'ok' в списке выбора, но вы только группируете его в поле cursos.cursoID . Это означает, что, если вы зарегистрировали не участвующих в курсе студентов, все они будут показаны в качестве зачисленных или не зарегистрированных. Расширьте предложение group by в соответствии со стандартом sql и получите ожидаемые результаты:

 ...GROUP BY cursos.cursoID, cursos.estadoCurso, cursosUsuarios.userHabilitado;