问题1: select jydm,max(jymc),sum(fxsl) fxsl from fx200404 group by fxr,jydm
2 SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb FROM FX200404 A,jydm B WHERE A.fxr='甲' AND A.JYDM=B.JYDM GROUP BY jydm,jymc,B.jylb
2 SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb FROM FX200404 A,jydm B WHERE A.fxr='甲' AND A.JYDM=B.JYDM GROUP BY A.jydm,A.jymc,B.jylb
2 SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb FROM FX200404 A,jydm B WHERE A.fxr='甲' AND A.JYDM=B.JYDM GROUP BY A.jydm,A.jymc,B.jylb
没看清楚 2 --先建立函数 create function Getjylbs(@fxr varchar(20)) returns varchar(50) as begin declare @r varchar(50) set @r='其中' SELECT @r=@r+cast(jylb as varchar(10))+'='+cast(fxsl as varchar(20)+' ' from ( SELECT SUM(A.fxsl) AS fxsl,B.jylb FROM FX200404 A,jydm B WHERE A.fxr=@fxr AND A.JYDM=B.JYDM GROUP BY B.jylb ) as x return @r end--再这样查询 SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,cast(B.jylb as varchar(50)) as jylb FROM FX200404 A,jydm B WHERE A.fxr='甲' AND A.JYDM=B.JYDM GROUP BY A.jydm,A.jymc,B.jylb union all SELECT '合计' as jydm,'合计' as jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,dbo.getjylbs('甲') as jylb FROM FX200404 A,jydm B WHERE A.fxr='甲' AND A.JYDM=B.JYDM
select jydm,max(jymc),sum(fxsl) fxsl from fx200404 group by fxr,jydm
SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb
FROM FX200404 A,jydm B
WHERE A.fxr='甲' AND A.JYDM=B.JYDM
GROUP BY jydm,jymc,B.jylb
SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb
FROM FX200404 A,jydm B
WHERE A.fxr='甲' AND A.JYDM=B.JYDM
GROUP BY A.jydm,A.jymc,B.jylb
SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,B.jylb
FROM FX200404 A,jydm B
WHERE A.fxr='甲' AND A.JYDM=B.JYDM
GROUP BY A.jydm,A.jymc,B.jylb
2
--先建立函数
create function Getjylbs(@fxr varchar(20))
returns varchar(50)
as
begin
declare @r varchar(50)
set @r='其中'
SELECT @r=@r+cast(jylb as varchar(10))+'='+cast(fxsl as varchar(20)+' '
from (
SELECT SUM(A.fxsl) AS fxsl,B.jylb
FROM FX200404 A,jydm B
WHERE A.fxr=@fxr AND A.JYDM=B.JYDM
GROUP BY B.jylb
) as x
return @r
end--再这样查询
SELECT A.jydm,A.jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,cast(B.jylb as varchar(50)) as jylb
FROM FX200404 A,jydm B
WHERE A.fxr='甲' AND A.JYDM=B.JYDM
GROUP BY A.jydm,A.jymc,B.jylb
union all
SELECT '合计' as jydm,'合计' as jymc,SUM(A.fxsl) AS fxsl,SUM(A.fxsl*(B.jg6-B.jg4)/1.17) AS MAOLI,dbo.getjylbs('甲') as jylb
FROM FX200404 A,jydm B
WHERE A.fxr='甲' AND A.JYDM=B.JYDM