SELECT distinct
first_value(`m`.`message`) over (partition by sender_id, recipient_id ORDER BY `m`.`id` DESC) AS `message`,
`u`.`id`, `u`.`name`, `m`.`read` , recipient_id /*вывел чтоб было видно с кем он общался*/
FROM `mails` AS `m`
JOIN `users` AS `u`
ON `m`.`sender_id` = `u`.`id`
WHERE `m`.`recipient_id` = $user_idSELECT n.id, n.text
FROM news AS n
JOIN news_tags AS nt ON nt.news_id = n.id
JOIN tags AS t1 ON t1.id = nt.tag_id AND t1.name in ("животные", "кроты" /*перечисляете свои теги*/)
group by n.id, n.text
having count(distinct t1.id) = 2 /*кол-во перечисленных тегов*/SELECT
u.*,
ui.`image_name`,
(SELECT COUNT(il.`id`) FROM images_likes AS il WHERE `il`.image_id = `ui`.id) AS count_likes,
FROM users AS u
LEFT JOIN (SELECT id, user_id, image_name,
@row_num := CASE WHEN @row_num_val = user_id THEN @row_num + 1
WHEN (@row_num_val := user_id) IS NOT NULL THEN 1
END rn
FROM users_images, (SELECT @row_num := null, @row_num_val := null) AS x
ORDER BY user_id, id
--select row_number() over(partition by user_id order by id) rn, user_id , id, image_name
--from users_images
) AS ui ON ui.user_id = u.id and ui.rn <= 5
WHERE u.flag > 0SELECT t1.type_id, t1.name, coalesce(t01.id, t02.id) t0_id
FROM table1 t1
left join type1 t01 on (t1.type_id = t01.id and t1.type_id = type1)
left join type1 t02 on (t1.type_id = t02.id and t1.type_id = type2)
WHERE t1.data_id = ?SELECT
wp_posts.ID,
wp_posts.post_title,
wc_product_meta_lookup.minPrice,
wc_product_meta_lookup.maxPrice
FROM
wp_posts
LEFT JOIN (
SELECT
product_id,
(min_price * GREATEST(1, coalesce(t2.meta_value*80,1), coalesce(t3.meta_value*80),1)) as minPrice,
(max_price * GREATEST(1, coalesce(t2.meta_value*80,1), coalesce(t3.meta_value*80),1)) as maxPrice
FROM
wp_wc_product_meta_lookup as t1
LEFT JOIN wp_postmeta as t2 ON t1.product_id = t2.post_id
AND t2.meta_key = 'convert_to_rub'
AND t2.meta_value = 1
JOIN wp_term_relationships tr ON (t1.product_id = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
LEFT JOIN wp_termmeta as t3 ON tr.term_taxonomy_id = t3.term_id
AND t3.meta_key = 'convert_to_rub'
AND t3.meta_value = 1
) wc_product_meta_lookup ON (
wp_posts.ID = wc_product_meta_lookup.product_id
)
WHERE
1 = 1
AND wp_posts.post_type = 'product'
AND wc_product_meta_lookup.minPrice > 0
--GROUP BY wp_posts.ID
ORDER BY wp_posts.post_name ASCselect p.*
from prices p,
(select currency_id, max(relevant_from) max_relevant_from
from price
where relevant_from <= NOW()
group by currency_id) p2
where p.currency_id = p2.currency_id and p.relevant_from = p2.max_relevant_fromselect ClientName, count( ClientName) cnt_all_row, count( distinct ClientName) cnt_unique_CN
from table2 t2
join table1 t1 on (t1.ClientID = t2.ClientID)
where t2.Query = 'Data4'
group by ClientNameselect t2.Query, count( distinct ClientName) cnt_unique_CN
from table2 t2
join table1 t1 on (t1.ClientID = t2.ClientID)
where t2.Query = 'Data4'
group by t2.Queryselect max(Strength) max_St, min(Strength) min_St
from table
, JSON_TABLE(concat('[',JSON_Col,']'),
'$[*]'
COLUMNS(
NESTED PATH '$.Strength[*]' COLUMNS (Strength INT PATH '$') )
) as ttselect 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_idwith 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 * 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 null