这样的Sql语句能简化吗？

[软若石]

SELECT SubType.SubTypeName, SubType_1.SubTypeName,
SubType_2.SubTypeName
FROM Customer INNER JOIN
SubType ON Customer.Field1 = SubType.SubTypeID INNER JOIN
SubType SubType_1 ON Customer.Field2 = SubType_1.SubTypeID INNER JOIN
SubType SubType_2 ON Customer.Field3 = SubType_2.SubTypeID

Customer里面的Field1到Field20都是存的SubType的SubTypeID的值。而显示的时候，需要显示SubTypeID对应的SubTypeName的值，上面是取了3个。如果我要是取20个，是不是得像上面一样再写出来17个。像这样的语句能不能简化？

[软若石]

UUUPPP

[还在加载中灬]

你就从了吧！

[Landa_Jimmy]

这样已经很简化了。

[软若石]

应该还有更好的办法吧！！！^_^

[Tiger_Zhao]

这样写也不简单，效率又不高。

WITH /* 测试数据
Customer(id, 
              Field1, Field2, Field3, Field4, Field5,
              Field6, Field7, Field8, Field9, Field10,
              Field11, Field12, Field13, Field14, Field15,
              Field16, Field17, Field18, Field19, Field20) AS (
    SELECT 1,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 UNION ALL
    SELECT 2,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26
),
SubType(SubTypeID,SubTypeName) AS (
    SELECT number+1, CHAR(65+number)
      FROM master..spt_values
     WHERE type = 'p'
       AND number < 26
), */
t1 AS (
 SELECT *
   FROM (
         SELECT id, 
                Field1, Field2, Field3, Field4, Field5,
                Field6, Field7, Field8, Field9, Field10,
                Field11, Field12, Field13, Field14, Field15,
                Field16, Field17, Field18, Field19, Field20
           FROM Customer
        ) t
UNPIVOT (
         SubTypeID
         FOR field IN (Field1, Field2, Field3, Field4, Field5,
                       Field6, Field7, Field8, Field9, Field10,
                       Field11, Field12, Field13, Field14, Field15,
                       Field16, Field17, Field18, Field19, Field20)
        ) u
)
,t2 AS (
    SELECT t1.id, t1.field, SubType.SubTypeName
      FROM t1
      JOIN SubType
        ON t1.SubTypeID = SubType.SubTypeID
)
SELECT *
   FROM t2
 PIVOT (
        MAX(SubTypeName)
         FOR field IN (Field1, Field2, Field3, Field4, Field5,
                       Field6, Field7, Field8, Field9, Field10,
                       Field11, Field12, Field13, Field14, Field15,
                       Field16, Field17, Field18, Field19, Field20)
       ) p

         id Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9 Field10 Field11 Field12 Field13 Field14 Field15 Field16 Field17 Field18 Field19 Field20
----------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
          1 A      B      C      D      E      F      G      H      I      J       K       L       M       N       O       P       Q       R       S       T
          2 G      H      I      J      K      L      M      N      O      P       Q       R       S       T       U       V       W       X       Y       Z

[yooq_csdn]

如果数据量不大 可以把Field1到Field20 连起来 用 like 关联另一个表

[rfq]

语句已经连接 您可以 用连接提示 和索引优化

[lihaoran_1991]

还想怎么简化？

[薛定谔的DBA]

表不是太大的话，SubType 用临时表。另外Customer 没有用到列，用exists 来判断

[习惯性蹭分]

subtype表數據量不是很多的話可以建個返回subTypename的函數。 查詢時把字段傳進去就可以了。

create function fn_getName(@SubTypeID int)
returns Nvarchar(100)
as begin
 declare @subTypeName Nvarchar(100)
 select @subTypeName=subTypeName from subType
 where subTypeID=@SubTypeID
 return @subTypeName
 end
go
/*
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'dbo.fn_getName(Field'+ltrim(number)+') as subTypeName'+ltrim(number)+char(13)
from master..spt_values where type='p'
and number>0 and number<21
set  @sql='select '+@sql+' from customer'
print @sql
*/
select dbo.fn_getName(Field1) as subTypeName1
,dbo.fn_getName(Field2) as subTypeName2
,dbo.fn_getName(Field3) as subTypeName3
,dbo.fn_getName(Field4) as subTypeName4
,dbo.fn_getName(Field5) as subTypeName5
,dbo.fn_getName(Field6) as subTypeName6
,dbo.fn_getName(Field7) as subTypeName7
,dbo.fn_getName(Field8) as subTypeName8
,dbo.fn_getName(Field9) as subTypeName9
,dbo.fn_getName(Field10) as subTypeName10
,dbo.fn_getName(Field11) as subTypeName11
,dbo.fn_getName(Field12) as subTypeName12
,dbo.fn_getName(Field13) as subTypeName13
,dbo.fn_getName(Field14) as subTypeName14
,dbo.fn_getName(Field15) as subTypeName15
,dbo.fn_getName(Field16) as subTypeName16
,dbo.fn_getName(Field17) as subTypeName17
,dbo.fn_getName(Field18) as subTypeName18
,dbo.fn_getName(Field19) as subTypeName19
,dbo.fn_getName(Field20) as subTypeName20
 from customer