select field1 as 列名,sum(field2) as 总和,avg(field3) as 平均数 from tab group by field1
你是要找指定表的这些字段吗? --select * from syscolumns where id=object_id('表名') 你将看到所有字段的属性
--得到表中的所有列信息(包含数据类型名): SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND "syscolumns.id = object_id('tableName')然后得到列后动态拼接。
CREATE TABLE AA ( ID INT, NAME CHAR(4), [MONEY] DECIMAL(5, 2) )INSERT INTO AA VALUES (1, '张三', 150.50) INSERT INTO AA VALUES (3, '李四', 200)DECLARE @tblname NVARCHAR(10), @colname NVARCHAR(10), @sql NVARCHAR(4000) SELECT @tblname = 'AA'DECLARE CUR CURSOR FOR SELECT A.name + '.' + B.name AS [column] FROM sys.tables A, sys.columns B, sys.types C WHERE A.[object_id] = B.[object_id] AND B.user_type_id = C.user_type_id AND C.name IN ('int', 'decimal') AND A.name = @tblname OPEN CUR FETCH NEXT FROM CUR INTO @colname WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @sql = ISNULL(@sql, '') + ' SELECT ''' + @colname + ''' AS COLUMN_NAME, SUM(' + @colname + ') AS COLUMN_SUM, AVG(' + @colname + ') AS COLUMN_AVG FROM ' + @tblname + ' UNION ' FETCH NEXT FROM CUR INTO @colname END CLOSE CUR DEALLOCATE CUR IF (LEN(@sql) > 0) SELECT @sql = LEFT(@sql, LEN(@sql) - 6) EXEC sp_executesql @sql
看起来像是作业题,不过现在的作业题好高级 USE TEMPDB GO IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB( COL1 INT ,COL2 FLOAT ,COL3 VARCHAR(10) ) INSERT INTO TB SELECT 1,2.2,'A' UNION ALL SELECT 2,3.3,'B' GO DECLARE @SQL VARCHAR(MAX) SELECT @SQL=ISNULL(@SQL+' UNION ALL ','')+'SELECT '''+COL.NAME+''' AS [列名],SUM('+COL.NAME+') AS [总和],AVG('+COL.NAME+') AS [平均数] FROM TB ' FROM SYS.COLUMNS COL INNER JOIN SYS.TYPES TYPES ON COL.SYSTEM_TYPE_ID=TYPES.SYSTEM_TYPE_ID WHERE OBJECT_NAME(OBJECT_ID)='TB' AND TYPES.NAME IN ('tinyint','SMALLINT','INT','BIGINT','REAL','MONEY','NUMERIC','FLOAT','DECIMAL') EXEC (@SQL) /* 列名 总和 平均数 COL1 3 1 COL2 5.5 2.75 */
create table chu(id char(3),sname char(5),sno int,sage int)insert into chu select '001','lx',5,17 union all select '002','su',6,18 union all select '003','wg',8,20 union all select '004','hj',9,21select * from chuid sname sno sage ---- ----- ----------- ----------- 001 lx 5 17 002 su 6 18 003 wg 8 20 004 hj 9 21 declare @sql varchar(6000)=''select @sql=@sql +' union all select '''+b.name+''' ''列名'',sum('+b.name+') ''总和'',avg('+b.name+') ''平均数'' from chu' from sys.objects a inner join sys.columns b on a.object_id=b.object_id inner join sys.types c on b.system_type_id=c.system_type_id where a.[type]='U' and a.name='chu' and c.name in ('int','decimal')select @sql=stuff(@sql,1,11,'')exec(@sql) 列名 总和 平均数 ---- ----------- ----------- sno 28 7 sage 76 19(2 row(s) affected)
--select * from syscolumns where id=object_id('表名')
你将看到所有字段的属性
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND "syscolumns.id = object_id('tableName')然后得到列后动态拼接。
CREATE TABLE AA
(
ID INT,
NAME CHAR(4),
[MONEY] DECIMAL(5, 2)
)INSERT INTO AA VALUES (1, '张三', 150.50)
INSERT INTO AA VALUES (3, '李四', 200)DECLARE @tblname NVARCHAR(10),
@colname NVARCHAR(10),
@sql NVARCHAR(4000)
SELECT @tblname = 'AA'DECLARE CUR CURSOR
FOR
SELECT A.name + '.' + B.name AS [column]
FROM sys.tables A, sys.columns B, sys.types C
WHERE A.[object_id] = B.[object_id]
AND B.user_type_id = C.user_type_id
AND C.name IN ('int', 'decimal')
AND A.name = @tblname
OPEN CUR
FETCH NEXT FROM CUR INTO @colname
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql = ISNULL(@sql, '') + ' SELECT ''' + @colname + ''' AS COLUMN_NAME, SUM(' + @colname + ') AS COLUMN_SUM, AVG(' + @colname + ') AS COLUMN_AVG FROM ' + @tblname + ' UNION '
FETCH NEXT FROM CUR INTO @colname
END
CLOSE CUR
DEALLOCATE CUR
IF (LEN(@sql) > 0)
SELECT @sql = LEFT(@sql, LEN(@sql) - 6)
EXEC sp_executesql @sql
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 INT
,COL2 FLOAT
,COL3 VARCHAR(10)
)
INSERT INTO TB
SELECT 1,2.2,'A' UNION ALL
SELECT 2,3.3,'B'
GO
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=ISNULL(@SQL+'
UNION ALL ','')+'SELECT '''+COL.NAME+''' AS [列名],SUM('+COL.NAME+') AS [总和],AVG('+COL.NAME+') AS [平均数] FROM TB '
FROM SYS.COLUMNS COL
INNER JOIN SYS.TYPES TYPES ON COL.SYSTEM_TYPE_ID=TYPES.SYSTEM_TYPE_ID
WHERE OBJECT_NAME(OBJECT_ID)='TB' AND TYPES.NAME IN ('tinyint','SMALLINT','INT','BIGINT','REAL','MONEY','NUMERIC','FLOAT','DECIMAL')
EXEC (@SQL)
/*
列名 总和 平均数
COL1 3 1
COL2 5.5 2.75
*/
create table chu(id char(3),sname char(5),sno int,sage int)insert into chu
select '001','lx',5,17 union all
select '002','su',6,18 union all
select '003','wg',8,20 union all
select '004','hj',9,21select * from chuid sname sno sage
---- ----- ----------- -----------
001 lx 5 17
002 su 6 18
003 wg 8 20
004 hj 9 21
declare @sql varchar(6000)=''select @sql=@sql
+' union all select '''+b.name+''' ''列名'',sum('+b.name+') ''总和'',avg('+b.name+') ''平均数'' from chu'
from sys.objects a
inner join sys.columns b
on a.object_id=b.object_id
inner join sys.types c
on b.system_type_id=c.system_type_id
where a.[type]='U' and a.name='chu'
and c.name in ('int','decimal')select @sql=stuff(@sql,1,11,'')exec(@sql)
列名 总和 平均数
---- ----------- -----------
sno 28 7
sage 76 19(2 row(s) affected)