Ошибка SQL: Subquery returns more than 1 row
SQL: [SELECT /* 127,383625 */ product_uid_id, product_cnt_account, product_hld_holder, product_mbr_member, product_chr_link, product_lnk_parent, CONCAT(product_chr_name, ' с логотипом') AS product_chr_name, product_chr_article, product_img_image, COALESCE(product_txt_text3, product_txt_text) AS product_txt_text, product_txt_text2, # ред. Д.Кузьменко. Тикет 15819. IFNULL(NULLIF(product_set_group1, ''), product_chr_article) AS product_set_group1, IFNULL(NULLIF(product_set_group1, ''), product_chr_article) AS this_set_group1, product_set_brand, product_lnk_factory, product_set_color, product_set_filtercolor, product_set_size, product_set_dimensions, product_set_material, product_set_filtermaterial, REPLACE(product_set_print, ';', ', ') AS product_set_print, product_set_weight, # JSON данные о цветах product_txt_colors, product_set_field1, product_set_field2, product_set_field3, product_set_field4, product_set_field5, product_set_field6, product_set_field7, product_set_field8, product_dec_price1, product_dec_price2, product_dec_price3, product_chk_sex, product_int_balance1, product_int_balance2, product_dat_checked, product_chr_synonym, # Флаги product_enm_new, product_enm_promo AS product_enm_sale, product_enm_label, IFNULL(product_int_free1, 0) AS product_int_free1, IFNULL(product_int_balance3, 0) AS product_int_balance3, # Тираж IFNULL(IFNULL(product_int_delivery11, product_int_balance1), 0) + IFNULL(IFNULL(product_int_delivery12, product_int_balance2), 0) AS product_int_balance, # Тикет http://support.webplatform.ru/issues/15716 IF(product_enm_action = 'YES', 'product-order', IF(product_dec_price3 > 0, 'discount', '')) AS product_price_class, # Флаги для вывода В. Баканов тикет 21956,21957 #бестселлер если остаток > 3000шт на складе + вручную IF( product_int_balance1 > 3000 OR product_enm_top = 'YES' , 'YES', 'NO') AS product_enm_bestseller, #быстрая доставка на товары с Артэ с остатком в Минске от 50шт. IF( product_chr_article RLIKE '^(AR|VV)' AND product_int_balance2 > 50 , 'YES', 'NO') AS product_enm_fastdelivery , CASE WHEN ( SELECT COUNT(video_img_image1) FROM account_video a WHERE a.video_cnt_account = 127 AND a.video_hld_holder = 16171 AND a.video_mbr_member IN (product_uid_id) AND ( a.video_img_image1 LIKE '%.mp4' OR a.video_img_image1 LIKE '%.quicktime' OR a.video_img_image1 LIKE '%.avi' OR a.video_img_image1 LIKE '%.webm' OR a.video_img_image1 LIKE '%.mov' OR a.video_img_image1 LIKE '%.mkv' ) ) > 0 THEN 'YES' ELSE 'NO' END AS product_enm_hasvideo, # Thumbgallery ( SELECT CONCAT('["', REPLACE(product_img_image, 'office/127', '/245x245xffffff'), '",', GROUP_CONCAT(CONCAT('"', REPLACE(photo_img_640x480image, 'office/127', '/245x245xffffff'), '"') SEPARATOR ','), ']') FROM account_photo WHERE photo_cnt_account = 127 AND photo_hld_holder = 16171 AND photo_mbr_member = product_uid_id ORDER BY photo_ind_index LIMIT 5 ) AS product_txt_photos, # Мета product_txt_keywords, IF(NULLIF(product_chr_title, '') IS NULL, IF(NULLIF('с логотипом | Тёплый мерч для сотрудников | Брендирование шапок для вашей команды', '') IS NULL, CONCAT(product_chr_name, ' с нанесением логотипа | ','Шапки и шарфы с лейблами',' оптом в МИСТЕР СНАБ'), CONCAT('"', product_chr_name, '" ','с логотипом | Тёплый мерч для сотрудников | Брендирование шапок для вашей команды')), product_chr_title) AS product_chr_title, IF(NULLIF(product_txt_descr, '') IS NULL, CONCAT('Заказать ', LOWER(product_chr_name), ' с логотипом | ',product_chr_article,' | ','Шапки и шарфы с лейблами',' с нанесением фирменной символики в МИСТЕР СНАБ'), product_txt_descr) AS product_txt_descr, # OpenGraph CONCAT('https://', 'mister-snab.ru', '/300x300xffffff/', product_img_image) AS og_meta_image, # Срок поставки JSON_UNQUOTE(JSON_EXTRACT(product_txt_parameters, '$.ondemand_days')) AS ondemand_days, CASE WHEN product_enm_exists = 'YES' THEN 'Срок доставки уточняйте в отделе продаж' WHEN IFNULL(product_int_delivery11, product_int_balance1) = 0 AND IFNULL(product_int_delivery12, product_int_balance2) > 0 THEN 'от 60 рабочих дней (без нанесения)' ELSE CONCAT('от ', CASE UPPER(REGEXP_SUBSTR(product_chr_article, '^[A-Z]+')) WHEN 'OA' THEN 2 WHEN 'G' THEN 4 WHEN 'HG' THEN 3 WHEN 'OC' THEN 5 WHEN 'PO' THEN 2 WHEN 'VV' THEN 2 WHEN 'BP' THEN 3 WHEN 'AR' THEN 2 WHEN 'ST' THEN 5 WHEN 'MD' THEN 2 ELSE 5 END, ' рабочих дней (без нанесения)') END AS delivery_days, ( SELECT list_chr_link FROM account_list WHERE list_cnt_account = 127 AND list_hld_holder = 17466 AND list_mbr_member = 0 AND list_chr_name = product_set_brand ) AS brand_link, # Группа id для цветов и отзывов ( SELECT GROUP_CONCAT(product_uid_id SEPARATOR ',') FROM account_product WHERE product_cnt_account = 127 AND product_hld_holder = 16171 AND product_set_group1 = (SELECT this_set_group1) AND product_dat_checked >= NOW() - INTERVAL 3 DAY AND product_img_image IS NOT NULL AND IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0) > 0 AND product_dec_price1 > 1 GROUP BY IF((NULLIF(product_set_color, '') IS NULL) AND (product_lnk_parent NOT IN (60785, 60645)), # Одежда, футболки product_chr_article, product_set_color ) ) AS product_average_rating, # Средняя оценка ( SELECT ROUND(AVG(gbook_int_rating), 2) FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ) AS product_average_rating, # Количество оценок @rating_count := ( SELECT COUNT(gbook_int_rating) FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ), IF(@rating_count = 0, NULL, @rating_count) AS product_rating_count, # Подпись для оценок ( SELECT CASE WHEN COUNT(gbook_int_rating) % 100 BETWEEN 11 AND 19 THEN 'оценок' WHEN COUNT(gbook_int_rating) % 10 = 1 THEN 'оценка' WHEN COUNT(gbook_int_rating) % 10 BETWEEN 2 AND 4 THEN 'оценки' ELSE 'оценок' END FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ) AS product_rating_label, # Количество вопросов @questions_count := ( SELECT COUNT(message_uid_id) FROM account_message WHERE message_cnt_account = 127 AND message_hld_holder = 16171 AND message_mbr_member = product_uid_id ), IF(@questions_count = 0, NULL, @questions_count) AS product_questions_count, # Подпись для вопросов ( SELECT CASE WHEN COUNT(message_uid_id) % 100 BETWEEN 11 AND 19 THEN 'вопросов' WHEN COUNT(message_uid_id) % 10 = 1 THEN 'вопрос' WHEN COUNT(message_uid_id) % 10 BETWEEN 2 AND 4 THEN 'вопроса' ELSE 'вопросов' END FROM account_message WHERE message_cnt_account = 127 AND message_hld_holder = 16171 AND message_mbr_member = product_uid_id ) AS product_questions_label, # Текст для отзывов IF(NULL IS NOT NULL, 'Отзывов пока нет', 'Отзывов пока нет. Авторизуйтесь чтобы оценить товар.') AS reviews_text, # Текст для вопросов о товаре IF(NULL IS NOT NULL, 'Вопросы о товаре', 'Вопросы о товаре. Авторизуйтесь чтобы задать вопрос.') AS questions_text, # Сортировка IF(COALESCE(IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0)) = 0, 0, 1) AS product_int_balanceorder FROM account_product WHERE product_cnt_account = 127 AND product_hld_holder = 16171 AND ( product_mbr_member IN (60659) OR product_uid_id IN (NULL) ) # Не заброшенный AND (product_dat_checked >= NOW() - INTERVAL 3 DAY OR product_enm_exists = 'YES') # Есть изображение AND product_img_image IS NOT NULL # Есть цена AND product_dec_price1 > 0 AND product_chr_link LIKE ? GROUP BY IFNULL(NULLIF(product_set_group1, ''), product_chr_article) ORDER BY product_int_balance1 DESC LIMIT 1 /* module_id:383625 URN */]
SQL: [SELECT /* 127,383625 */ product_uid_id, product_cnt_account, product_hld_holder, product_mbr_member, product_chr_link, product_lnk_parent, CONCAT(product_chr_name, ' с логотипом') AS product_chr_name, product_chr_article, product_img_image, COALESCE(product_txt_text3, product_txt_text) AS product_txt_text, product_txt_text2, # ред. Д.Кузьменко. Тикет 15819. IFNULL(NULLIF(product_set_group1, ''), product_chr_article) AS product_set_group1, IFNULL(NULLIF(product_set_group1, ''), product_chr_article) AS this_set_group1, product_set_brand, product_lnk_factory, product_set_color, product_set_filtercolor, product_set_size, product_set_dimensions, product_set_material, product_set_filtermaterial, REPLACE(product_set_print, ';', ', ') AS product_set_print, product_set_weight, # JSON данные о цветах product_txt_colors, product_set_field1, product_set_field2, product_set_field3, product_set_field4, product_set_field5, product_set_field6, product_set_field7, product_set_field8, product_dec_price1, product_dec_price2, product_dec_price3, product_chk_sex, product_int_balance1, product_int_balance2, product_dat_checked, product_chr_synonym, # Флаги product_enm_new, product_enm_promo AS product_enm_sale, product_enm_label, IFNULL(product_int_free1, 0) AS product_int_free1, IFNULL(product_int_balance3, 0) AS product_int_balance3, # Тираж IFNULL(IFNULL(product_int_delivery11, product_int_balance1), 0) + IFNULL(IFNULL(product_int_delivery12, product_int_balance2), 0) AS product_int_balance, # Тикет http://support.webplatform.ru/issues/15716 IF(product_enm_action = 'YES', 'product-order', IF(product_dec_price3 > 0, 'discount', '')) AS product_price_class, # Флаги для вывода В. Баканов тикет 21956,21957 #бестселлер если остаток > 3000шт на складе + вручную IF( product_int_balance1 > 3000 OR product_enm_top = 'YES' , 'YES', 'NO') AS product_enm_bestseller, #быстрая доставка на товары с Артэ с остатком в Минске от 50шт. IF( product_chr_article RLIKE '^(AR|VV)' AND product_int_balance2 > 50 , 'YES', 'NO') AS product_enm_fastdelivery , CASE WHEN ( SELECT COUNT(video_img_image1) FROM account_video a WHERE a.video_cnt_account = 127 AND a.video_hld_holder = 16171 AND a.video_mbr_member IN (product_uid_id) AND ( a.video_img_image1 LIKE '%.mp4' OR a.video_img_image1 LIKE '%.quicktime' OR a.video_img_image1 LIKE '%.avi' OR a.video_img_image1 LIKE '%.webm' OR a.video_img_image1 LIKE '%.mov' OR a.video_img_image1 LIKE '%.mkv' ) ) > 0 THEN 'YES' ELSE 'NO' END AS product_enm_hasvideo, # Thumbgallery ( SELECT CONCAT('["', REPLACE(product_img_image, 'office/127', '/245x245xffffff'), '",', GROUP_CONCAT(CONCAT('"', REPLACE(photo_img_640x480image, 'office/127', '/245x245xffffff'), '"') SEPARATOR ','), ']') FROM account_photo WHERE photo_cnt_account = 127 AND photo_hld_holder = 16171 AND photo_mbr_member = product_uid_id ORDER BY photo_ind_index LIMIT 5 ) AS product_txt_photos, # Мета product_txt_keywords, IF(NULLIF(product_chr_title, '') IS NULL, IF(NULLIF('с логотипом | Тёплый мерч для сотрудников | Брендирование шапок для вашей команды', '') IS NULL, CONCAT(product_chr_name, ' с нанесением логотипа | ','Шапки и шарфы с лейблами',' оптом в МИСТЕР СНАБ'), CONCAT('"', product_chr_name, '" ','с логотипом | Тёплый мерч для сотрудников | Брендирование шапок для вашей команды')), product_chr_title) AS product_chr_title, IF(NULLIF(product_txt_descr, '') IS NULL, CONCAT('Заказать ', LOWER(product_chr_name), ' с логотипом | ',product_chr_article,' | ','Шапки и шарфы с лейблами',' с нанесением фирменной символики в МИСТЕР СНАБ'), product_txt_descr) AS product_txt_descr, # OpenGraph CONCAT('https://', 'mister-snab.ru', '/300x300xffffff/', product_img_image) AS og_meta_image, # Срок поставки JSON_UNQUOTE(JSON_EXTRACT(product_txt_parameters, '$.ondemand_days')) AS ondemand_days, CASE WHEN product_enm_exists = 'YES' THEN 'Срок доставки уточняйте в отделе продаж' WHEN IFNULL(product_int_delivery11, product_int_balance1) = 0 AND IFNULL(product_int_delivery12, product_int_balance2) > 0 THEN 'от 60 рабочих дней (без нанесения)' ELSE CONCAT('от ', CASE UPPER(REGEXP_SUBSTR(product_chr_article, '^[A-Z]+')) WHEN 'OA' THEN 2 WHEN 'G' THEN 4 WHEN 'HG' THEN 3 WHEN 'OC' THEN 5 WHEN 'PO' THEN 2 WHEN 'VV' THEN 2 WHEN 'BP' THEN 3 WHEN 'AR' THEN 2 WHEN 'ST' THEN 5 WHEN 'MD' THEN 2 ELSE 5 END, ' рабочих дней (без нанесения)') END AS delivery_days, ( SELECT list_chr_link FROM account_list WHERE list_cnt_account = 127 AND list_hld_holder = 17466 AND list_mbr_member = 0 AND list_chr_name = product_set_brand ) AS brand_link, # Группа id для цветов и отзывов ( SELECT GROUP_CONCAT(product_uid_id SEPARATOR ',') FROM account_product WHERE product_cnt_account = 127 AND product_hld_holder = 16171 AND product_set_group1 = (SELECT this_set_group1) AND product_dat_checked >= NOW() - INTERVAL 3 DAY AND product_img_image IS NOT NULL AND IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0) > 0 AND product_dec_price1 > 1 GROUP BY IF((NULLIF(product_set_color, '') IS NULL) AND (product_lnk_parent NOT IN (60785, 60645)), # Одежда, футболки product_chr_article, product_set_color ) ) AS product_average_rating, # Средняя оценка ( SELECT ROUND(AVG(gbook_int_rating), 2) FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ) AS product_average_rating, # Количество оценок @rating_count := ( SELECT COUNT(gbook_int_rating) FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ), IF(@rating_count = 0, NULL, @rating_count) AS product_rating_count, # Подпись для оценок ( SELECT CASE WHEN COUNT(gbook_int_rating) % 100 BETWEEN 11 AND 19 THEN 'оценок' WHEN COUNT(gbook_int_rating) % 10 = 1 THEN 'оценка' WHEN COUNT(gbook_int_rating) % 10 BETWEEN 2 AND 4 THEN 'оценки' ELSE 'оценок' END FROM account_gbook WHERE gbook_cnt_account = 127 AND gbook_hld_holder = 16171 AND gbook_mbr_member = product_uid_id ) AS product_rating_label, # Количество вопросов @questions_count := ( SELECT COUNT(message_uid_id) FROM account_message WHERE message_cnt_account = 127 AND message_hld_holder = 16171 AND message_mbr_member = product_uid_id ), IF(@questions_count = 0, NULL, @questions_count) AS product_questions_count, # Подпись для вопросов ( SELECT CASE WHEN COUNT(message_uid_id) % 100 BETWEEN 11 AND 19 THEN 'вопросов' WHEN COUNT(message_uid_id) % 10 = 1 THEN 'вопрос' WHEN COUNT(message_uid_id) % 10 BETWEEN 2 AND 4 THEN 'вопроса' ELSE 'вопросов' END FROM account_message WHERE message_cnt_account = 127 AND message_hld_holder = 16171 AND message_mbr_member = product_uid_id ) AS product_questions_label, # Текст для отзывов IF(NULL IS NOT NULL, 'Отзывов пока нет', 'Отзывов пока нет. Авторизуйтесь чтобы оценить товар.') AS reviews_text, # Текст для вопросов о товаре IF(NULL IS NOT NULL, 'Вопросы о товаре', 'Вопросы о товаре. Авторизуйтесь чтобы задать вопрос.') AS questions_text, # Сортировка IF(COALESCE(IFNULL(product_int_balance1, 0) + IFNULL(product_int_balance2, 0)) = 0, 0, 1) AS product_int_balanceorder FROM account_product WHERE product_cnt_account = 127 AND product_hld_holder = 16171 AND ( product_mbr_member IN (60659) OR product_uid_id IN (NULL) ) # Не заброшенный AND (product_dat_checked >= NOW() - INTERVAL 3 DAY OR product_enm_exists = 'YES') # Есть изображение AND product_img_image IS NOT NULL # Есть цена AND product_dec_price1 > 0 AND product_chr_link LIKE ? GROUP BY IFNULL(NULLIF(product_set_group1, ''), product_chr_article) ORDER BY product_int_balance1 DESC LIMIT 1 /* module_id:383625 URN */]
Решение задачи за 2 минуты
Фирменные треки