select p.*
from periods p
join (
select subject_id, group_id, min(start) min_start, max(start) max_start, max(case when "your date" between start and finish then start end) mid_start
from periods
group by subject_id, group_id
having mid_start is not null /*если хотите чтобы данные выводились только те у которых "your date" попал в период */
) temp_T on ( p.subject_id = temp_T.subject_id
and p.group_id = temp_T.group_id
and p.start in (min_start, coalesce(mid_start,min_start), max_start))SELECT user_id, 'обыкновенный' dogovor_name, SUM(case when dogovor_name='обыкновенный' then 1 else 0 end) AS cnt
FROM user_dolg
GROUP BY user_idselect distinct
first_value(message) over (partition by LEAST(sender_id, recipient_id),GREATEST(sender_id, recipient_id)
order by id desc)
from tableselect message
from table
where id in (select max(id) from table group by LEAST(sender_id, recipient_id),GREATEST(sender_id, recipient_id))with recursive cte (main_id, id, id_referer, lvl) as (
select id,
id,
id_referer,
1 lvl
from tb_users
where id_referer is null
union all
select main_id,
p.id,
p.id_referer,
lvl + 1
from tb_users p
inner join cte
on p.id_referer = cte.id
where lvl < 3
)
select main_id,
count(case lvl when 1 then 1 end) cnt_lvl1,
count(case lvl when 2 then 1 end) cnt_lvl2,
count(case lvl when 3 then 1 end) cnt_lvl3
from cte
group by main_idselect a.*, b.*
from table1 a
left join table2 b on (a.id = b.id)
where 1=1 /*постоянное*/
and b.id is not null /*при наличии этой строки будет inner join для left join*/select s.Name
, sum(nvl(o.Amount, 0)) as Quantity
from Sellers as s,
Orders as o
where s.id = o.Salesperson_id
and o.Order_date is not null
and o.Order_date > to_date('20091231','YYYYMMDD') /*если это date*/
group by s.id, s.Name
having count(o.Salesperson_id) > 1
order by sum(nvl(o.Amount, 0)) desc
offset 0 rows fetch next 1 rows onlySELECT * FROM `tab` WHERE id in (2,3,1)
order by find_in_set (id, '2,3,1')UPDATE tableA a
JOIN tableA b ON (a.entity_id = b.entity_id and b.language_id = 1 and b.text is not null)
SET a.text = b.text
WHERE a.text is nullwith t1 as (select 1 id, '222' se union all
select 2, '222,333' union all
select 3, '333,222' union all
select 4, '333,111,222')
select *
from t1
where find_in_set( '222', se) and find_in_set( '333', se)with t1 as (select 1 id, '222' se union all
select 2, '222,333' union all
select 3, '333,222' union all
select 4, '333,111,222')
select *
from t1
where find_in_set( '222', se) and find_in_set( '333', se)
having abs(find_in_set( '222', se)-find_in_set( '333', se)) = 1SELECT d.dfname,
COUNT(ed.*) edu,
COUNT(case when ed.dfemployee is null then 1 end) notedu,
COUNT(em.*) AS vse
FROM tdepartment d
left join temployee em on (em.dfdepartment = d.dfobj)
left join teducation ed on ( ed.dfemployee = em.dfobj)
group by d.dfname
having edu > noteduselect case when status in ('ACTIVE', 'ONLINE') then 1
when status in ('DISCHARGED', 'WARN', 'ABNORMAL') then 2
end status_type,
Count(*) cnt
from sensor_instances
where groupid = 18
group by status_typeselect
case when t1.attr_type = 1 and t1.value_int = 20 then t1.value_int end y,
case when t1.attr_type = 2 and t1.value_int = 4 then t1.value_int end m,
case when t1.attr_type = 3 and t1.value_int = 4 then t1.value_int end d,
case when t1.attr_type = 4 and t1.value_string = 'google' then t1.value_string end utm_source,
k.value_int kind_int,
i.count cnt
from stat_item_attrs k
left join stat_item i on i.id = k.item_id
join stat_item_attrs t1 on t1.item_id = k.item_id and t1.attr_type in (1/*y*/,2/*m*/,3/*d*/4/*utm_source*/) and (t1.value_int in (20, 4 /*20-04-04*/) or t1.value_string = 'google')
where
k.attr_type = 0/*kind*/ and k.value_int in(0,1)/*hosts,leads*/SELECT source,
AVG(w.lenght) ag,
sum(SUM(h.price)) over (partition by h.source) s
FROM help h left JOIN watch w ON h.user_id=w.user_id and h.film_id=w.film_id
group by sourceSELECT * FROM `table`
WHERE 1 = 1
and `column1` LIKE '%word1%'
and `column1` LIKE '%word2%'
and `column1` LIKE '%word3%'
and `column1` LIKE '%word4%'
and `column1` LIKE '%word5%'