1 Востаннє редагувалося Betterthanyou (28.04.2020 11:55:12)

Тема: Порахувати лише позитивні значення для колонки

Таблиці (без полів які в запитані не використовуються):

Account_
* Id_ int
* FirstName_ VARCHAR
* LastName_ VARCHAR

AdminAccount_
* Id_ int
* KeyToAccount_ int -> Foreign Key To Account_.Id_

Message_
* Id_ int
* Date_ datetime
* IsRead_ bool
* KeyToRecipient_ int  -> Foreign Key To Account_.Id_
* KeyToSender_ int -> Foreign Key To Account_.Id_

Коли користувач реєструється, створюється рядок в Account_, якщо користувач адмін створюється AdminAccount_ з прив'язкою по ключі KeyToAccount_

Переписуватися можуть лише адміни
При створені повідомлення записується його дата Date_, чи повідомлення було прочитане IsRead_ та ід відправника та отримувача

Я хочу написати запит який вибере [Ім'я], [Прізвище], [Кількість не прочитаних повідомлень] та посортує це все за датою, новіші - перші, ліміт 50 рядків

Тут мені вдалося отримати всіх адмінів та виключити свій адмін обліковий запис

SELECT 
Account_.FirstName_ AS FirstName1, 
Account_.LastName_ AS LastName1
FROM Account_ 
INNER JOIN AdminAccount_ ON AdminAccount_.KeyToAccount_ = Account_.Id_ 
LEFT JOIN Message_ ON Message_.KeyToRecipient_ = Account_.Id_ 
WHERE Account_.Id_<>1
LIMIT 0, 50

Тепер весь запит

SELECT 
Account_.FirstName_ AS FirstName1, 
Account_.LastName_ AS LastName1,
count(Message_.IsRead_>0) AS Count1
FROM Account_ 
INNER JOIN AdminAccount_ ON AdminAccount_.KeyToAccount_ = Account_.Id_ 
LEFT JOIN Message_ ON Message_.KeyToRecipient_ = Account_.Id_ 
WHERE Account_.Id_<>1
ORDER BY max(Message_.Date_) DESC 
LIMIT 0, 50

Отримую помилку

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'Account_.FirstName_'; this is incompatible with sql_mode=only_full_group_by

Допоможіть написати запит.

2 Востаннє редагувалося koala (28.04.2020 12:35:41)

Re: Порахувати лише позитивні значення для колонки

SELECT field FROM table --- всі поля field з таблиці table. Наприклад, 10 рядків.
SELECT COUNT(*) FROM table --- скільки рядків у таблиці table, тобто 10.
SELECT field, COUNT(*) FROM table --- скільки ви тут очікуєте побачити рядків, просто цікаво? 10 і в кожному число 10?

Я вас розчарую - в останньому прикладі, якщо ви вимкнете ONLY_FULL_GROUP_BY, ви отримаєте 1 рядок з довільним значенням field, а інакше помилку, яку ви бачите. COUNT рахує значення, і якщо вам потрібна не вся таблиця, то вам потрібен GROUP BY по тих полях, по яких ви їх рахуєте.
Цікаво - GROUP BY Account.Id вистачить?

Подякували: Betterthanyou, leofun012

3 Востаннє редагувалося Betterthanyou (28.04.2020 23:35:20)

Re: Порахувати лише позитивні значення для колонки

koala написав:

Цікаво - GROUP BY Account.Id вистачить?

Так, запрацювало

SELECT 
Account_.Id_ AS AccountId, 
Account_.FirstName_ AS FirstName,  
Account_.LastName_ AS LastName, 
count(CASE WHEN Message_.IsRead_=false AND Message_.KeyToRecipient_=1 THEN 1 END) AS CountIsRead 
FROM Account_ 
INNER JOIN AdminAccount_ ON AdminAccount_.KeyToAccount_ = Account_.Id_ 
LEFT JOIN Message_ ON Message_.KeyToSender_ = Account_.Id_
WHERE Account_.Id_ <> 1
GROUP BY Account_.Id_
ORDER BY CountIsRead DESC, max(Message_.Date_) DESC
LIMIT 0, 50

4

Re: Порахувати лише позитивні значення для колонки

COUNT(*) WHERE Message_.IsRead_>0 за наявності індексу за IsRead може бути ефективнішим.

5

Re: Порахувати лише позитивні значення для колонки

Нуууу, я б перед тим як вішати індекс на булевське поле добре подумав чи воно того варте. Чи можливо я Вас невірно зрозумів?

Подякували: Betterthanyou, leofun012

6

Re: Порахувати лише позитивні значення для колонки

Тю, не помітив. Таки bool. А нащо bool на >0 перевіряти? Його треба на VARCHAR перетворювати і довжину дивитися - якщо 4, то то виконується, а якщо 5 - то ні.

Подякували: Betterthanyou1

7 Востаннє редагувалося Betterthanyou (28.04.2020 16:40:20)

Re: Порахувати лише позитивні значення для колонки

koala написав:

А нащо bool на >0 перевіряти?

Я розроблював БД у візуальному режимі MySql Workbench, чомусь поля з boolean замінилися на tinyint(1). Я зустрічався з проблемою що при перетворені типів bool -> int, ціле значення може бути не 1, а більше (Давно ще я на форму писав таку тему по С++). Тому я вибрав таку перевірку

8

Re: Порахувати лише позитивні значення для колонки

Betterthanyou написав:

...Workbench, чомусь поля з boolean замінилися на tinyint(1)...

https://dev.mysql.com/doc/refman/8.0/en … yntax.html

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true

Подякували: Betterthanyou, leofun012

9

Re: Порахувати лише позитивні значення для колонки

А як тоді з від'ємними?

Подякували: Betterthanyou1

10

Re: Порахувати лише позитивні значення для колонки

koala написав:

А як тоді з від'ємними?

Про це не подумав, зроблю поле беззнаковим

11

Re: Порахувати лише позитивні значення для колонки

Мені здається,  IsRead_<>0 буде кращим рішенням.

Подякували: Betterthanyou1

12

Re: Порахувати лише позитивні значення для колонки

koala написав:

А як тоді з від'ємними?

Так як і написано в документації:

A value of zero is considered false. Nonzero values are considered true

CREATE TABLE tab ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, some_column TINYINT ( 1 ) NOT NULL );
INSERT INTO tab ( some_column )
VALUES
    ( TRUE ),
    ( FALSE ),
    ( 0 ),
    ( 1 ),
    ( 2 ),
    ( - 1 );
SELECT
    sum( some_column IS TRUE ) AS `true`,
    sum( some_column IS FALSE ) AS `false` 
FROM
    tab;

true false
4     2

Якщо є певний страх за дані які вводяться  :)
UNSIGNED в допомогу:

CREATE TABLE tab ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, some_column TINYINT ( 1 ) UNSIGNED NOT NULL );

true false
3     3

Хоча тут можуть бути певні моменти - див. https://dev.mysql.com/doc/refman/8.0/en … rflow.html

Ну і якщо дійсно потрібно обмежувати - CHECK constraint (з MySQL 8 ), ну або тригери:

CREATE TABLE tab ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, some_column TINYINT ( 1 ) CHECK ( some_column = 0 OR some_column = 1 ) NOT NULL );
Подякували: Betterthanyou1