--测试数据 if not object_id(N'T') is null drop table T Go Create table T([id1] int,[id2] int,[id3] int) Insert T select 1,2,3 Go --测试数据结束 DECLARE @sql NVARCHAR(max)='select (select Max(MaxValue) from (values ' SELECT @sql = @sql + STUFF( ( SELECT ',(' + name + ')' FROM syscolumns WHERE id = ( SELECT MAX(id) FROM sysobjects WHERE xtype = 'u' AND name = 'T' --表名 ) FOR XML PATH('') ), 1, 1, '' ); SET @sql=@sql+') as #temp(MaxValue)) as MaxValue from T' EXEC(@sql)
CREATE TABLE A ( A INT , B INT, C INT, D INT ) INSERT INTO A VALUES(1,2,3,4),(9,2,4,8),(2,5,4,7)SELECT *,(SELECT MAX(x) FROM (VALUES(a),(b),(c),(d) ) tab(x)) FROM A
通过表名,查询出列名来。然后通过动态sql去查找最大最小值。
如果有多行,可以这样处理(id为主键,不做比较,后边三列为比较列) --测试数据 if not object_id(N'T') is null drop table T Go Create table T(id INT,[id1] int,[id2] int,[id3] int) Insert T select 1,1,2,3 UNION ALL select 2,9,8,7 Go --测试数据结束 DECLARE @sql NVARCHAR(max)='select id, (select Max(MaxValue) from (values ' SELECT @sql = @sql + STUFF( ( SELECT ',(' + name + ')' FROM syscolumns WHERE id = ( SELECT MAX(id) FROM sysobjects WHERE xtype = 'u' AND name = 'T' --表名 ) FOR XML PATH('') ), 1, 1, '' ); SET @sql=@sql+') as #temp(MaxValue)) as MaxValue from T' EXEC(@sql)
没这种东西, 只能自己写动态sql来完成。
-- 很早之前,有个兄弟写的,忘了是谁了,我记录了一下,供楼主参考。 DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int) INSERT INTO @T SELECT 4,3,4,1,null UNION ALL SELECT 3,2,2,1,null UNION ALL SELECT 2,2,3,4,null UNION ALL SELECT 4,4,3,4,null UNION ALL SELECT 3,1,2,2,null
SELECT A.*from @T A FOR XML PATH('') --SQL查询如下:
UPDATE A SET lvl = B.x.value('min(//row/*)','int') FROM @T AS A CROSS APPLY (SELECT x = (SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
if not object_id(N'T') is null
drop table T
Go
Create table T([id1] int,[id2] int,[id3] int)
Insert T
select 1,2,3
Go
--测试数据结束
DECLARE @sql NVARCHAR(max)='select (select Max(MaxValue) from (values '
SELECT
@sql = @sql + STUFF(
(
SELECT
',(' + name + ')'
FROM
syscolumns
WHERE
id =
(
SELECT
MAX(id)
FROM
sysobjects
WHERE
xtype = 'u'
AND name = 'T' --表名
)
FOR XML PATH('')
), 1, 1, ''
);
SET @sql=@sql+') as #temp(MaxValue)) as MaxValue from T'
EXEC(@sql)
CREATE TABLE A
(
A INT ,
B INT,
C INT,
D INT
)
INSERT INTO A VALUES(1,2,3,4),(9,2,4,8),(2,5,4,7)SELECT *,(SELECT MAX(x) FROM (VALUES(a),(b),(c),(d) ) tab(x)) FROM A
--测试数据
if not object_id(N'T') is null
drop table T
Go
Create table T(id INT,[id1] int,[id2] int,[id3] int)
Insert T
select 1,1,2,3 UNION ALL
select 2,9,8,7
Go
--测试数据结束
DECLARE @sql NVARCHAR(max)='select id, (select Max(MaxValue) from (values '
SELECT
@sql = @sql + STUFF(
(
SELECT
',(' + name + ')'
FROM
syscolumns
WHERE
id =
(
SELECT
MAX(id)
FROM
sysobjects
WHERE
xtype = 'u'
AND name = 'T' --表名
)
FOR XML PATH('')
), 1, 1, ''
);
SET @sql=@sql+') as #temp(MaxValue)) as MaxValue from T'
EXEC(@sql)
-- 很早之前,有个兄弟写的,忘了是谁了,我记录了一下,供楼主参考。
DECLARE @T TABLE (lvl1 int,lvl2 int,lvl3 int,lvl4 int,lvl int)
INSERT INTO @T
SELECT 4,3,4,1,null UNION ALL
SELECT 3,2,2,1,null UNION ALL
SELECT 2,2,3,4,null UNION ALL
SELECT 4,4,3,4,null UNION ALL
SELECT 3,1,2,2,null
SELECT A.*from @T A FOR XML PATH('')
--SQL查询如下:
UPDATE A SET
lvl = B.x.value('min(//row/*)','int')
FROM @T AS A
CROSS APPLY (SELECT x = (SELECT A.* FOR XML PATH('row'),TYPE)) AS B;
SELECT * FROM @T;