求一条SQL的统计代码解决方法

求一条SQL的统计代码
数据如下所示，要求统计总表数据（SJZB）的数目和总数SUM(HTZJ,SSZJ),要求是
SJZB表中的编号与SSMC表的编号以及JSSJ表的编号一样，并且前提条件是SSMC中编号出现的所有数据，也就是说有多少条记录，在JSSJ中也相应的有多少条记录，再加上SJZB中为‘出具审计报告',才算完成一条记录，才能统计SJZB中为一条，才能统计相应的数目，也就如以下的例子所说

在SSMC表中，编号为NS-2007-001有三条记录，而JSSJ中也有三条记录，再加上SJZB中的"SJZB"为‘出具审计报告‘，这样才算完成一条记录，才能统计为一条记录和总数

而编号为NS-2007-002在SSMC中有二条记录，而JSSJ中有一条记录，即使SJZB中的“SJZB”为‘出具审计报告’，也不算一条记录，不能统计

而编号为NS-2007-003在SSMC中有一条记录，而JSSJ中也有一条记录，但是SJZB中的"SJZB"为‘审计结果确认‘，也不算一条记录，不能统计

1、总表数据（ SJZB）
SJBH SJXS HTZJ SSZJ SJZT
NS-2007-001 结算 490948.23 665614.42 出具审计报告
NS-2007-002 决算 327298.82 443742.88 出具审计报告
NS-2007-003 预算 163649.41 221871.54 审计结果确认
NS-2007-004 结算 163649.41 221871.54 出具审计报告
NS-2007-005 结算 163649.41 221871.54 出具审计报告

2、送审数据（SSMC)
ID SJBH HTZJ SSZJ
1 NS-2007-001 163649.41 221871.54
2 NS-2007-001 163649.41 221871.54
3 NS-2007-001 163649.41 221871.54
4 NS-2007-002 163649.41 221871.54
5 NS-2007-002 163649.41 221871.54
6 NS-2007-003 163649.41 221871.54
7 NS-2007-004 163649.41 221871.54
8 NS-2007-005 163649.41 221871.54

3、结算数据（JSSJ）
ID SJBH HTZJ SSZJ
1 NS-2007-001 163649.41 221871.54
2 NS-2007-001 163649.41 221871.54
3 NS-2007-001 163649.41 221871.54
4 NS-2007-002 163649.41 221871.54
5 NS-2007-003 163649.41 221871.54
6 NS-2007-004 163649.41 221871.54

以下统计的结果只有编号NS-2007-001与NS-2007-004满足条件，则统计结果为：（总表数据)SJZB
count(*) sum(HTZJ) sum(SSZJ)
2 490948.23 +163649.41 =654597.64 665614.42 +221871.5400=887485.96

------解决方案--------------------

SQL code

create table tb(col1 varchar(1),col2 decimal(10,0))
insert into tb select 'A',1000
insert into tb select 'A',200
insert into tb select 'B',800
drop FUNCTION dbo.f_str
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re varchar(100)
    SET @re=''
    SELECT @re=@re+'+'+CAST(col2 as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,'')+'=')
END

select col1,dbo.f_str(col1)+ltrim(sum(col2)) as hb,count(*) as [count]
from tb group by col1

------解决方案--------------------
select count(sjzb.SJZB),sum(t1.htzj),sum(t2.SSZJ)
from sjzb,(select count(*) as count1,sum(htzj) as htzj from SSMC group SJBH),(select count(*) as count2,sum(SSZJ) as SSZJ from JSSJ group SJBH)
where sjzb.SJZT='出具审计报告' and  sjzb.sjbh=t1.sjbh and t1.sjbh.t2.sjbh and t1.count1=t2.count2

------解决方案--------------------
只能做到这步了

SQL code


select count(*), sum(htzj), sum(sszj)
from
(select sjbh, htzj, sszj
from sjzb
where sjbh in
(select a.sjbh
from
(select SJBH, count(sjbh) as sjbhcount
from SSMC
group by SJBH
union
select SJBH, -1 * count(sjbh) as sjbhcount
from JSSJ
group by SJBH) as a inner join sjzb s
on a.sjbh = s.sjbh
where s.SJZT = '出具审计报告'
group by a.sjbh
having sum(sjbhcount) = 0 )) b

------解决方案--------------------

SQL code


--1、总表数据（SJZB） 
declare @SJZB table (SJBH varchar(12),SJXS varchar(4),HTZJ decimal(18,2),SSZJ decimal(18,2),SJZT varchar(15))
insert into @SJZB values('NS-2007-001','結算',490948.23,665614.42,'出具審計報告')
insert into @SJZB values('NS-2007-002','決算',327298.82,443742.88,'出具審計報告')
insert into @SJZB values('NS-2007-003','預算',163649.41,221871.54,'審計結果確認')
insert into @SJZB values('NS-2007-004','結算',163649.41,221871.54,'出具審計報告')
insert into @SJZB values('NS-2007-005','結算',163649.41,221871.54,'出具審計報告')

select * from @SJZB

--2、送审数据（SSMC) 
declare @SSMC table (id int,SJBH varchar(12),HTZJ decimal(18,2),SSZJ decimal(18,2))
insert into @SSMC values(1,'NS-2007-001',163649.41,221871.54) 
insert into @SSMC values(2,'NS-2007-001',163649.41,221871.54 )
insert into @SSMC values(3,'NS-2007-001',163649.41,221871.54 )
insert into @SSMC values(4,'NS-2007-002',163649.41,221871.54 )
insert into @SSMC values(5,'NS-2007-002',163649.41,221871.54 )
insert into @SSMC values(6,'NS-2007-003',163649.41,221871.54 )
insert into @SSMC values(7,'NS-2007-004',163649.41,221871.54 )
insert into @SSMC values(8,'NS-2007-005',163649.41,221871.54 )

----select * from @SSMC

--3、结算数据（JSSJ） 
declare @JSSJ table (id int,SJBH varchar(12),HTZJ decimal(18,2),SSZJ decimal(18,2))
insert into @JSSJ values(1,'NS-2007-001',163649.41,221871.54) 
insert into @JSSJ values(2,'NS-2007-001',163649.41,221871.54 )
insert into @JSSJ values(3,'NS-2007-001',163649.41,221871.54 )
insert into @JSSJ values(4,'NS-2007-002',163649.41,221871.54 )
insert into @JSSJ values(5,'NS-2007-003',163649.41,221871.54 )
insert into @JSSJ values(6,'NS-2007-004',163649.41,221871.54 )

----select * from @JSSJ
select count(*) as "count(*)",sum(HTZJ) as "sum(HTZJ)",sum(SSZJ) as "sum(SSZJ)" 
from @SJZB
where SJBH in ('NS-2007-001','NS-2007-004')


select count(*) as "count(*)",sum(HTZJ) as "sum(HTZJ)",sum(SSZJ) as "sum(SSZJ)" 
from @SJZB
where 
(
  SJBH='NS-2007-001' 
  and (select count (*) from @SSMC where SJBH = 'NS-2007-001') = (select count (*) from @JSSJ where SJBH = 'NS-2007-001')
  and (select count (*) from @SJZB where SJBH = 'NS-2007-001') = 1
)
or 
(
  SJBH

求一条SQL的统计代码解决方法

相关推荐