нужно что бы посты из бд отображались только пользователям определенной группы.
FROM post
JOIN user ON user_id = {текущий пользователь} AND FIND_IN_SET(post.group_id, user.group_ids_csv)ID username group
1 admin 1,2,3
SELECT [DISTINCT] number
FROM table t1
WHERE service NOT IN (1,2,3)
AND NOT EXISTS (
SELECT NULL
FROM table t2
WHERE t1.number = t2.number
AND t2.service IN (1,2,3)
)SELECT number
FROM test
GROUP BY number
HAVING MIN(service) > 3CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
IF OLD.val1 <> NEW.val1 THEN -- задать условие
NEW.id := NEW.id * 10; -- выполнить действие
END IF;
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION fn_test();CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
NEW.id := NEW.id * 10; -- выполнить действие
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
WHEN (OLD.val1 <> NEW.val1) -- задать условие
EXECUTE FUNCTION fn_test();WITH RECURSIVE
cte AS ( SELECT CAST(DATE_FORMAT(@range_from, '%Y-%m-01') AS DATE) month_start,
LAST_DAY(@range_from) month_end
UNION ALL
SELECT month_start + INTERVAL 1 MONTH,
LAST_DAY(month_start + INTERVAL 1 MONTH)
FROM cte
WHERE month_start < DATE_FORMAT(@range_till, '%Y-%m-01')
)
SELECT cte.month_start, COUNT(employee.id) employees_amount
FROM cte
LEFT JOIN employee ON employee.date_employment <= cte.month_end
AND ( employee.date_dismissal >= cte.month_start
OR employee.date_dismissal IS NULL )
GROUP BY 1;SELECT COALESCE(
(
SELECT busy FROM `all_reservs` WHERE dates ='27.06.22' AND number='22' LIMIT 1
),
'no'
) AS busy;Но это такое убожество что я толком ничерта не понимаю
отличаются ли запросы sql MSSQL от Mysql
возможно ли сменить БД без нарушения функциональности софта.
CREATE TABLE test ( word0 VARCHAR(255),
word1 VARCHAR(255),
word2 VARCHAR(255),
word3 VARCHAR(255)
);WITH
cte1 AS (
SELECT *, ROW_NUMBER() OVER () identity
FROM test
),
cte2 AS (
SELECT word0 word, identity FROM cte1 UNION ALL
SELECT word1 word, identity FROM cte1 UNION ALL
SELECT word2 word, identity FROM cte1 UNION ALL
SELECT word3 word, identity FROM cte1
)
SELECT LEAST(t1.word, t2.word), GREATEST(t1.word, t2.word), COUNT(DISTINCT identity)
FROM cte2 t1
JOIN cte2 t2 USING( identity )
WHERE t1.word > t2.word
GROUP BY 1, 2;CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id + amount) :: TEXT :: BYTEA)) STORED
)из конкатенированный строки wallet_id + hash
CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id :: TEXT || amount :: TEXT) :: BYTEA)) STORED
)CREATE DEFINER=`sql_test`@`%` EVENT `Маркировка`
ON SCHEDULE
EVERY 1 HOUR
STARTS '2022-05-29 23:15:23'
-- ON COMPLETION NOT PRESERVE -- не имеет смысла для периодической задачи
-- ENABLE -- по умолчанию эвент включен при создании
DO
UPDATE `tasks`
SET `status` = 4
WHERE `status` = 3
AND EXISTS ( SELECT NULL
FROM `config`
WHERE `name` = 'profile_deleting'
AND `value` = 'allowed' );CREATE DEFINER=`sql_test`@`%` EVENT `Маркировка`
ON SCHEDULE
EVERY 1 HOUR
STARTS '2022-05-29 23:15:23'
-- ON COMPLETION NOT PRESERVE
-- ENABLE
DO
BEGIN
IF (SELECT `value` FROM `config` WHERE `name` = 'profile_deleting') = 'allowed' THEN
UPDATE `tasks` SET `status` = 4 WHERE `status` = 3;
END IF;
END;IF 'allowed' IN (SELECT `value` FROM `config` WHERE `name` = 'profile_deleting') THENЗатупил.. Нашел вариант..
UPDATE request
JOIN ( SELECT MAX(id) id
FROM request
WHERE user_id IN ( '97' /* список */ )
) subquery USING ( id )
SET request.message = 'text';