select * from
(
select to_char(time, 'YYYY.MM.DD HH24:MI') time_round_minute, count(distinct userId) cnt
from log_table
group by to_char(time, 'YYYY.MM.DD HH24:MI')
)
order by cnt descselect distinct t1.session_id, t1.action
from table t1
join table t2 on t1.session_id = t2.session_id
where t1.action in ('вход', 'переход на страницу 1', 'переход на страницу 2', 'целевое действие')
and t2.action not in (перечень того, что не должно быть точно)select t1.session_id, t1.action
from table t1
where t1.action in ('вход', 'переход на страницу 1', 'переход на страницу 2', 'целевое действие')
and not exists(select 1 from table t2 where t1.session_id = t2.session_id and t2.action in (перечень того, что не должно быть точно))SELECT distinct a.id, a.rank, a.firstname, a.name,
a.secondname, b.table1id
FROM table1 as a
left JOIN table2 as b on b.table1id = a.id and b.dateto < ?SELECT a.id, a.rank, a.firstname, a.name,
a.secondname, (select count(*) from table2 as b on b.table1id = a.id and b.dateto < ? and rownum = 1) cnt
FROM table1 as aSELECT a.id, a.rank, a.firstname, a.name,
a.secondname, exists(select 1 from table2 as b on b.table1id = a.id and b.dateto < ?) as is_exists
FROM table1 as aselect ext.*
from ext
where
exists (select 1 from t as t1
join t as t2 on t1.field_id = t2.field_id
where t1.field_id = ext.f1
and t1.value_id = v1
and t2.value_id = v2 )
and exists (select 1 from t as t1
join t as t2 on t1.field_id = t2.field_id
where t1.field_id = ext.f2
and t1.value_id = v3
and t2.value_id = v4 )Пробовал через join, но получается множество дублей задач, так как призов к одной задаче множество.
select task_id, ......, prize_id, .......
from (сложный join)$out = [];
foreach($rows as $row)
{
$task = &$out[row['task_id']];
$task['id'] = row['task_id'];
$task['name'] = row['name_task'];
.... // другие свойства задачи
if(!array_key_exists('prizes', $task))
{
$task['prizes'] = [];
}
if(!is_null($row['prize_id']))
{
$prize = &$task['prizes'][$row['prize_id']];
$prize['id'] = $row['prize_id'];
..... // другие свойства приза
unset($prize);
}
unset($task);
}
var_dump($out);SELECT du.*, (
SELECT COUNT(dm."receiverRead") "unreadMessages"
FROM dialogs_messages dm
WHERE
dm."dialogId" = du."dialogId"
AND dm."receiverRead" = false
AND dm."senderUserId" NOT IN ('69e56a68-edbd-4f8b-8ccd-cb8031c5c865')
AND dm.id NOT IN (
SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
)
GROUP BY dm."receiverRead"
)
FROM (
SELECT
DISTINCT ON (du."dialogId") du."dialogId",
SUBSTRING(dm."message", 1, 60),
du."joinedDateTime",
users."avatarUrl",
users.username
FROM dialogs_users du
LEFT JOIN dialogs_messages dm ON dm."dialogId" = du."dialogId"
LEFT JOIN users on users.id = dm."senderUserId"
WHERE
du."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
AND dm.id NOT IN (
SELECT dmd."messageId" FROM dialogs_messages_deleted dmd
WHERE dmd."userId" = '69e56a68-edbd-4f8b-8ccd-cb8031c5c865'
)
ORDER BY du."dialogId", dm."message" DESC
) du
ORDER BY du."joinedDateTime" DESC;update checkanaliz set id_napravlenie = 1 where id_napravlenie = 2delete from napravlenie where id = 2select *
from (select parent, product,
rank() over (partition by parent order by price) price_rnk
from t2
) a
where price_rnk = 1select case
when a.begin is null and a.end is null then 'нет данных'
when a.begin is not null and a.end is not null then concat(a.end, '-', a.begin)
when a.begin is null and a.end is not null then concat('ранее ', a.end + 1)
when a.begin is not null and a.end is null then concat('от ', a.begin)
end interval_str,
cnt
from
(
select inters.begin, inters.end, count(prepod.name) cnt
from
(
select null begin, 1969 end union all
select 1970 begin, 1979 end union all
select 1980 begin, 1989 end union all
select 1990 begin, 1999 end union all
select 2000 begin, null end union all
select null begin, null end -- запись для тех, у кого нет данных др.
) inters -- таблица с интервалами
left join
(
select 'a' name, STR_TO_DATE('2013-02-11', '%Y-%m-%d') date_r union all
select 'aa' name, STR_TO_DATE('2010-09-01', '%Y-%m-%d') date_r union all
select 'b' name, STR_TO_DATE('1968-02-11', '%Y-%m-%d') date_r union all
select 'bb' name, STR_TO_DATE('1969-01-21', '%Y-%m-%d') date_r union all
select 'c' name, STR_TO_DATE('1980-02-11', '%Y-%m-%d') date_r union all
select 'd' name, STR_TO_DATE('1989-02-11', '%Y-%m-%d') date_r union all
select 'z' name, null date_r -- препод, у которого нет данных др.
) prepod on inters.begin <= year(prepod.date_r) and inters.end >= year(prepod.date_r)
or inters.begin is null and inters.end >= year(prepod.date_r)
or inters.begin <= year(prepod.date_r) and inters.end is null
or prepod.date_r is null and inters.begin is null and inters.end is null
group by inters.begin, inters.end
order by inters.begin desc, inters.end desc
) arecords(<тут>)VALUES(<тут>)1, 'test', 'test' <тут> 'test', SELECT count(e.id) "кол-во записей example со статусом 1",
(
select count(type_id)
from example e_1
where type_id = 1
and e_1.status = 3
) "кол-во для типа 1",
(
select count(type_id)
from example e_3
where type_id = 3
and e_3.status = 3
) "кол-во для типа 3",
(
select count(type_id)
from example e_4
where type_id = 4
and e_4.status = 3
) "кол-во для типа 4"
FROM example e
WHERE e.status = 1
and e.user_id = 53;SELECT count(e.id) "кол-во записей example со статусом 1",
count(case when e.type_id = 1
and e.status = 3 then 1 end) "кол-во для типа 1",
count(case when e.type_id = 3
and e.status = 3 then 1 end) "кол-во для типа 3",
count(case when e.type_id = 4
and e.status = 3 then 1 end) "кол-во для типа 4"
FROM example e
WHERE e.status = 1
and e.user_id = 53;SELECT count(case when e.status = 1 then 1 end) "кол-во записей example со статусом 1",
count(case when e.type_id = 1
and e.status = 3 then 1 end) "кол-во для типа 1",
count(case when e.type_id = 3
and e.status = 3 then 1 end) "кол-во для типа 3",
count(case when e.type_id = 4
and e.status = 3 then 1 end) "кол-во для типа 4"
FROM example e
where e.user_id = 53-- Считаем статус 1, игнорируем типы
SELECT 'with_status_1' type_query, null type_id, count(*) cnt
FROM example e
where e.status = 1
and e.user_id = 53
union all
-- Считаем остальные типы со статусом 3
SELECT 'with_another_types' type_query, e.type_id, count(*) cnt
FROM example e
where e.status = 3
and e.user_id = 53
group by e.type_idSELECT p.*, u.name FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id and pt1.tag_id = 3
JOIN postTags pt2 ON pt2.post_id = p.id and pt2.tag_id = 5
JOIN postTags pt3 ON pt3.post_id = p.id and pt3.tag_id = 7
WHERE p.user_id = 4Select a.*
from
(
SELECT p.*, u.name, count (distinct pt1.tag_id) over (partition by p.id) unik_tag_count
FROM posts p
JOIN users u ON u.id = p.user_id
JOIN postTags pt1 ON pt1.post_id = p.id
WHERE p.user_id = 4
and pt1.tag_id in (3, 5, 7)
) a
where a.unik_tag_count = 3 -- уникальное кол-во тегов на один постSelect t1.save_id, t1.meta_key, t1.meta_value, t2.meta_key as t2_meta_key, t2.meta_value as t2_meta_value
from cpl_save_meta t1
join cpl_save_meta t2 on t1.save_id = t2.save_id
and t1.meta_key = 'width_surface' and t1.meta_value = '2.6'
and t2.meta_key = 'height_surface' and t2.meta_value = '3.53'
where (select count(*) from cpl_save_meta t3
where t3.save_id = t1.save_id
and t3.meta_key not in ('width_surface', 'height_surface')) = 0select a.* from
(
select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
from
(
select c.id, cast(unnest(string_to_array(translate(c.value,'{}',''),',')) as INTEGER) as split_value
from
( -- имитация данных таблицы
(select 0 as id, '{1,2,3}' as value) union all
(select 1, '{4, 5}') union all
(select 2, '{3, 6}')
) c
) b
) a
where a.v_count > 1select a.* from
(
select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
from
(
select c.id, unnest(c.value) as split_value
from
( -- имитация данных таблицы
(select 0 as id, array[1,2,3] as value) union all
(select 1, array[4,5]) union all
(select 2, array[3,6])
) c
) b
) a
where a.v_count > 1insert into orders (column_1, column_2, column_3)
select column_1, column_2, 'default_value' fake_column
from basketinsert into orders (column_1, column_2, column_3)
select column_1, column_2, (select count(*) from basket) fake_column
from basketselect item.idItem
from item
where
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Железо"
and details_item.idItem = item.idItem) > 0
and
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Дерево"
and details_item.idItem = item.idItem) > 0
and
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Медь"
and details_item.idItem = item.idItem) > 0
.... и так далее, все обязательные критерии прописываем через подзапрос count()select idItem
from (select item.idItem,
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Железо"
and details_item.idItem = item.idItem) as count_1,
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Дерево"
and details_item.idItem = item.idItem) as count_2,
count (SELECT details_item.idItem FROM details_item
JOIN detail ON detail.idDetail = details_item .idDetail
WHERE detail.title = "Медь"
and details_item.idItem = item.idItem) as count_3
.... и так далее все обязательные критерии через подзрапрос count(...) as count_n
from item)
where count_1 > 0 and count_2 > 0 and count_3 > 0 -- строгое соответствие параметрам поиска
-- тут делаем логику нечувствительности к одному из параметров поиска
or count_1 = 0 and count_2 > 0 and count_3 > 0
or count_1 > 0 and count_2 = 0 and count_3 > 0
or count_1 > 0 and count_2 > 0 and count_3 = 0select *
from t as t1
join t as t2 on t1.user_id1 = t2.user_id2
AND t2.user_id1 = t1.user_id2insert into parking_time (owner_id, auto_id, parker_id, place_id, begin_date, exp_date)
select :owner_id, a.id, :parker_id, :place_id, :begin_date, :exp_date
from auto a
where a.car_number = :car_number