主页 / dba / 问题 / 232702
Accepted
Tom_W
Asked: 2019-03-22 01:42:30 +0800 CST

在选择中两次使用同一个表会返回重复的行

  • 772

我目前正在尝试整理一些可以告诉我每个客户使用的两个重要表的名称的东西。遗憾的是，这些表的名称并不总是相同，所以我目前想出了以下 T-SQL；

create table #test  (   [Database] sysname,
                                    [TenantId] sysname,
                                    [TasksTable] sysname,
                                    [TimeRecordTable] sysname   )

insert into #test
select      db_name() as [Database], 
                SCHEMA_NAME(tbl.schema_id) as [TenantId], 
                tbl.name AS [TasksTable],
                tbl2.name AS [TimeRecordTable]
from    sys.tables tbl, 
            sys.tables tbl2
where tbl.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') 
and     tbl2.name IN ('TaskTimeRecord','TaskTimeRecord_NEW')
and     SCHEMA_NAME(tbl.schema_id) = '2'

select * from #test
drop table #test

运行此程序时，我得到了 200 多行重复数据，而不是包含我正在寻找的结果的单行。

有人对如何使它工作有任何建议吗？

sql-server

1 个回答

  1. Best Answer

    您可以使用子查询

    SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       (SELECT t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id) as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

    但是，如果子查询返回多个表名，则查询将失败并出现以下错误：

    子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

    如果可以返回多个表名

    如果由于多个表名导致子查询出现问题，您可以使用FOR XML PATH('').

    SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

    如果子查询和主查询都可以返回多个表名

    而且每个模式仍然需要一行，您可以创建两个逗号分隔的字符串

    SELECT DISTINCT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TaskTable,
       stuff(((SELECT ', '+ t3.Name from sys.tables t3 where t3.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t3.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW','TaskTimeRecord_NEW','TaskTimeRecord');
    • 1

相关问题