每个记录都要一个表头的话,只有一个记录就当个一个select语句来... DECLARE @SQL VARCAHR(100),@NUM VARCHAR(8) DECLARE CUR CURSOR FOR SELECT 编号 FROM GZ FOR READ ONLY DECLARE @COUNT INT SET @COUNT=(SELECT COUNT(*) FROM GZ) OPEN CUR WHILE @COUNT<>0 BEGIN FETCH NEXT FROM CUR INTO @NUM SET @SQL=@SQL+' SELECT * FROM GZ WHERE 编号='''+@NUM+''' @COUNT=@COUNT-1 END CLOSE CUR DEALLOCATE CUR EXEC (@SQL) SELECT '合计' AS 合计,'' AS '',sum(isnull(岗位工资,0)) AS 岗位工资, sum(isnull(金额,0)) AS 金额, sum(isnull(加班,0))AS 加班, sum(isnull(扣岗,0)) AS 扣岗 FROM GZ
SELECT 姓名,编号,岗位工资,金额,加班,扣岗 FROM ( SELECT '姓名' AS 姓名,'编号' AS 编号, '岗位工资' AS 岗位工资,'金额' AS 金额, '加班' AS 加班,'扣岗' AS 扣岗,o1=姓名,o2=0 FROM @T UNION ALL SELECT 姓名,编号,RTRIM(岗位工资), RTRIM(金额),RTRIM(加班), RTRIM(扣岗),姓名,1 FROM @T UNION ALL SELECT '','','','','','', 姓名,2 FROM @T WHERE 编号 NOT IN(SELECT TOP 1 编号 FROM @T) ) AS A ORDER BY o1,o2这样的代码如何加入合计那一条呢
DECLARE @SQL VARCAHR(100),@NUM VARCHAR(8) DECLARE CUR CURSOR FOR SELECT 编号 FROM GZ FOR READ ONLY DECLARE @COUNT INT SET @COUNT=(SELECT COUNT(*) FROM GZ) OPEN CUR WHILE @COUNT<>0 BEGIN FETCH NEXT FROM CUR INTO @NUM SET @SQL=@SQL+' SELECT * FROM GZ WHERE 编号='''+@NUM+'''' @COUNT=@COUNT-1 END CLOSE CUR DEALLOCATE CUR EXEC (@SQL) SELECT '合计' AS 合计,'' AS '',sum(isnull(岗位工资,0)) AS 岗位工资, sum(isnull(金额,0)) AS 金额, sum(isnull(加班,0))AS 加班, sum(isnull(扣岗,0)) AS 扣岗 FROM GZ
[code=SQL]declare @T table (姓名 varchar(4),编号 varchar(2),岗位工资 int,金额 int,加班 int,扣岗 int) insert into @T select '张三','01',100,100,50,30 union all select '李四','02',200,100,60,40 union all select '王五','03',100,200,50,20 SELECT 姓名,编号,岗位工资,金额,加班,扣岗 FROM ( SELECT '姓名' AS 姓名,'编号' AS 编号, '岗位工资' AS 岗位工资,'金额' AS 金额, '加班' AS 加班,'扣岗' AS 扣岗,o1=编号,o2=0 FROM @T UNION ALL SELECT 姓名,编号,RTRIM(岗位工资), RTRIM(金额),RTRIM(加班), RTRIM(扣岗),编号,1 FROM @T UNION ALL SELECT '','','','','','', 编号,2 FROM @T UNION ALL SELECT '','合计:',CAST(SUM(岗位工资) AS VARCHAR),CAST(SUM(金额) AS VARCHAR), CAST(SUM(加班) AS VARCHAR),CAST(SUM(扣岗) AS VARCHAR),CAST(MAX(编号)+1 AS VARCHAR) 编号,3 FROM @T
) AS A ORDER BY o1,o2 /* 姓名 编号 岗位工资 金额 加班 扣岗 张三 01 100 100 50 30
姓名 编号 岗位工资 金额 加班 扣岗 李四 02 200 100 60 40
姓名 编号 岗位工资 金额 加班 扣岗 王五 03 100 200 50 20
合计: 400 400 160 90*/ [code]
declare @T table (姓名 varchar(4),编号 varchar(2),岗位工资 int,金额 int,加班 int,扣岗 int) insert into @T select '张三','01',100,100,50,30 union all select '李四','02',200,100,60,40 union all select '王五','03',100,200,50,20 SELECT 姓名,编号,岗位工资,金额,加班,扣岗 FROM ( SELECT '姓名' AS 姓名,'编号' AS 编号, '岗位工资' AS 岗位工资,'金额' AS 金额, '加班' AS 加班,'扣岗' AS 扣岗,o1=编号,o2=0 FROM @T UNION ALL SELECT 姓名,编号,RTRIM(岗位工资), RTRIM(金额),RTRIM(加班), RTRIM(扣岗),编号,1 FROM @T UNION ALL SELECT '','','','','','', 编号,2 FROM @T UNION ALL SELECT '','合计:',CAST(SUM(岗位工资) AS VARCHAR),CAST(SUM(金额) AS VARCHAR), CAST(SUM(加班) AS VARCHAR),CAST(SUM(扣岗) AS VARCHAR),CAST(MAX(编号)+1 AS VARCHAR) 编号,3 FROM @T
UNION ALL
SELECT '合计','',SUM(岗位工资),SUM(金额),SUM(加班),SUM(扣岗)FROM GZ
前面的...
---合计部分
union
select '',sum(isnull(岗位工资,0)),sum(isnull(金额,0)),sum(isnull(加班,0)),sum(isnull(扣岗,0))
---合计部分
union
select '合计','',sum(isnull(岗位工资,0)),sum(isnull(金额,0)),sum(isnull(加班,0)),sum(isnull(扣岗,0))
from gz
INSERT @T SELECT N'张三', '01' , 100, 100 , 50, 30
INSERT @T SELECT N'李四' , '02' , 200 , 100 , 60, 40
INSERT @T SELECT N'王五' , '03', 100 , 200, 50, 20
SELECT * FROM @T
UNION ALL
SELECT N'合计','',SUM(岗位工资),SUM(金额),SUM(加班),SUM(扣岗)FROM @T
/*
姓名 编号 岗位工资 金额 加班 扣岗
---------- ---------- ----------- ----------- ----------- -----------
张三 01 100 100 50 30
李四 02 200 100 60 40
王五 03 100 200 50 20
合计 400 400 160 90(影響 4 個資料列)
*/
DECLARE @SQL VARCAHR(100),@NUM VARCHAR(8)
DECLARE CUR CURSOR FOR SELECT 编号 FROM GZ FOR READ ONLY
DECLARE @COUNT INT
SET @COUNT=(SELECT COUNT(*) FROM GZ)
OPEN CUR
WHILE @COUNT<>0
BEGIN
FETCH NEXT FROM CUR INTO @NUM
SET @SQL=@SQL+'
SELECT * FROM GZ WHERE 编号='''+@NUM+'''
@COUNT=@COUNT-1
END
CLOSE CUR
DEALLOCATE CUR
EXEC (@SQL)
SELECT '合计' AS 合计,'' AS '',sum(isnull(岗位工资,0)) AS 岗位工资,
sum(isnull(金额,0)) AS 金额,
sum(isnull(加班,0))AS 加班,
sum(isnull(扣岗,0)) AS 扣岗
FROM GZ
FROM (
SELECT '姓名' AS 姓名,'编号' AS 编号,
'岗位工资' AS 岗位工资,'金额' AS 金额,
'加班' AS 加班,'扣岗' AS 扣岗,o1=姓名,o2=0
FROM @T
UNION ALL
SELECT 姓名,编号,RTRIM(岗位工资),
RTRIM(金额),RTRIM(加班),
RTRIM(扣岗),姓名,1
FROM @T
UNION ALL
SELECT '','','','','','',
姓名,2
FROM @T WHERE 编号 NOT IN(SELECT TOP 1 编号 FROM @T)
) AS A
ORDER BY o1,o2这样的代码如何加入合计那一条呢
DECLARE @SQL VARCAHR(100),@NUM VARCHAR(8)
DECLARE CUR CURSOR FOR SELECT 编号 FROM GZ FOR READ ONLY
DECLARE @COUNT INT
SET @COUNT=(SELECT COUNT(*) FROM GZ)
OPEN CUR
WHILE @COUNT<>0
BEGIN
FETCH NEXT FROM CUR INTO @NUM
SET @SQL=@SQL+'
SELECT * FROM GZ WHERE 编号='''+@NUM+''''
@COUNT=@COUNT-1
END
CLOSE CUR
DEALLOCATE CUR
EXEC (@SQL)
SELECT '合计' AS 合计,'' AS '',sum(isnull(岗位工资,0)) AS 岗位工资,
sum(isnull(金额,0)) AS 金额,
sum(isnull(加班,0))AS 加班,
sum(isnull(扣岗,0)) AS 扣岗
FROM GZ
insert into @T
select '张三','01',100,100,50,30 union all
select '李四','02',200,100,60,40 union all
select '王五','03',100,200,50,20
SELECT 姓名,编号,岗位工资,金额,加班,扣岗
FROM (
SELECT '姓名' AS 姓名,'编号' AS 编号,
'岗位工资' AS 岗位工资,'金额' AS 金额,
'加班' AS 加班,'扣岗' AS 扣岗,o1=编号,o2=0
FROM @T
UNION ALL
SELECT 姓名,编号,RTRIM(岗位工资),
RTRIM(金额),RTRIM(加班),
RTRIM(扣岗),编号,1
FROM @T
UNION ALL
SELECT '','','','','','',
编号,2 FROM @T
UNION ALL
SELECT '','合计:',CAST(SUM(岗位工资) AS VARCHAR),CAST(SUM(金额) AS VARCHAR),
CAST(SUM(加班) AS VARCHAR),CAST(SUM(扣岗) AS VARCHAR),CAST(MAX(编号)+1 AS VARCHAR) 编号,3 FROM @T
) AS A
ORDER BY o1,o2
/*
姓名 编号 岗位工资 金额 加班 扣岗
张三 01 100 100 50 30
姓名 编号 岗位工资 金额 加班 扣岗
李四 02 200 100 60 40
姓名 编号 岗位工资 金额 加班 扣岗
王五 03 100 200 50 20
合计: 400 400 160 90*/
[code]
insert into @T
select '张三','01',100,100,50,30 union all
select '李四','02',200,100,60,40 union all
select '王五','03',100,200,50,20
SELECT 姓名,编号,岗位工资,金额,加班,扣岗
FROM (
SELECT '姓名' AS 姓名,'编号' AS 编号,
'岗位工资' AS 岗位工资,'金额' AS 金额,
'加班' AS 加班,'扣岗' AS 扣岗,o1=编号,o2=0
FROM @T
UNION ALL
SELECT 姓名,编号,RTRIM(岗位工资),
RTRIM(金额),RTRIM(加班),
RTRIM(扣岗),编号,1
FROM @T
UNION ALL
SELECT '','','','','','',
编号,2 FROM @T
UNION ALL
SELECT '','合计:',CAST(SUM(岗位工资) AS VARCHAR),CAST(SUM(金额) AS VARCHAR),
CAST(SUM(加班) AS VARCHAR),CAST(SUM(扣岗) AS VARCHAR),CAST(MAX(编号)+1 AS VARCHAR) 编号,3 FROM @T
) AS A
ORDER BY o1,o2
/*
姓名 编号 岗位工资 金额 加班 扣岗
张三 01 100 100 50 30 姓名 编号 岗位工资 金额 加班 扣岗
李四 02 200 100 60 40 姓名 编号 岗位工资 金额 加班 扣岗
王五 03 100 200 50 20 合计: 400 400 160 90 */