日期:2014-05-17  浏览次数:20500 次

怎么去掉多表查询结果中的重复字段?
SQL code
SELECT
    字段序号=a.colorder ,
    字段名=a.name ,
     外键字段所在的表=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
                THEN object_name(tony.rkeyid) ELSE ''
           END ,
     外键字段=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
                 THEN (SELECT name FROM syscolumns
                       WHERE colid=tony.fkey AND id=tony.fkeyid)      
                 ELSE ''
             END,
     类型=b.name 
FROM dbo.syscolumns a
    LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype 
    INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
    LEFT JOIN sysobjects htl ON htl.parent_obj=d.id AND htl.xtype='F'
    LEFT JOIN sysforeignkeys tony on htl.id=tony.constid
WHERE d.name='Articles'  --这里输入包含表名称的条件
ORDER BY d.id, a.colorder



------解决方案--------------------
SQL code
SELECT DISTINCT
    字段序号=a.colorder ,
    字段名=a.name ,
     外键字段所在的表=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
                THEN object_name(tony.rkeyid) ELSE ''
           END ,
     外键字段=CASE WHEN tony.fkey is not null and tony.fkey=a.colid
                 THEN (SELECT name FROM syscolumns
                       WHERE colid=tony.fkey AND id=tony.fkeyid)      
                 ELSE ''
             END,
     类型=b.name 
FROM dbo.syscolumns a
    LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype 
    INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0
    LEFT JOIN sysobjects htl ON htl.parent_obj=d.id AND htl.xtype='F'
    LEFT JOIN sysforeignkeys tony on htl.id=tony.constid
WHERE d.name='Articles'  --这里输入包含表名称的条件