如何去掉多表查询结果中的重复字段
日期：2014-05-17 浏览次数：20591 次
怎么去掉多表查询结果中的重复字段？
------解决方案--------------------
- 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' --这里输入包含表名称的条件
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
