- 1
- 2
- 3
if @OWNER_PHONE <> @OLD_PHONE set @PHONE = @OWNER_PHONE
if @PHONE <> @OLD_PHONE set @OWNER_PHONE = @PHONE
set @PHONE = @OWNER_PHONE
Нашли или выдавили из себя код, который нельзя назвать нормальным, на который без улыбки не взглянешь? Не торопитесь его удалять или рефакторить, — запостите его на говнокод.ру, посмеёмся вместе!
0
if @OWNER_PHONE <> @OLD_PHONE set @PHONE = @OWNER_PHONE
if @PHONE <> @OLD_PHONE set @OWNER_PHONE = @PHONE
set @PHONE = @OWNER_PHONE
+1
declare @NL table
(ARTICLE_ID int primary key,
M int,
DS datetime,
DD datetime,
RAS money,
REST money,
NWS money,
NWA money,
LD_AMOUNT money,
LD_DATE datetime,
IS_NL bit,
SCC_ID int,
IS_NOT_MARKDOWN bit)
Double Side,Single Density / Double Side, Double Density — это понятно. Но почему datetime?
0
CREATE OR REPLACE FUNCTION update_last_comment_ids_on_rev_update() RETURNS trigger AS $$
BEGIN
UPDATE comments
SET last_revision_id = NEW.id
FROM comment_revisions
WHERE comments.id_ru = NEW.comment_id
AND comment_revisions.id = comments.last_revision_id
AND comment_revisions.fetch_time < NEW.fetch_time;
IF (NEW.source = 'XYZ') THEN
UPDATE comments
SET last_xyz_revision_id = NEW.id
FROM comment_revisions
WHERE comments.id_ru = NEW.comment_id
AND comment_revisions.id = comments.last_xyz_revision_id AND comment_revisions.fetch_time < NEW.fetch_time;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Извини, defecate-plusplus, снова я на тебя ору из 1970-х.
0
CASE WHEN DAY(saledate)<10 THEN '0'+STR(DAY(saledate),1) ELSE STR(DAY(saledate),2) END + '.' + CASE WHEN MONTH(saledate)<10 THEN '0'+ STR(MONTH(saledate),1) ELSE STR(MONTH(saledate),2) END + '.'+ STR(YEAR(saledate),4) as SaleDate
Я у мамы программист...
convert(varchar, saledate, 104) as SaleDate
0
https://ibb.co/rpFfBMM
Просто код не поместится(
+1
SELECT
`1000, 1000000 field`
(SELECT group_concat(band_category_languages.name SEPARATOR ', ')
FROM calendar_entry_categories
LEFT JOIN band_category_languages ON band_category_languages.band_category_id = calendar_entry_categories.band_category_id
WHERE calendar_entry_categories.calendar_entry_id = calendar_entries.id
AND band_category_languages.language_id = 1
GROUP BY calendar_entry_categories.calendar_entry_id) AS band_category_string,
(SELECT entry.id
FROM calendar_entries AS entry
WHERE entry.entry_kind_parent_id = calendar_entries.id
AND entry.profile_type_id = 10869
AND entry.profile_type = 1) AS move_to_personal_calendar,
(SELECT count(moved_entry.id)
FROM calendar_entries AS moved_entry
WHERE moved_entry.entry_kind_parent_id = calendar_entries.id
AND moved_entry.status = 3) AS count_moved,
(SELECT user_images.image
FROM calendar_entries AS calendar_entries1
LEFT JOIN user_images ON user_images.id = calendar_entries1.image
WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
AND calendar_entries.entry_kind = 4) AS festival_image,
(SELECT countries1.code
FROM calendar_entries AS calendar_entries1
LEFT JOIN locations AS locations1 ON locations1.id = calendar_entries1.location_id
LEFT JOIN countries AS countries1 ON countries1.id = locations1.country_id
WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
AND calendar_entries.entry_kind = 4) AS festival_country_code,
(SELECT count(*)
FROM calendar_entry_likes
WHERE calendar_entry_likes.calendar_entry_id = calendar_entries.id) AS count_of_likes,
(SELECT count(*)
FROM calendar_entry_likes
WHERE calendar_entry_likes.calendar_entry_id = calendar_entries.id
AND calendar_entry_likes.user_id = 10869 ) AS is_like,
(SELECT group_concat(entry_type_languages.name SEPARATOR ', ')
FROM calendar_entry_types
LEFT JOIN entry_type_languages ON entry_type_languages.entry_type_id = calendar_entry_types.entry_type_id
WHERE (calendar_entry_types.calendar_entry_id = calendar_entries.id
OR calendar_entry_types.calendar_entry_id = calendar_entries.entry_kind_parent_id)
AND entry_type_languages.language_id = 1
GROUP BY calendar_entry_types.calendar_entry_id) AS entry_types_string,
(SELECT band_addresses.city_name
FROM band_addresses
WHERE band_addresses.band_id = calendar_entries.profile_type_id) AS band_city_name,
(SELECT countries.code
FROM band_addresses
LEFT JOIN countries ON countries.id = band_addresses.country_id
WHERE band_addresses.band_id = calendar_entries.profile_type_id) AS band_country_code,
(SELECT group_concat(genre_languages.name SEPARATOR ', ')
FROM calendar_entry_genres
LEFT JOIN genre_languages ON genre_languages.genre_id = calendar_entry_genres.genre_id
WHERE calendar_entry_genres.calendar_entry_id = calendar_entries.id
AND genre_languages.language_id = 1
GROUP BY calendar_entry_genres.calendar_entry_id) AS genre_string
FROM `calendar_entries`
LEFT JOIN `locations` ON `locations`.`id` = `calendar_entries`.`location_id`
LEFT JOIN `calendar_entries` AS `festival` ON `festival`.`id` = `calendar_entries`.`entry_kind_parent_id`
LEFT JOIN `locations` AS `festival_location` ON `festival_location`.`id` = (
SELECT calendar_entries1.id
FROM calendar_entries AS calendar_entries1
WHERE calendar_entries1.id = calendar_entries.entry_kind_parent_id
AND calendar_entries.entry_kind = 4
)
LEFT JOIN `countries` ON `countries`.`id` = `locations`.`country_id`
LEFT JOIN `country_languages` ON `country_languages`.`country_id` = `locations`.`country_id`
LEFT JOIN `states` ON `states`.`id` = `locations`.`state_id`
LEFT JOIN `bands` ON `bands`.`id` = `calendar_entries`.`profile_type_id`
AND `bands`.`soft_delete` = 0
AND `bands`.`status` = 1
LEFT JOIN `band_images` ON `band_images`.`id` = `calendar_entries`.`image`
LEFT JOIN `band_privacy_settings` ON `band_privacy_settings`.`band_id` = `calendar_entries`.`profile_type_id`
WHERE (`bands`.`soft_delete` = 0 OR `bands`.`soft_delete` IS NULL)
AND (`bands`.`status` = 1 OR `bands`.`status` IS NULL)
AND ((
(SELECT count(*)
FROM band_administrators
WHERE band_administrators.band_id = bands.id
AND band_administrators.user_id = 10869 ) > ? OR `band_privacy_settings`.`band_privacy_level` != ?
) AND (
(SELECT user_type
FROM users
WHERE users.id = 10869 ) = ? OR `band_privacy_settings`.`band_privacy_level` != ?
) AND (
(SELECT count(*)
FROM user_bands
WHERE user_bands.band_id = bands.id
AND user_bands.status = 1
AND user_bands.user_id = 10869 ) > ? OR `band_privacy_settings`.`band_privacy_level` != ?
) OR `band_privacy_settings`.`band_privacy_level` IS NULL
) AND (`festival`.`entry_kind` = ? OR `festival`.`id` IS NULL)
AND (date(`calendar_entries`.`date_from`) >= ?)
AND `calendar_entries`.`soft_delete` = ?
AND (`country_languages`.`language_id` = ? OR `country_languages`.`language_id` IS NULL)
AND `calendar_entries`.`entry_kind` IN (?, ?)
AND `calendar_entries`.`event_class` IN (?, ?)
AND `calendar_entries`.`status` = ?
GROUP BY `1000, 1000000 field`
ORDER BY calendar_entries.date_from
LIMIT 1000, 20
Сидить замовник такий не тужить тай думку гадає, а що це я буду платити півтори куска спеціалусту за роботу, як є "профі" за 500 доларів, а тут ше і Вася програміст каже, а шо це ти будеш платити таку суму, як я зроблю дешевше за 300! Настрій хороший, угода укладена, робота кипить. Проходить не так багато часу, як замовник каже, а що це сторінка 2хв грузиться, а Вася каже, то нічо ми то все оптимізуємо! Проходить ще декілька місяців, а результату нема. Тоді замовнек розуміє, що Вася не профі і шукає профі в Х рази дорощого! Не так багато часу пройшло, як профі за 1000 каже, я в два рази оптимізував, більше не реально! Замовник пригнічений, шукає профі, і в один прекрасний момент знаходиться дійсно профі, який по домовленості в зарплату X оптимізував, повикидував, переписав, дописав і в результаті 50 мс при 500000 записів. Підсумок: 300 + 1000 + X != 300 :)
+1
SET @from = 11836;
SET @to = 11840;
INSERT INTO `sprinter_catalog_tree` (
`sprinter_catalog_tree`.`level`,
`sprinter_catalog_tree`.`position`,
`sprinter_catalog_tree`.`parent_id`,
`sprinter_catalog_tree`.`catalog`,
`sprinter_catalog_tree`.`info_id`,
-- ...
)
SELECT
`sprinter_catalog_tree`.`level`,
`sprinter_catalog_tree`.`position`,
@to,
`sprinter_catalog_tree`.`catalog`,
`sprinter_catalog_tree`.`info_id`,
`sprinter_catalog_tree`.`format_id`,
`sprinter_catalog_tree`.`linked_id`,
`sprinter_catalog_tree`.`linked_shablon_id`,
-- ...
FROM `sprinter_catalog_tree` where parent_id = @from order by id;
INSERT INTO `sprinter_catalog_tree` (
`sprinter_catalog_tree`.`level`,
`sprinter_catalog_tree`.`position`,
`sprinter_catalog_tree`.`parent_id`,
`sprinter_catalog_tree`.`catalog`,
`sprinter_catalog_tree`.`info_id`,
-- ...
)
SELECT
a.`level`,
a.`position`,
(SELECT id from sprinter_catalog_tree as b where b.parent_id = @to and b.name like (SELECT name from sprinter_catalog_tree where id = a.parent_id)),
a.`catalog`,
a.`info_id`,
a.`format_id`,
a.`linked_id`,
a.`linked_shablon_id`,
-- ...
FROM `sprinter_catalog_tree` as a where parent_id in (SELECT id FROM sprinter_catalog_tree where parent_id = @from) order by id;
INSERT INTO `sprinter_catalog_info`
(`name`,
`eng_name`,
`text`,
-- ..
)
SELECT
`sprinter_catalog_info`.`name`,
`sprinter_catalog_info`.`eng_name`,
`sprinter_catalog_info`.`text`,
`sprinter_catalog_info`.`short_text`,
-- ...
FROM `sprinter_catalog_info` where id in (SELECT info_id from `sprinter_catalog_tree` where parent_id = @from or parent_id in (SELECT id from `sprinter_catalog_tree` where parent_id = @from));
CREATE temporary table if not exists ids
SELECT id from `sprinter_catalog_tree` where parent_id = @to or parent_id in (SELECT id from `sprinter_catalog_tree` where parent_id = @to);
UPDATE sprinter_catalog_tree as a SET info_id = (SELECT id from sprinter_catalog_info as b where a.name like b.name order by id desc limit 1) where a.id in (SELECT id from ids);
DROP table ids;
Высрал вот такое говно в качестве write-n-throw скрипта.
Дано: есть элементы дерева, хранящиеся в таблице sprinter_catalog_tree, связаны друг с другом через parent_id. Каждому из них соответствует указанный в info_id элемент таблицы sprinter_catalog_info.
Задача: скопировать все вложенные в раздел @from каталога элементы и его подразделы (вложенность не более 1 уровня) в раздел @to, так чтобы у них были новые id, и также скопировать соответствующие им sprinter_catalog_info. Скопированные sprinter_catalog_tree должны указывать на корректные sprinter_catalog_info, id которых заранее не известны.
Вот такое говно получилось, расскажите как надо было?
+2
SELECT SQL_NO_CACHE IT.itemCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.ean),",", 1) as ean,
SUBSTRING_INDEX(GROUP_CONCAT(IT.artCode),",", 1) as artCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.description),",", 1) as description,
SUBSTRING_INDEX(GROUP_CONCAT(IT.siteName),",", 1) as siteName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.familyName),",", 1) as familyName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.familyCode),",", 1) as familyCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyCode),",", 1) as SSfamilyCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyName),",", 1) as SSfamilyName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorCode),",", 1) as sectorCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorName),",", 1) as sectorName,
SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusCode),",", 1) as radiusCode,
SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusName),",", 1) as radiusName,
FI.areaName,
FI.areaCode,
ROUND(SUM(IT.price), 2) as price,
SUM(IT.quantity) as theoreticalQty,
IFNULL(FI.countedQty, 0) as countedQty,
ROUND(SUM(IT.quantity) * ROUND(SUM(IT.price), 2), 2) as theoreticalQtyValue,
ROUND(IFNULL(FI.countedQty, 0) * ROUND(SUM(IT.price), 2), 2) as countedQtyValue,
IFNULL(FI.countedQty, 0) - SUM(IT.quantity) as stockGapQty,
ROUND((IFNULL(FI.countedQty, 0) - SUM(IT.quantity)) * SUM(IT.price), 2) as stockGapValue,
DATE_FORMAT(ST.lastImport, "%d-%m-%Y %H:%i:%s") as dateOfLastImport,
DATE_FORMAT(NOW(), "%d-%m-%Y %H:%i:%s") as dateOfExport
FROM Items as IT
LEFT JOIN (
SELECT MIN(FI.ean) as ean, SUM(FI.quantity) as countedQty, IT.itemCode,
GROUP_CONCAT(DISTINCT AR.name) as areaName,
GROUP_CONCAT(DISTINCT AR.code) as areaCode
FROM FoundItems as FI
INNER JOIN Items as IT ON FI.ean = IT.ean
LEFT JOIN Areas as AR ON AR.id = FI.areaId
INNER JOIN Stores as ST ON ST.id = IT.storeId
WHERE eventId = ${args.eventId}
AND IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
GROUP BY IT.itemCode
) as FI ON IT.itemCode = FI.itemCode
INNER JOIN Stores as ST ON ST.id = IT.storeId
WHERE IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
GROUP BY IT.itemCode, FI.areaName, FI.areaCode, FI.countedQty, dateOfLastImport
а що ця конструкція вміє, а ета що, про та ще й ето потрібно впихнути сюди
−2
SELECT country, QTY, MIN(launched)
FROM (SELECT country, launched, COUNT(name) QTY
FROM Classes c JOIN
Ships s ON c.class = s.class
GROUP BY country,launched
HAVING COUNT(name) = (SELECT MAX(qty)
FROM (SELECT country,launched,COUNT(name) qty
FROM Classes c1 JOIN
Ships s1 ON c1.class = s1.class
WHERE country = c.country
GROUP BY country,launched
)e
)
)T
GROUP BY t.qty, t.country;
-2 sql ex rating
я так понял необходимо ещё как-то добавить проверку на NULL?
−1
select Outcomes.ship,count(*)
from Outcomes
where outcomes.result!='sunk'
group by ship
HAVING count(*)>=2
union
select ships.name,count(*)
from ships
where ships.launched!=NULL
group by name
HAVING count(*)>=2
-9 sql ex...
неверно на тренировочной
коммент :
парсить имя на '%NAME%' ? или как ?