👩🏿‍💼 💩 😾 外部キーを介してテーブル内のレコードへのリンクの数を数える 👊🏻 🧒🏽 🦀

独自の目的で次の問題を解決する必要がありました。各レコードの1つのプレート（ファイル）に対して、外部キーを介して接続された外部レコードの数を自動的に計算します。この問題はファイルプレートの特定の構造で解決されましたが、必要に応じて、より普遍的なソリューションに変換できます。

ソリューションは、数百万行のレコードと毎分更新のないアンロードされたデータベース用に作成されたため、パフォーマンスの低下の問題は問題ではありませんでした。

主な理由は、ファイルテーブルへの外部リンクの数が開発プロセス中に変化する可能性があり、クエリを絶えずやり直すことは単純に不合理だからです。システム自体にいくつかのモジュール性が計画されていたため、すべての最終的なテーブルは正確にはわかっていません。

2つのラベルを作成するためのスクリプト：

CREATE TABLE [dbo].[File]( [IdFile] [int] IDENTITY(1, 1) NOT NULL, [NameFile] [nvarchar](max) NOT NULL, [CountUsage] [int] NOT NULL, PRIMARY KEY (IdFile) ) SET identity_insert [dbo].[File] ON; INSERT INTO [dbo].[File] ([IdFile], [NameFile],[CountUsage]) VALUES (1, 'test1', 0), (2, 'test2', 1000) SET identity_insert [dbo].[File] OFF; CREATE TABLE [dbo].[TestForFiles]( [IdTest] [int] IDENTITY(1, 1) NOT NULL, [IdFileForTest] [int] NOT NULL, PRIMARY KEY (IdTest) ) ALTER TABLE [dbo].[TestForFiles] WITH CHECK ADD CONSTRAINT [FK_TestForFiles_File] FOREIGN KEY([IdFileForTest]) REFERENCES [dbo].[File] ([IdFile]) ALTER TABLE [dbo].[TestForFiles] CHECK CONSTRAINT [FK_TestForFiles_File] INSERT INTO [dbo].[TestForFiles] ([IdFileForTest]) VALUES (1), (1), (1), (2)

テーブルFileとTestForFilesを受け取ります。 TestForFilesテーブルは、IdFileForTestフィールドによってFileを参照します。

以下のデータセットが取得されます。

次のスクリプトは、テーブル内のレコード数をカウントするクエリを生成します。

 DECLARE @sql_tables nvarchar(max) = null; SELECT @sql_tables = CASE WHEN @sql_tables IS NULL THEN '' ELSE @sql_tables + CHAR(13) + CHAR(10) + ' UNION ALL' + CHAR(13) + CHAR(10) END + ' SELECT ' + c.name + ' AS IdFile, count(*) AS FileCount FROM ' + t.name + ' GROUP BY ' + c.name FROM sys.foreign_key_columns AS fk INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id INNER JOIN sys.columns AS c2 ON fk.referenced_object_id = c2.object_id AND fk.referenced_column_id = c2.column_id WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables WHERE name = 'File') AND c2.name = 'IdFile'; IF @sql_tables IS NOT NULL BEGIN DECLARE @sql nvarchar(max) = 'UPDATE dbo.[File]' + CHAR(13) + CHAR(10) + 'SET CountUsage = t2.FileCount' + CHAR(13) + CHAR(10) + 'FROM dbo.[File]' + CHAR(13) + CHAR(10) + 'INNER JOIN (' + CHAR(13) + CHAR(10) + ' SELECT IdFile, SUM(FileCount) AS FileCount ' + CHAR(13) + CHAR(10) + ' FROM (' + CHAR(13) + CHAR(10) + @sql_tables + CHAR(13) + CHAR(10) + ' ) t' + CHAR(13) + CHAR(10) + ' GROUP BY IdFile' + CHAR(13) + CHAR(10) + ') t2 ON t2.IdFile = dbo.[File].IdFile'; print @sql; EXEC sp_executesql @sql; END;

このようなクエリは次のとおりです。

 UPDATE dbo.[File] SET CountUsage = t2.FileCount FROM dbo.[File] INNER JOIN ( SELECT IdFile, SUM(FileCount) AS FileCount FROM ( SELECT IdFileForTest AS IdFile, count(*) AS FileCount FROM TestForFiles GROUP BY IdFileForTest ) t GROUP BY IdFile ) t2 ON t2.IdFile = dbo.[File].IdFile

実行の結果、次の表の内容があります。

もう一度繰り返します-タスクは特定のテーブルファイルに対して解決されました。IdFileフィールドに外部キーがある場合にのみ計算が機能します。

あなたの批判を前もってありがとう。

外部キーを介してテーブル内のレコードへのリンクの数を数える

More articles: