Перекрёстный запрос. Сортировка

@gazzifikator · Регистрация: 02.10.2013

Студворк — интернет-сервис помощи студентам

Таблица содержит товары, магазины в которых эти товары продаются и цены товаров в магазинах.

SQL
1
2
3
4
5
6
7
CREATE TABLE `cross_product` (
  `product_id` INT(11) NOT NULL,
  `shop_id` INT(11) NOT NULL,
  `price` DOUBLE(10,4) NOT NULL,
  KEY `product_di` (`product_id`),
  KEY `shop_id` (`shop_id`)
) ENGINE=MyISAM;

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT * FROM cross_product;
+------------+---------+----------+
| product_id | shop_id | price    |
+------------+---------+----------+
|          1 |       1 |  40.0000 |
|         27 |       1 | 550.0000 |
|         22 |       1 | 265.0000 |
|         28 |       1 | 650.0000 |
|         43 |       1 | 570.0000 |
|         47 |       1 | 812.0000 |
|         22 |       8 | 313.0000 |
|          1 |       2 |  39.0000 |
|          1 |       3 |  30.6000 |
|         47 |       2 | 353.0000 |
|         28 |       2 | 562.5000 |
|         22 |       9 | 229.0000 |
|         22 |       3 | 258.0000 |
|         28 |       3 | 650.0000 |
|         27 |       3 | 536.0000 |
|         22 |       2 | 200.0000 |
|         43 |       2 | 439.0000 |
|         43 |       3 | 468.0000 |
+------------+---------+----------+

Перекрёстный запрос:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
product_id, 
group_concat(IF(shop_id=1, price, NULL)) AS s1, 
group_concat(IF(shop_id=10, price, NULL)) AS s10, 
group_concat(IF(shop_id=9, price, NULL)) AS s9, 
group_concat(IF(shop_id=3, price, NULL)) AS s3, 
group_concat(IF(shop_id=8, price, NULL)) AS s8, 
group_concat(IF(shop_id=2, price, NULL)) AS s2 
FROM cross_product
WHERE 1 
GROUP BY product_id;
+------------+----------+------+----------+----------+----------+----------+
| product_id | s1       | s10  | s9       | s3       | s8       | s2       |
+------------+----------+------+----------+----------+----------+----------+
|          1 | 40.0000  | NULL | NULL     | 30.6000  | NULL     | 39.0000  |
|         22 | 265.0000 | NULL | 229.0000 | 258.0000 | 313.0000 | 200.0000 |
|         27 | 550.0000 | NULL | NULL     | 536.0000 | NULL     | NULL     |
|         28 | 650.0000 | NULL | NULL     | 650.0000 | NULL     | 562.5000 |
|         43 | 570.0000 | NULL | NULL     | 468.0000 | NULL     | 439.0000 |
|         47 | 812.0000 | NULL | NULL     | NULL     | NULL     | 353.0000 |
+------------+----------+------+----------+----------+----------+----------+

С фильтрацией я разобрался, надеюсь что HAVING мне хватит:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT 
product_id, 
group_concat(IF(shop_id=1, price, NULL)) AS s1, 
group_concat(IF(shop_id=10, price, NULL)) AS s10, 
group_concat(IF(shop_id=9, price, NULL)) AS s9, 
group_concat(IF(shop_id=3, price, NULL)) AS s3, 
group_concat(IF(shop_id=8, price, NULL)) AS s8, 
group_concat(IF(shop_id=2, price, NULL)) AS s2 
FROM cross_product
WHERE 1 
GROUP BY product_id 
HAVING s2 > 40;
+------------+----------+------+----------+----------+----------+----------+
| product_id | s1       | s10  | s9       | s3       | s8       | s2       |
+------------+----------+------+----------+----------+----------+----------+
|         22 | 265.0000 | NULL | 229.0000 | 258.0000 | 313.0000 | 200.0000 |
|         28 | 650.0000 | NULL | NULL     | 650.0000 | NULL     | 562.5000 |
|         43 | 570.0000 | NULL | NULL     | 468.0000 | NULL     | 439.0000 |
|         47 | 812.0000 | NULL | NULL     | NULL     | NULL     | 353.0000 |
+------------+----------+------+----------+----------+----------+----------+

А вот с сортировкой проблемы. Надеялся подсунуть предварительно отсортированные данные но не вышло:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
product_id,  
group_concat(IF(shop_id=1, price, NULL)) AS s1,  
group_concat(IF(shop_id=10, price, NULL)) AS s10,  
group_concat(IF(shop_id=9, price, NULL)) AS s9,  
group_concat(IF(shop_id=3, price, NULL)) AS s3,  
group_concat(IF(shop_id=8, price, NULL)) AS s8,  
group_concat(IF(shop_id=2, price, NULL)) AS s2  
FROM (SELECT * FROM cross_product ORDER BY IF(shop_id=2,0,1) ASC, price ASC) AS t 
WHERE 1  
GROUP BY product_id;
+------------+----------+------+----------+----------+----------+----------+
| product_id | s1       | s10  | s9       | s3       | s8       | s2       |
+------------+----------+------+----------+----------+----------+----------+
|          1 | 40.0000  | NULL | NULL     | 30.6000  | NULL     | 39.0000  |
|         22 | 265.0000 | NULL | 229.0000 | 258.0000 | 313.0000 | 200.0000 |
|         27 | 550.0000 | NULL | NULL     | 536.0000 | NULL     | NULL     |
|         28 | 650.0000 | NULL | NULL     | 650.0000 | NULL     | 562.5000 |
|         43 | 570.0000 | NULL | NULL     | 468.0000 | NULL     | 439.0000 |
|         47 | 812.0000 | NULL | NULL     | NULL     | NULL     | 353.0000 |
+------------+----------+------+----------+----------+----------+----------+

По задумке данные должны были быть отсортированы по столбцу "s2" в порядке возрастания.

@Grossmeister · 09.04.2015, 15:46

Сообщение от gazzifikator

По задумке данные должны были быть отсортированы по столбцу "s2" в порядке возрастания

Ну так и поставь снизу ORDER BY, какие проблемы?

@gazzifikator · 09.04.2015, 15:59 **[ТС]**

Сообщение от Grossmeister

Ну так и поставь снизу ORDER BY, какие проблемы?

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
product_id,  
group_concat(IF(shop_id=1, price, NULL)) AS s1,  
group_concat(IF(shop_id=10, price, NULL)) AS s10,  
group_concat(IF(shop_id=9, price, NULL)) AS s9,  
group_concat(IF(shop_id=3, price, NULL)) AS s3,  
group_concat(IF(shop_id=8, price, NULL)) AS s8,  
group_concat(IF(shop_id=2, price, NULL)) AS s2  
FROM cross_product AS t 
WHERE 1  
GROUP BY product_id ORDER BY s2;
+------------+----------+------+----------+----------+----------+----------+
| product_id | s1       | s10  | s9       | s3       | s8       | s2       |
+------------+----------+------+----------+----------+----------+----------+
|         27 | 550.0000 | NULL | NULL     | 536.0000 | NULL     | NULL     |
|         22 | 265.0000 | NULL | 229.0000 | 258.0000 | 313.0000 | 200.0000 |
|         47 | 812.0000 | NULL | NULL     | NULL     | NULL     | 353.0000 |
|          1 | 40.0000  | NULL | NULL     | 30.6000  | NULL     | 39.0000  |
|         43 | 570.0000 | NULL | NULL     | 468.0000 | NULL     | 439.0000 |
|         28 | 650.0000 | NULL | NULL     | 650.0000 | NULL     | 562.5000 |
+------------+----------+------+----------+----------+----------+----------+

как видно, сортировка работает не так как нужно

@Grossmeister · 09.04.2015, 16:51

Сообщение от gazzifikator

как видно, сортировка работает не так как нужно

Работает так, как нужно.
У тебя там строки, а не числа, а они сортируются по кодовой таблице. Либо преобразуй их в числа, либо выравнивай (дополняй слева нулями) до одинаковой длины.

@gazzifikator · 10.04.2015, 09:52 **[ТС]**

Сообщение от Grossmeister

Работает так, как нужно.
У тебя там строки, а не числа, а они сортируются по кодовой таблице. Либо преобразуй их в числа, либо выравнивай (дополняй слева нулями) до одинаковой длины.

Почитал - http://dev.mysql.com/doc/refma... oup-concat, заменил на SUM.

Спасибо, теперь работает.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
product_id,  
SUM(IF(shop_id=1, price, NULL)) AS s1,  
SUM(IF(shop_id=10, price, NULL)) AS s10,  
SUM(IF(shop_id=9, price, NULL)) AS s9,  
SUM(IF(shop_id=3, price, NULL)) AS s3,  
SUM(IF(shop_id=8, price, NULL)) AS s8,  
SUM(IF(shop_id=2, price, NULL)) AS s2  
FROM cross_product AS t 
WHERE 1  
GROUP BY product_id ORDER BY s2;
+------------+----------+------+----------+----------+----------+----------+
| product_id | s1       | s10  | s9       | s3       | s8       | s2       |
+------------+----------+------+----------+----------+----------+----------+
|         27 | 550.0000 | NULL |     NULL | 536.0000 |     NULL |     NULL |
|          1 |  40.0000 | NULL |     NULL |  30.6000 |     NULL |  39.0000 |
|         22 | 265.0000 | NULL | 229.0000 | 258.0000 | 313.0000 | 200.0000 |
|         47 | 812.0000 | NULL |     NULL |     NULL |     NULL | 353.0000 |
|         43 | 570.0000 | NULL |     NULL | 468.0000 |     NULL | 439.0000 |
|         28 | 650.0000 | NULL |     NULL | 650.0000 |     NULL | 562.5000 |
+------------+----------+------+----------+----------+----------+----------+

PS. можно было и сразу написать что при GROUP_CONCAT возвращает строку.

Новые блоги и статьи Все статьи Все блоги /
Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .	SDL3 для Web (WebAssembly): Синхронизация спрайтов SDL3 и тел Box2D 8Observer8 04.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-sync-physics-sprites-sdl3-c. zip На первой гифке отладочные линии отключены, а на второй включены:. . .	SDL3 для Web (WebAssembly): Идентификация объектов на Box2D v3 - использование userData и событий коллизий 8Observer8 02.03.2026 Содержание блога Финальная демка в браузере. Итоговый код: finish-collision-events-sdl3-c. zip Сканируйте QR-код на мобильном и вы увидите, что появится джойстик для управления главным героем. . . .
Реалии Hrethgir 01.03.2026 Нет, я не закончил до сих пор симулятор. Эта задача сложнее. Не получилось уйти в плавсостав, но оно и к лучшему, возможно. Точнее получалось - но сварщиком в палубную команду, а это значит, в моём. . .	Ритм жизни kumehtar 27.02.2026 Иногда приходится жить в ритме, где дел становится всё больше, а вовлечения в происходящее — всё меньше. Плотный график не даёт вниманию закрепиться ни на одном событии. Утро начинается с быстрых,. . .	SDL3 для Web (WebAssembly): Сборка библиотек: SDL3, Box2D, FreeType, SDL3_ttf, SDL3_mixer и SDL3_image из исходников с помощью CMake и Emscripten 8Observer8 27.02.2026 Недавно вышла версия 3. 4. 2 библиотеки SDL3. На странице официальной релиза доступны исходники, готовые DLL (для x86, x64, arm64), а также библиотеки для разработки под Android, MinGW и Visual Studio. . . .	SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .