多个可匹配字段使用同一函数能简化写法么

[zhufensun4626]

Select 
min(col1),
min(col2),
min(col3),
min(col4),
min(col5),
min(col6),
min(col7),
min(col8)
from
table
group by x

对n个字段名包含'col'字段求Min，能否利用正则简化写进一行

[吉普赛的歌]

哪里还能怎么简化， 如果你只是懒得写每个列， 可以用动态sql:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT IDENTITY(1,1) PRIMARY KEY,
	col1 INT,	
	col2 INT,
	col3 INT,
	col4 INT,	
	col5 INT,
	col6 INT,
	col7 INT,	
	col8 INT,
	x INT
)
GO
--上面是构建测试表


DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+ ',MIN('+ c.name+')
'  FROM sys.[columns] AS c 
WHERE c.[object_id]=OBJECT_ID('t')
	AND c.name NOT IN ('id','x')
SET @sql='Select '+SUBSTRING(@sql,2,LEN(@sql))+'from t group by x ';
PRINT @sql
/*
Select MIN(col1)
,MIN(col2)
,MIN(col3)
,MIN(col4)
,MIN(col5)
,MIN(col6)
,MIN(col7)
,MIN(col8)
from t group by x 
*/
EXEC (@sql)

[二月十六]

借1#版主的代码，稍微改动下

DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+ ',MIN('+ c.name+')
'  FROM sys.[columns] AS c 
WHERE c.[object_id]=OBJECT_ID('t')
    AND c.name LIKE '%col%'   --这里改动了一下
SET @sql='Select '+SUBSTRING(@sql,2,LEN(@sql))+'from t group by x ';
PRINT @sql
/*
Select MIN(col1)
,MIN(col2)
,MIN(col3)
,MIN(col4)
,MIN(col5)
,MIN(col6)
,MIN(col7)
,MIN(col8)
from t group by x 
*/
EXEC (@sql)

[zhufensun4626]

引用 1 楼 yenange 的回复:

哪里还能怎么简化， 如果你只是懒得写每个列， 可以用动态sql:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT IDENTITY(1,1) PRIMARY KEY,
	col1 INT,	
	col2 INT,
	col3 INT,
	col4 INT,	
	col5 INT,
	col6 INT,
	col7 INT,	
	col8 INT,
	x INT
)
GO
--上面是构建测试表


DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+ ',MIN('+ c.name+')
'  FROM sys.[columns] AS c 
WHERE c.[object_id]=OBJECT_ID('t')
	AND c.name NOT IN ('id','x')
SET @sql='Select '+SUBSTRING(@sql,2,LEN(@sql))+'from t group by x ';
PRINT @sql
/*
Select MIN(col1)
,MIN(col2)
,MIN(col3)
,MIN(col4)
,MIN(col5)
,MIN(col6)
,MIN(col7)
,MIN(col8)
from t group by x 
*/
EXEC (@sql)

3Q 版主 hive sql里面也这么写么

[吉普赛的歌]

引用 3 楼 zhufensun4626 的回复:

3Q 版主 hive sql里面也这么写么

https://blog.csdn.net/allen_infosys/article/details/51162145
你用这个看行不行

[zhufensun4626]

引用 4 楼 yenange 的回复:

[quote=引用 3 楼 zhufensun4626 的回复:]
3Q 版主 hive sql里面也这么写么

https://blog.csdn.net/allen_infosys/article/details/51162145
你用这个看行不行[/quote]
好的，我看看，方便加您QQ以后交流学习下么