-- селект номеров строчек в разрезе дискусий. аналог 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 = 1CREATE 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;CREATE TABLE #Hours (FixHours INT, AddHours INT, CalCulatedValue INT)
INSERT INTO #Hours VALUES (10,0,14),(5,0,14),(5,1,16),(0,5,17)
SELECT * FROM #Hours h
SELECT FixHours, AddHours, CalCulatedValue FROM #Hours h
WHERE NOT (h.FixHours > 0 AND h.AddHours > 0)
UNION ALL
SELECT FixHours, 0, h.CalCulatedValue FROM #Hours h
WHERE h.FixHours > 0 AND h.AddHours > 0
UNION ALL
SELECT 0, AddHours, h.CalCulatedValue FROM #Hours h
WHERE h.FixHours > 0 AND h.AddHours > 0DECLARE @tableName NVARCHAR(20);
SELECT @tableName = dictionary_link FROM table1;
EXEC(' SELECT * FROM '+@tableName+' WHERE 1 = 1 ');CREATE TABLE #test (id INT);
INSERT INTO #test EXEC(' SELECT id FROM '+@tableName+' WHERE 1 = 1 ');
-- или так:
EXEC(' SELECT id INTO #test FROM '+@tableName+' WHERE 1 = 1 ');SELECT
UserId, [Resource] = ResourceId, table_num,
[Action] = CASE WHEN table_num = 1 THEN ... -- table_num тут индикатор из какого массива запись.
FROM (
SELECT UserId, ResourceId, AssignmentId,table_num = min(table_num)
FROM (
SELECT DISTINCT UserId = M2.UserId
, ResourceId = ISNULL(M2.ResourceId,'00000000-0000-0000-0000-000000000000')
, AssignmentId = ISNULL(M2.AssignmentId, '00000000-0000-0000-0000-000000000000')
,1 [table_num] from #newMatrix M2
UNION
SELECT DISTINCT UserId = M2.UserId
, ResourceId = ISNULL(M2.ResourceId,'00000000-0000-0000-0000-000000000000')
, AssignmentId = ISNULL(M2.AssignmentId, '00000000-0000-0000-0000-000000000000')
,2 from #oldMatrix M2
) a
GROUP BY UserId, ResourceId, AssignmentId
HAVING COUNT(*) <> 2 -- одинаковые записи в обеих таблицах, останется только разница.
) S
<code>
</code>