-- селект номеров строчек в разрезе дискусий. аналог ms sql row_number over ( partition by ...
SELECT
@row_number:=CASE WHEN @discussion_id=discussion_id THEN @row_number+1 ELSE 1 END AS ROW_NUMBER
,@discussion_id:=discussion_id AS discussion_id
,message_id
FROM posts, (SELECT @row_number:=0,@discussion_id:=0) AS t
ORDER BY discussion_id
-- http://www.mysqltutorial.org/mysql-update-join/
UPDATE posts p
JOIN (
SELECT
@row_number:=CASE WHEN @discussion_id=discussion_id THEN @row_number+1 ELSE 1 END AS row_number
,@discussion_id:=discussion_id AS discussion_id
,message_id
FROM posts, (SELECT @row_number:=0,@discussion_id:=0) AS t
) r
ON r.discussion_id = p.discussion_id AND r.message_id = p.message_id
SET p.number = r.row_number-- вариант 1
SELECT s.id, s.Column1, s.Column2
FROM (
SELECT t.id, t.Column1, t.Column2, RN = ROW_NUMBER() OVER (PARTITION BY t.Column2 ORDER BY id ASC)
FROM test1 AS t
) S
WHERE S.RN = 1
-- вариант 2 с CTE
;WITH S AS (
SELECT t.id, t.Column1, t.Column2, RN = ROW_NUMBER() OVER (PARTITION BY t.Column2 ORDER BY id ASC)
FROM test1 AS t
)
SELECT s.id, s.Column1, s.Column2
FROM s
WHERE S.RN = 1SELECT S.workday, S.smena, S.userid, COUNT(*)
FROM (
SELECT t.dtime, t.userid,
CONVERT( CASE WHEN HOUR(t.dtime) >= 22 THEN DATE_ADD(t.dtime,INTERVAL 1 DAY) ELSE t.dtime END, DATE) as 'workday',
CASE WHEN HOUR(t.dtime) >= 10 AND HOUR(t.dtime) < 22 THEN 'day' ELSE 'night' END as 'smena'
FROM test AS t
) S
GROUP BY workday, smena, userid
ORDER BY S.workday ASC, S.smena DESC, S.useridCREATE TABLE dbo.CONTENT_TFS2 (
TFS_ID BIGINT IDENTITY(1, 1) NOT NULL,
TFS_CONTENT NVARCHAR(1024) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO CONTENT_TFS2 (TFS_CONTENT)
VALUES
('111'),
('222')SET IDENTITY_INSERT CONTENT_TFS2 ON
INSERT INTO CONTENT_TFS2 (TFS_ID,TFS_CONTENT)
VALUES
(7,'333'),
(8,'555')
SET IDENTITY_INSERT CONTENT_TFS2 OFF-- drop table test
CREATE TABLE test (id INT IDENTITY(1,1) NOT NULL, t1 INT, t2 INT, t3 INT);
GO
INSERT INTO test (t1, t2, t3)
VALUES (1,2,0),(3,4,0),(5,6,1)
GO
CREATE TRIGGER TG_testModify ON test AFTER INSERT,UPDATE
AS
BEGIN
IF UPDATE([t1])
SELECT d.t1 AS t1_old, i.t1 AS t1_new, 'Column_t1'
FROM INSERTED AS i
LEFT JOIN DELETED AS d ON i.id = d.id
WHERE ( NULLIF(d.t1, i.t1) IS NOT NULL AND d.t1 IS NOT NULL )
OR ( NULLIF(i.t1, d.t1) IS NOT NULL AND d.t1 IS NULL )
END
GO
UPDATE test SET t1 = 4 WHERE t3 = 0;
UPDATE test SET t1 = NULL WHERE t3 = 1;
UPDATE test SET t1 = 2 WHERE t3 = 1;WHETE id NOT IN
(
SELECT t2.id
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.id
WHERE t1.id = 1 ---- тут ваша категория переносимая
UNION
SELECT t3.id
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.id
WHERE t1.id = 1 ---- тут ваша категория переносимая
)WITH RECURSIVE CTE as (
select Id, Name, parent_id, 0 as Level
from categories
where parent_id = @IdCategory ---- тут ваша категория переносимая
union all
select
t2.Id, t2.Name, t2.parent_id, Level + 1
from categories t2
join CTE on CTE.Id = t2.parent_id
)
SELECT * FROM categories
WHERE Id NOT IN (SELECT Id FROM CTE )