sql – 从已连接表的列表中选择唯一列名
发布时间:2021-05-25 17:59:07 所属栏目:MsSql教程 来源:网络整理
导读:我有一个表列表,可以通过相同的PK列连接在一起.由于这个表列表可能因项目而异,我想创建一个足够动态的查询来从这些表中提取所有唯一列. For example,I have three tables below:Table A (PK field,column1,column 2)Table B (PK field,column3,column 4)Table
副标题[/!--empirenews.page--]
我有一个表列表,可以通过相同的PK列连接在一起.由于这个表列表可能因项目而异,我想创建一个足够动态的查询来从这些表中提取所有唯一列. For example,I have three tables below: Table A (PK field,column1,column 2) Table B (PK field,column3,column 4) Table C (PK field,column5,column 5) 这三个表连接在“PK字段”列上,我希望查询输出类似于: PK field column1 column2 column3 column4 column5 ..data.. ..data.. ..data.. ..data.. ..data.. ..data.. 最后,此查询将成为SQL函数或SP的一部分,因此用户可以定义表的列表,并在开头的PK字段,然后执行它将返回我的预期输出与数据集. 我想在下面使用这个查询,但结果不是我喜欢的: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' 有关如何设计此SP或功能的任何建议,将不胜感激. 提前致谢. 两个示例表的DDL: CREATE TABLE [dbo].[G_bDEM]( [blaiseKey_code] [nvarchar](255) NULL,[qSex] [int] NULL,[qDOB] [datetime] NULL,[qDOBNR] [int] NULL,[qAge] [int] NULL,[qAgeNR] [int] NULL,[qAgeRange] [int] NULL,[qAge15OrOver] [int] NULL,[qNotEligible] [nvarchar](1) NULL,[qBornInNZ] [int] NULL,[qCountryOfBirth] [nvarchar](2) NULL,[qArriveNZYr] [int] NULL,[qArriveNZYrNR] [int] NULL,[qArriveNZMth] [int] NULL,[bDEM_BOP_qHowManyRaised] [int] NULL,[bDEM_BOP_q1stParentBornNZ] [int] NULL,[bDEM_BOP_q2ndParentBornNZ] [int] NULL,[bDEM_BOP_qHowManyParentBornNZ] [int] NULL,[qMaoriDescent] [int] NULL,[qSchQual] [int] NULL,[qSchQualOth] [nvarchar](200) NULL,[qSchQualOthNR] [int] NULL,[qSchQualYr] [int] NULL,[qSchQualYrNR] [int] NULL,[qPostSchQual] [int] NULL,[q3MthsStudy] [int] NULL,[qHighestQual] [int] NULL,[qHighestQualOth] [nvarchar](200) NULL,[qHighestQualOthNR] [int] NULL,[qHighestQualYr] [int] NULL,[qHighestQualYrNR] [int] NULL,[qWorkIntro] [nvarchar](1) NULL,[qDidPaidWork] [int] NULL,[qAwayFromWork] [int] NULL,[qFamilyBusWork] [int] NULL,[bDEM_WOR_qPaidWorkIntro] [nvarchar](1) NULL,[bDEM_WOR_qJobsNum] [int] NULL,[bDEM_WOR_qJobsNumNR] [int] NULL,[bDEM_WOR_tabDEM_T2_fTotMins] [int] NULL,[bDEM_WOR_q2JobsNoHrsIntro] [nvarchar](1) NULL,[bDEM_WOR_q2Jobs2HrsIntro] [nvarchar](1) NULL,[bDEM_WOR_q2Jobs1HrsIntro] [nvarchar](1) NULL,[bDEM_WOR_qOccupation] [nvarchar](200) NULL,[bDEM_WOR_qOccupationNR] [int] NULL,[bDEM_WOR_qMainTasks] [nvarchar](200) NULL,[bDEM_WOR_qMainTasksNR] [int] NULL,[bDEM_WOR_qFeelAboutJob] [int] NULL,[bDEM_WOR_qEmployArrangement] [int] NULL,[bDEM_WOR_qPermEmployee] [int] NULL,[qHasJobToStart] [int] NULL,[qLookedForWork] [int] NULL,[qJobSearchA] [int] NULL,[qJobSearchB] [int] NULL,[qJobSearchC] [int] NULL,[qJobSearchD] [int] NULL,[qJobSearchE] [int] NULL,[qJobSearchF] [int] NULL,[qJobSearchG] [int] NULL,[qJobSearchH] [int] NULL,[qJobSearchI] [int] NULL,[qJobSearchOth] [nvarchar](200) NULL,[qJobSearchOthNR] [int] NULL,[qCouldStartLastWk] [int] NULL,[qIncTotalAmt] [int] NULL,[fCountryName] [nvarchar](60) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[G_bLWW]( [blaiseKey_code] [nvarchar](255) NULL,[qThingsWorthwhileScale] [int] NULL ) ON [PRIMARY] 解决方法此脚本为具有类似PK名称的任何表生成动态SQL.查询: SET NOCOUNT ON IF OBJECT_ID (N'dbo.A') IS NOT NULL DROP TABLE dbo.A IF OBJECT_ID (N'dbo.B') IS NOT NULL DROP TABLE dbo.B IF OBJECT_ID (N'dbo.C') IS NOT NULL DROP TABLE dbo.C CREATE TABLE dbo.A (PK_field INT PRIMARY KEY,column1 INT,column2 INT) CREATE TABLE dbo.B (PK_field INT PRIMARY KEY,column3 INT,column4 INT) CREATE TABLE dbo.C (PK_field INT PRIMARY KEY,column5 INT,[column 6] INT) INSERT INTO dbo.A (PK_field,column2) VALUES (1,1,2),(2,2) INSERT INTO dbo.B (PK_field,column4) VALUES (2,3,4) INSERT INTO dbo.C (PK_field,[column 6]) VALUES (1,5,6),(3,6) DECLARE @SQL NVARCHAR(MAX) ;WITH cte AS ( SELECT column_name = '[' + c.name + ']',table_name = '[' + s.name + '].[' + o.name + ']' FROM sys.columns c WITH (NOLOCK) JOIN sys.objects o WITH (NOLOCK) ON c.[object_id] = o.[object_id] JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.name IN ('A','B','C') AND s.name = 'dbo' AND o.[type] = 'U' ),unicol AS ( SELECT TOP 1 column_name FROM cte GROUP BY cte.column_name HAVING COUNT(cte.column_name) > 1 ),cols AS ( SELECT DISTINCT column_name FROM cte ),tbl AS ( SELECT DISTINCT table_name FROM cte ),rs AS ( SELECT tbl.table_name,column_name = ISNULL(cte.column_name,cols.column_name + ' = NULL') FROM cols CROSS JOIN tbl LEFT JOIN cte ON cols.column_name = cte.column_name AND cte.table_name = tbl.table_name ),rs2 AS ( SELECT uni = ' UNION ALL' + CHAR(13) + 'SELECT ' + STUFF(( SELECT ',' + rs.column_name FROM rs WHERE tbl.table_name = rs.table_name GROUP BY rs.column_name ORDER BY rs.column_name FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),2,'') + ' FROM ' + table_name FROM tbl ) SELECT @SQL = 'SELECT ' + STUFF(( SELECT CHAR(13) + ',' + ISNULL(unicol.column_name,cols.column_name + ' = MAX(' + cols.column_name + ')') FROM cols LEFT JOIN unicol ON cols.column_name = unicol.column_name FOR XML PATH(''),' ') + ' FROM (' + STUFF(( SELECT CHAR(10) + uni FROM rs2 FOR XML PATH(''),11,'') + CHAR(13) + ') t GROUP BY ' + (SELECT column_name FROM unicol) PRINT @SQL EXECUTE sys.sp_executesql @SQL (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |