Komenda sql która zwróci ustaloną liczbę wyników.

Question

Witam, poszukuję komendy SQL która ograniczy ilość zwracanych wyników. Chciałbym aby z tabeli artykuły komenda wybierała po cztery artykuły z każdej kategorii (wartość w kolumnie kategoria).

Answer 1

select * from mnm;

+--------+------------+
| worker | sub_worker |
+--------+------------+
|      6 |          3 |
|      8 |         13 |
|      8 |          1 |
|     14 |         15 |
|     14 |         16 |
|     14 |         17 |
+--------+------------+

select * from mnm limit 4 offset 3;

+--------+------------+
| worker | sub_worker |
+--------+------------+
|     14 |         15 |
|     14 |         16 |
|     14 |         17 |
+--------+------------+

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

Pomocny link/linki, który naprowadził mnie na rozwiązanie - poniżej.

mysqltutorial.org dba.stackexchange.com databasejournal.com

select * from ax;

+----+-------+----------+
| id | title | category |
+----+-------+----------+
|  1 | a     | Poland   |
|  2 | b     | World    |
|  3 | c     | World    |
|  4 | d     | World    |
|  5 | e     | Poland   |
|  6 | f     | Poland   |
|  7 | g     | World    |
+----+-------+----------+
7 rows in set (0.00 sec)

select * from (select any_value(id) as id1, any_value(title) as title1, any_value(category), row_number() over(partition by category) as row_num from ax) as alias_first where row_num <= 3;

+-----+--------+---------------------+---------+
| id1 | title1 | any_value(category) | row_num |
+-----+--------+---------------------+---------+
|   1 | a      | Poland              |       1 |
|   5 | e      | Poland              |       2 |
|   6 | f      | Poland              |       3 |
|   2 | b      | World               |       1 |
|   3 | c      | World               |       2 |
|   4 | d      | World               |       3 |
+-----+--------+---------------------+---------+
6 rows in set (0.00 sec)

select id1, title1, category_a1 from (select any_value(id) as id1, any_value(title) as title1, any_value(category) as category_a1, row_number() over(partition by category) as row_num from ax) as alias_first where row_num <= 3;

+-----+--------+-------------+
| id1 | title1 | category_a1 |
+-----+--------+-------------+
|   1 | a      | Poland      |
|   5 | e      | Poland      |
|   6 | f      | Poland      |
|   2 | b      | World       |
|   3 | c      | World       |
|   4 | d      | World       |
+-----+--------+-------------+
6 rows in set (0.00 sec)

Edit 25/05/2020.

Te "any_value" są po to, żeby nie było errora 1140. Możliwe, że niektóra/niektóre z kolumn błędu nie powodują i tam tego nie musi być, więc "any_value" powinno być, tylko tam, gdzie jest to wymagane. W kodzie powyższym u mnie jest chyba za dużo "any_value", ale niech już zostanie, bo być może wszystkie kolumny powinny mieć "any_value", tak jak zostało napisane, ale nie będę raczej tego sprawdzał. Odsyłam, więc i do dokumentacji.

Za nieścisłości w sformułowaniu zdań, przepraszam.

Comments

Zdanie, które napisałeś jest raczej sprzeczne. W miarę możliwości redaguj posty.

OK. Błąd, który dostałeś, to jest chyba mieszanie kolumny/kolumn, która ma instrukcję grupująca (patrz, najprawdopodobniej row_number()) z nie kolumną/kolumnami, która obsługuje oddziaływania na tego typu instrukcje (patrz wyżej, wspomniane row_number()). Jest to niedozwolone, gdy tam nie ma klauzuli GROUP BY.

Wykonaj proszę poniższe polecenia i wklej rezultat z odpowiedzi.

describe ax;

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| title       | varchar(20) | YES  |     | NULL    |                |
| category    | varchar(20) | YES  |     | NULL    |                |
| date_newest | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

show create table ax \G

*************************** 1. row ***************************
       Table: ax
Create Table: CREATE TABLE `ax` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) DEFAULT NULL,
  `category` varchar(20) DEFAULT NULL,
  `date_newest` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Tak jak ja teraz, umieściłem "opis" tabeli.

Powinniśmy coś na to zaradzić. Możliwe właśnie, że utworzona tabela u Ciebie jest "wadliwa".

---

Pierwsze:

Drugie:

---

Po pierwsze, nie jestem pewien, czy Twoja "samowolka" nie spowodowała ów błędu.

Zamiast pisać "*", wymień ręcznie wszystkie kolumny lub część (zależnie od potrzeb) i daj znać, czy "zaskoczyło".

Ewentualnie spróbuj gdzieś upchać klauzulę GROUP BY, czyli pogrupuj po jakiejś kolumnie i może także będzie OK.

Jeszcze info o tym, dlaczego napisałem select w select, poniżej.

To counteract this problem, we need to wrap our query into a derived table. We can then attach a where clause to it:

https://mariadb.com/kb/en/window-functions-overview/

Podałem forka MySQL, ale mają podobne funkcjonalności. Mówi to o tym, że trzeba raczej owinąć to w derived table "zagnieżdzenie select".

Doczytaj w docs.

Aha i napisz może z jakiej bazy danych korzystasz, czy z wymienionego forka, czyli MariaDB, czy MySQL lub jakiejś innej.

---

Wymienienie kolumn po kolei nic nie daje, puchnięcie group by również.

---

Dlaczego nie napisałeś kodu po wymienieniu kolumn? Starasz się, aby ktoś Ci pomógł, ale tego nie ułatwiasz. Teraz będziesz musiał raczej przepisać ten kod pod swoje dyktando (nazewnictwo, itp.).

Chciałbym zwrócić Tobie także uwagę na to, abyś wklejał/przepisał cały kod błędu wraz z kodem (numerem).

Error number: 1140; Symbol: ER_MIX_OF_GROUP_FUNC_AND_FIELDS; SQLSTATE: 42000

Message: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html#error_er_mix_of_group_func_and_fields

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

Czytaj dokumentację, proszę. Czekasz chyba tylko na rozwiązanie podane na tacy.

select any_value(id1) as name_of_column, any_value(title1), any_value(category_a1), any_value(date_newest) from (select any_value(id) as id1, any_value(title) as title1, any_value(category) as category_a1, any_value(date_newest) as date_newest, row_number() over(partition by category order by any_value(date_newest)) as row_num from ax) as alias_first where row_num <= 3;

I taka sugestia na koniec. Umieść kod, który poprawisz wyrzucając z kodu instrukcje "any_value". Masz wyrzucić z kodu wszystkie "any_value", które nie powodują błędu.

Jeśli nie chcesz napisać ani jednego "any_value", to najprawdopodobniej można i tak, ale musisz napisać, to co "pisze"/jest napisane w dokumentacji.

Zgodnie z błędem masz ustawione, to.

SET sql_mode = 'ONLY_FULL_GROUP_BY';

To ustaw w takim razie, to.

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

Proszę o wiadomość zwrotną, czy kwestia została rozwiązana, czy też nie.

Aha, prosiłem Cię, żebyś podał informację o rodzaju bazy danych. Umieść także z jakiej wersji korzystasz.

Zapraszam do lektury, https://dev.mysql.com/doc/mysql-getting-started/en/.

Pytam się o wersję bazy, bo ja dostawałem ten komunikat, umieszczę go poniżej.

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner'; this is incompatible with sql_mode=only_full_group_by

Przez to, że "skróciłeś" komunikat o błędzie zapytania SQL, mogłem nie skojarzyć, że chodzi o error 1140, który podczas pisania kodu SQL u mnie wystąpił (zastosowałem any_value) zgodnie z doc. Ale możliwe, że ten komunikat jest u Ciebie "inny", bo masz inną wersję i/lub konfigurację/ustawienia bazy danych, itp.

Answer 2

CREATE TABLE kategoria (
id INT(6) UNSIGNED NOT NULL PRIMARY KEY,
nazwa VARCHAR(30) NOT NULL
) CHARACTER SET utf8;

INSERT INTO kategoria (id, nazwa) VALUES (1, 'javascript');
INSERT INTO kategoria (id, nazwa) VALUES (2, 'html5');
INSERT INTO kategoria (id, nazwa) VALUES (3, 'css3');
INSERT INTO kategoria (id, nazwa) VALUES (4, 'mysql5.x');

CREATE TABLE artykuly (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
kategoria INT(6) UNSIGNED NOT NULL,
tresc VARCHAR(255) NOT NULL
) CHARACTER SET utf8;

INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #01');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #02');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #01');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #02');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #03');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #01');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #02');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #04');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #03');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #05');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #06');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #03');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #04');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #04');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #05');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #05');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #07');
INSERT INTO artykuly (kategoria, tresc) VALUES (1,'Artykuł o javascript #08');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #06');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #07');
INSERT INTO artykuly (kategoria, tresc) VALUES (2,'Artykuł o html5 #08');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #01');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #02');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #03');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #04');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #06');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #07');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #05');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #06');
INSERT INTO artykuly (kategoria, tresc) VALUES (3,'Artykuł o css3 #08');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #07');
INSERT INTO artykuly (kategoria, tresc) VALUES (4,'Artykuł o mysql5.x #08');

(
  SELECT a.id AS 'Id artykuł',
         a.kategoria AS 'Id kategoria',
         a.tresc AS 'Treść',
         k.nazwa AS 'Nazwa kategorii'
  FROM   artykuly AS a, kategoria AS k
  WHERE  a.kategoria = k.id AND
  		 k.nazwa = 'javascript'
  ORDER BY a.tresc DESC, k.nazwa
  LIMIT 4
)
UNION
(
  SELECT a.id, a.kategoria, a.tresc, k.nazwa
  FROM   artykuly AS a, kategoria AS k
  WHERE  a.kategoria = k.id AND
  		 k.nazwa = 'html5'
  ORDER BY a.tresc DESC, k.nazwa
  LIMIT 4
)
UNION
(
  SELECT a.id, a.kategoria, a.tresc, k.nazwa
  FROM   artykuly AS a, kategoria AS k
  WHERE  a.kategoria = k.id AND
         k.nazwa = 'css3'
  ORDER BY a.tresc DESC, k.nazwa
  LIMIT 4
)
UNION
(
  SELECT a.id, a.kategoria, a.tresc, k.nazwa
  FROM   artykuly AS a, kategoria AS k
  WHERE  a.kategoria = k.id AND
         k.nazwa = 'mysql5.x'
  ORDER BY a.tresc DESC, k.nazwa
  LIMIT 4
);

| Id artykuł | Id kategoria | Treść                    | Nazwa kategorii |
| ---------- | ------------ | ------------------------ | --------------- |
| 18         | 1            | Artykuł o javascript #08 | javascript      |
| 17         | 1            | Artykuł o javascript #07 | javascript      |
| 11         | 1            | Artykuł o javascript #06 | javascript      |
| 10         | 1            | Artykuł o javascript #05 | javascript      |
| 21         | 2            | Artykuł o html5 #08      | html5           |
| 20         | 2            | Artykuł o html5 #07      | html5           |
| 19         | 2            | Artykuł o html5 #06      | html5           |
| 15         | 2            | Artykuł o html5 #05      | html5           |
| 30         | 3            | Artykuł o css3 #08       | css3            |
| 27         | 3            | Artykuł o css3 #07       | css3            |
| 26         | 3            | Artykuł o css3 #06       | css3            |
| 16         | 3            | Artykuł o css3 #05       | css3            |
| 32         | 4            | Artykuł o mysql5.x #08   | mysql5.x        |
| 31         | 4            | Artykuł o mysql5.x #07   | mysql5.x        |
| 29         | 4            | Artykuł o mysql5.x #06   | mysql5.x        |
| 28         | 4            | Artykuł o mysql5.x #05   | mysql5.x        |

Link do tesów on-line: Demo MySQL v.5.7

 

Comments

Zasada DRY (Don't Repeat Yourself) - nie powtarzaj się, się kłania. I może jeszcze skalowalność.