- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
SELECT last_name,
o.product,
p.price,
c.category
FROM orders o
JOIN prices p ON p.product = o.product
JOIN category c ON p.price >= c.limit_1 AND p.price < c.limit_2
Нашли или выдавили из себя код, который нельзя назвать нормальным, на который без улыбки не взглянешь? Не торопитесь его удалять или рефакторить, — запостите его на говнокод.ру, посмеёмся вместе!
0
SELECT last_name,
o.product,
p.price,
c.category
FROM orders o
JOIN prices p ON p.product = o.product
JOIN category c ON p.price >= c.limit_1 AND p.price < c.limit_2
Сикель-обоссилий.
−5
VALUE1 in (
(extract(year from '2023-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date,
(extract(year from '2022-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date,
(extract(year from '2021-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date,
(extract(year from '2020-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date,
(extract(year from '2019-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date,
(extract(year from '2018-11-01 00:00:00'::date-interval'1 year')::varchar(4)||'-12-31')::date
)
когда нужно ЗАХАРДКОДИТЬ дату
−3
ТОРПЕДО ВПЕРДЕ
)
−3
SELECT
DISTINCT customer_contracts_ref._Description AS Номер_договора,
CAST(dateadd(year, -2000, customer_contracts_ref._Fld418) as date) AS Дата_договора,
TSBO_ref._Description AS ЦБО,
trade_for_service._Number AS Номер_сделки,
CAST(dateadd(year, -2000, customer_contracts_ref._Fld419) as date) AS Дата_сделки,
service_ref._Description AS Вид_услуги,
ISNULL(trade_for_service._Fld7941, 0) AS Сумма_сделки,
ISNULL(STUFF(
(SELECT ', ', _Number as [text()]
FROM MTCKCenter.dbo._Document129 implementation_service_2
WHERE implementation_service._Fld2613RRef = implementation_service_2._Fld2613RRef
FOR XML PATH ('')), 1, 1, ''), 'ОТСУТСТВУЕТ') AS Номер_реализации,
ISNULL(STUFF(
(SELECT ', ',CAST(dateadd(year, -2000, trade_for_service._Fld16346) as date) as [text()]
FROM MTCKCenter.dbo._Document129 implementation_service_2
WHERE implementation_service._Fld2613RRef = implementation_service_2._Fld2613RRef
FOR XML PATH ('')), 1, 1, ''), '-') AS Дата_реализации,
CAST(customer_ref._Description as varchar(50)) AS Контрагент,
CASE trade_for_service._Fld9233
WHEN 0
THEN 'нет'
ELSE 'да'
END AS УТП_сделка,
CASE implementation_service._Fld8224
WHEN 0
THEN 'да'
ELSE 'нет'
END AS УТП_реализация,
CASE
WHEN implementation_service._Fld8224 = 0
THEN SUM(ISNULL(CAST(implementation_service._Fld2632 as money), 0))
ELSE '-'
END AS Сумма_УТП,
ISNULL(shopping._Fld1772,0) AS Сумма_оплаты_плюс,
FROM
MTCKCenter.dbo._Document7939 trade_for_service
JOIN MTCKCenter.dbo._Reference29 customer_contracts_ref
ON trade_for_service._Fld7945RRef = customer_contracts_ref._IDRRef
JOIN MTCKCenter.dbo._Reference90 service_ref
ON trade_for_service._Fld7942RRef = service_ref._IDRRef
JOIN MTCKCenter.dbo._Reference93 TSBO_ref
ON trade_for_service._Fld7947RRef = TSBO_ref._IDRRef
LEFT JOIN MTCKCenter.dbo._Document129 implementation_service
ON trade_for_service._Fld7945RRef = implementation_service._Fld2613RRef
JOIN MTCKCenter.dbo._Reference44 customer_ref
ON trade_for_service._Fld7944RRef = customer_ref._IDRRef
LEFT JOIN MTCKCenter.dbo._Document115 shopping
ON trade_for_service._Fld7945RRef = shopping._Fld1749RRef
LEFT JOIN MTCKCenter.dbo._Document104_VT1460 corrections1
ON trade_for_service._Fld7945RRef = corrections1._Fld1463RRef
LEFT JOIN MTCKCenter.dbo._Document104_VT1476 corrections2
ON trade_for_service._Fld7945RRef = corrections2._Fld1479RRef
LEFT JOIN MTCKCenter.dbo._Document98 refunds
ON trade_for_service._Fld7944RRef = refunds._Fld1199RRef
LEFT JOIN MTCKCenter.dbo._Document133_VT2880 write_off_to_
ON trade_for_service._Fld7945RRef = write_off_to_._Fld5602_RRRef
WHERE
(_Fld7942RRef=0x9C8F02DA550767D34576572C765E712A -- уменьшение стоимости авто
OR _Fld7942RRef=0xB999001DD8B71C2811EA5143E3D901D3) -- доплата за а/м
AND service_ref._Marked = 0x00
AND trade_for_service._Posted = 0x01
GROUP BY customer_contracts_ref._Description, trade_for_service._Posted,
customer_contracts_ref._Fld418, customer_contracts_ref._Fld419,
TSBO_ref._Description, service_ref._Description,
shopping._Fld1772, trade_for_service._Fld7941,
implementation_service._Fld2613RRef,
trade_for_service._Fld16346, customer_ref._Description,
trade_for_service._Fld9233, implementation_service._Fld8224,
implementation_service._Fld2632, trade_for_service._Fld8413,
corrections1._Fld8389, corrections2._Fld8390,
refunds._Fld1221, write_off_to_._Fld2887,
write_off_to_._Fld2888, trade_for_service._Number
Здравия всем желающим понюхать эту реальную кучу!
Есть БД, в которую данные заносились на похуй абсолютно.
Пример:
Номер_договора | Номер_сделки | Номер_реализации | Сумма_залупа | Сумма_кастюма | Сумма_второй_палочки_твикс |....|...|...|...|...|
а б в 300 3414 123424
а б в 300 3414 123424
г д е 134 154 17
г д ж 213 3903 125627
г д ж 213 13.151123151 125627
!ЗАДАЧА!
Надо выгрузить таблицу, в которой будут
1. ДИСТИНКТ номер_договора, дата_договора,
2. ДИСТИНКТ номер_сделки, дата_сделки (1 договор = 1 сделка),
3. ЧЕРЕЗ ЗАПЯТУЮ В 1ой ЯЧЕЙКЕ номер_реализации, дата_реализации (в одной сделке их может быть больше 1),
4. Запилить оконную функцию, кейс, или че нахуй угодно(я месяц работаю, до этого тыкал сиквел в вузе тока,
поэтому возможно просто не вижу более адекватное решение), которые
будут СУММИРОВАТЬ сумму_залупу, если у нее по 1 договору несколько РАЗНЫХ значений, и другие суммы,
или ВЫБИРАТЬ только одно значение, если они одинаковые по 1 договору.
1, 2, 3 пункты коряво, но разобрался. Зделал)
На 4ом жоско хочется пробить башней стену, вообще не понимаю, как это написать нормально.
−1
Доброго дня!
Никогда не писал для баз данных, да судьба-злодейка заставила.
Кто бы помог разобраться, м?
Имеется таблица-справочник, с несколькими колонками. Как я могу прочитать строки из нее, как в ListView?
Имеется код
SELECT
PAT.PATID,
PAT.PATFIRSTNAME,
PAT.PATMIDDLENAME,
PAT.PATLASTNAME,
PAT.PATBIRTHDATE,
PAT.PATSEX,
PAT.PATIDDOC,
PAT.PATIDDOCSERIES,
PAT.PATIDDOCNUMBER,
PAT.PATADDRESSFACT,
PAT.PATADDRESSFROMDOC,
PAT.PATPHONENUM,
PAT.PATPHOTO
FROM
PATLIST PAT
но хз, правильный ли он... и хз как обращаться к элементам полей. я имею только список полей.
Остались ли среди нас сикулянты, кодящие на Delphi+FIBPLUS?
без этого говнеца на работу не примут. ох, горе-то какое.
0
-- Теперь мы можем легко получить отчёт по продажам на прошлую дату:
DELIMITER ;
BEGIN;
CALL set_prot_snapshot_date('2018-10-09 17:23:47', NULL, -1);
SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
FROM docs d
INNER JOIN doc_pos p ON d.id = p.doc_id
GROUP BY d.date;
ROLLBACK;
https://habr.com/ru/post/425769/
Как научить MySQL заглядывать в прошлое
0
delete e from [Emails] as e where not exists (select 1 from [Emails] group by body having MAX(id)=e.Id)
говеное удаление дублей
0
ttt as (
select o.tovarid, o.edizmerid, o.cena,
o.vos, o.cena*o.vos as sum_vos,
coalesce(p.sum_kl,0.0) as _prih_kl, coalesce( p.sum_prih,0.0) as sum_prih,
coalesce( p.sum_prih_docs,0.0) as sum_prih_docs,
coalesce(r.sum_kl,0.0) as _rash_kl, coalesce(r.sum_rash,0.0) as sum_rash ,
coalesce(r.sum_rash_docs,0.0) as sum_rash_docs ,
coalesce(rd.sum_kl,0.0) as _rash_doc_kl, coalesce(rd.sum_rash_doc,0.0) as sum_rash_doc,
coalesce(rd.sum_rash_doc_docs, 0.0) as sum_rash_doc_docs ,
-- coalesce(rs.sum_kl,0.0) as _rash_storno_kl, coalesce(rs.sum_rash,0.0) as sum_rash_storno ,
o.ostid, o.ss, o.sb
from ost_ o
left join prih p on (o.tovarid = p.tovarid
and o.edizmerid=p.edizmerid
and o.ss = p.ss
and o.sb = p.sb)
left join rash r on (o.tovarid =r.tovarid
and o.edizmerid=r.edizmerid
and o.ss = r.ss
and o.sb = r.sb)
left join rash_doc rd on (o.tovarid =rd.tovarid
and o.edizmerid=rd.edizmerid
and o.ss = rd.ss_
and o.sb = rd.sb_)
-- left join rash_old_072018 ro on (o.tovarid =ro.tovarid
-- and o.edizmerid=ro.edizmerid)
-- 08.2018 left join rash_storno rs on (o.tovarid =rs.tovarid and o.edizmerid=rs.edizmerid)
-- where o.vos+p.sum_kl>0
),
tt as (
select *, ttt.vos + ttt._prih_kl - ttt._rash_kl as ost_fact ,
-- case when (ttt.vos+ttt._prih_kl<>0 ) then
-- ( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash_storno)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl)
-- else ttt.cena end as new_cen from ttt
-- case when (ttt.vos+ttt._prih_kl+ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 ) then
-- ( ttt.sum_vos+ttt.sum_prih +ttt.sum_rash_storno-ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_storno_kl-ttt._rash_kl-ttt._rash_doc_kl)
case when (ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl<>0 ) then
( ttt.sum_vos+ttt.sum_prih -ttt.sum_rash-ttt.sum_rash_doc)/(ttt.vos+ttt._prih_kl-ttt._rash_kl-ttt._rash_doc_kl)
else ttt.cena end as new_cen
from ttt
)
select tt.tovarid,
tt.cena,
tt.edizmerid,
t.kt,
t.nt,
tt.vos,
round(sum_vos,2),
u.name_u,
tt._prih_kl as _prih_kl,
-- round( tt.sum_prih,2) as sum_prih ,
tt.sum_prih_docs as sum_prih ,
tt._rash_kl as _rash_kl,
-- round( tt.sum_rash,2) as sum_rash ,
tt.sum_rash_docs as sum_rash ,
tt.ost_fact,
-- round(tt.ost_fact* tt.new_cen,5),
round(tt.ost_fact* tt.new_cen,2),
t.priz,
round(tt.new_cen,5),
tt.ostid,
tt._rash_doc_kl,
-- tt.sum_rash_doc,
tt.sum_rash_doc_docs,
t.dk_2010,
coalesce( tt.ss, '0'),
coalesce( tt.sb, '0')
from tt
left join material.tovar t on (t.tovarid=tt.tovarid)
left join nsi.units u on (u.id=tt.edizmerid);
Называется найди разницу между ttt, tt и t
0
return QUERY
with a as
(
with pr as
(
select p.ss, p.sb,
sum(sum_prih) as sum_prih
from material.prihod_by_all_sklads_by_doc_period(dt_ostatki) p
group by 1,2
),
r AS
(
select p.ss, p.sb,
sum(p.sum_by_cen) as sum_rash
from material.rashod_by_all_sklads_by_doc_period(dt_ostatki) p
group by 1,2
),
os AS
(
select o.ss, o.sb,
-- case when trim(o.sb)='1150' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1150'
-- when trim(o.sb)='1050' then 'Запчастини ВКМ,ВПК1 сб. 1050'
-- when trim(o.sb)='1105' then 'Запчастини ВКМ,ВПК1,ВПК2,ВПК3,сб. 1105'
-- else o.name_sklad end as name_skl,
sum(o.sum_ost_doc) as sum_ost_doc--,
from material.ostatki_by_all_sklads(dt_ostatki) o
left join material.sklad s on (s.skladid = o.skladid)
group by 1,2
),
topl AS
(
select o.ss, o.sb,
sum(o.ost_sum) as sum_ost_doc,
sum(o.vos_sum) as sum_vos,
sum(o.prih_sum) as sum_prih,
sum(o.rash_sum) as sum_rash
from material.ostatki_by_all_toplot(dt_ostatki) o
group by 1,2
)
select os.ss, os.sb,
os.sum_ost_doc,
coalesce ( pr.sum_prih,0) as sum_prih,
coalesce (r.sum_rash,0) as sum_rash
from os
left join pr on (os.ss= pr.ss and os.sb= pr.sb )
left join r on (os.ss= r.ss and os.sb= r.sb )
UNION
select os.ss, os.sb,
os.sum_ost_doc,
-- os.sum_vos + os.sum_prih - os.sum_rash as sum_ost_doc ,
os.sum_prih,
-- os.sum_rash
-- case when period_dt$>='2018.08.01' then COALESCE( r.sum_rash,0)
-- else os.sum_vos-os.sum_ost_doc + os.sum_prih end as sum_rash --os.sum_rash END
-- r.sum_rash
os.sum_vos-os.sum_ost_doc + os.sum_prih
from topl os left join r on (os.ss= r.ss and os.sb= r.sb )
/*
select o.ss, o.sb,
sum(o.ost_sum) as sum_ost_doc,
sum(o.prih_sum) as sum_prih,
sum(o.rash_sum) as sum_rash
from material.ostatki_by_all_toplot(dt_ostatki) o left JOIN r on (r.ss =o.ss and r.sb=o.sb)
group by 1,2
*/
/* UNION
select ob.ss, ob.sus,
'112' as depart_name,
'112',
sum(ob.ost_sum),
sum(ob.pr_sum),
sum(ob.rm_sum)
from material.oborot_by_ss_112_by_sklads(period_dt, dt_ostatki) ob
group by 1,2,3,4 */
),
b as
(
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'112') bb where bb.sb = '0007'
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'153') bb
UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'152') bb
select bb.ss,bb.sb,bb.sisb_d ,bb.sisb_k, bb.sdob, 0 from cont.bbz_sald_by_sb(y,m,'152') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'201') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'207') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'209') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'203') bb
UNION
select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'221') bb
-- UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'231') bb
-- UNION
-- select bb.ss,bb.sb,bb.sisb_d,bb.sisb_k, bb.sdob, bb.skob from cont.bbz_sald_by_sb(y,m,'232') bb
Весь запрос не влез...
+2
select sum(sma) as ism from Jur_7.ss2022_1 where ch=1 and gd*100+ms<2022*100+1
Нашел БД с ОТДЕЛЬНОЙ таблицей на каждый месяц (начиная с 2008 года). А тут еще и дата определяется....