SQL语句在内部联接查询中的应用

Question

我有三张桌子的图片和参数如下所示。我在构建查询时遇到了问题。确切的结果不是在一个查询中显示。我想从主表中得到信息。

在表客户中，我正在填充来自正规和主表的数据，并在主表下填充正则数据。我想将下面的查询合并

查询1

SELECT Customer.CustomerId,
       Customer.RefId,
       Regular.LicenseId,
       Regular.ControlId,
       Master.MasterId,
       Master.FullName,
       Master.profile
FROM   Customer
       INNER JOIN Regular
               ON Customer.RefId = Regular.LicenseId
       INNER JOIN Master
               ON Regular.ControlId = Master.MasterId 

查询2

SELECT Customer.CustomerId,
       Customer.RefId,
       Master.FullName,
       Master.profile
FROM   Customer
       INNER JOIN Master
               ON Customer.RefId = Master.MasterId 

结果

​

​

如何在一个查询中获得这两个结果。我厌倦了用不同的说法。我能找到解决办法吗..。

Answer 1

UNION应该包含相同数量的列

SELECT Customer.CustomerId,
       Customer.RefId,
       Regular.LicenseId,
       Regular.ControlId,
       Master.MasterId,
       Master.FullName,
       Master.profile
FROM   Customer
       INNER JOIN Regular
               ON Customer.RefId = Regular.LicenseId
       INNER JOIN Master
               ON Regular.ControlId = Master.MasterId
UNION ALL
SELECT Customer.CustomerId,
       Customer.RefId,
       '' LicenseId,
       '' ControlId,
       '' MasterId,
       Master.FullName,
       Master.profile
FROM   Customer
       INNER JOIN Master
               ON Customer.RefId = Master.MasterId 

更新

声明一个参数来传递查询字符串(r%或m%)

-- DemoProc 'R'
CREATE PROCEDURE Demoproc @param VARCHAR(50)
AS
  BEGIN
      SET NOCOUNT ON;

      IF( @param LIKE 'R%' )
        BEGIN
            SELECT Customer.CustomerId,
                   Customer.RefId,
                   Regular.LicenseId,
                   Regular.ControlId,
                   Master.MasterId,
                   Master.FullName,
                   Master.profile
            FROM   Customer
                   INNER JOIN Regular
                           ON Customer.RefId = Regular.LicenseId
                   INNER JOIN Master
                           ON Regular.ControlId = Master.MasterId
        END
      ELSE IF( @param LIKE 'M%' )
        BEGIN
            SELECT Customer.CustomerId,
                   Customer.RefId,
                   '' LicenseId,
                   '' ControlId,
                   '' MasterId,
                   Master.FullName,
                   Master.profile
            FROM   Customer
                   INNER JOIN Master
                           ON Customer.RefId = Master.MasterId
        END
  END

GO