求助：两个表关联查询数据
如题，我有两个表A,B
A是主表,B是从表B表有个字段表示 这票是否审核,
比如说,我A表有一条数据,在B表有10条与A表对应的数据，如果B表这10条数据中有一条未审核的，那么就算这票未审核，求助高手这样的数据如何查呢？
需要计算金额等等
ALTER proc [dbo].[BusinessQuery_SelectForZY]
@InputMan varchar(50),
@InputDepartment varchar(20),
@EndInputDate datetime ,
@StartInputDate datetime,
@Consignor varchar(100),
@DeliveryListNum varchar(50),
@ApproveNum varchar(20),
@PermissionsCode VARCHAR(10)
as
begin
declare @paySql varchar(5000)
set @paySql ='select
c.BusinessNum,
c.InputMan [录入人],
c.InputDate [录入时间],
c.DeliveryListNum [提单号],
c.ApproveNum [核销单号],
c.CustomsNum [海关编号],
CONVERT(VARCHAR(10),c.DeclareDate,120) [申报日期],
b.Consignor [付费单位],
c.Operate_Name [货主],
c.ConveyanceName [船名],
c.VoyageNum [航次],
(sum(case f.CostType when ''0'' then f.ShouldMoney else 0 end )-sum(case f.CostType when ''1'' then f.ShouldMoney else 0 end)) [收入]
from bus_BusinessDetail b,customs_CustomsDeclaration c,cost_FeeInfo f
where b.BusinessNum=c.BusinessNum
and b.BusinessNum=f.BusinessNum
and c.BusinessNum=f.BusinessNum
and f.Flag_ManagerCheckCost = ''1'' --查询已经审核的
'
if @InputMan is not null
begin
set @PaySql = @PaySql+ ' and c.InputMan = '''+@InputMan+''''
end
if @InputDepartment is not null
begin
set @PaySql = @PaySql+ ' and c.InputDepartment = '''+@InputDepartment+''''
end
IF @StartInputDate IS NOT NULL AND @EndInputDate IS NOT NULL
BEGIN
SET @PaySql = @PaySql + ' and c.InputDate BETWEEN ''' + CONVERT(VARCHAR(50),
@StartInputDate,120) + ''' AND ''' + CONVERT(VARCHAR(50),@EndInputDate,120)
+ ''''
end
if @Consignor is not null
begin
set @PaySql = @PaySql+' and b.Consignor='''+@Consignor+''''
end
if @DeliveryListNum is not null
begin
set @PaySql=@PaySql+' and c.DeliveryListNum='''+@DeliveryListNum+''''
end
if @ApproveNum is not null
begin
set @PaySql=@PaySql+' and c.ApproveNum='''+@ApproveNum+''''
end
if @PermissionsCode is not null
begin
set @PaySql=@PaySql+' and b.PermissionsCode='''+@PermissionsCode+''''
end
set @PaySql=@PaySql+' group by c.BusinessNum,c.InputMan,c.InputDate,c.DeliveryListNum,c.ApproveNum,c.CustomsNum,
c.DeclareDate,b.Consignor,c.Operate_Name,c.ConveyanceName,c.VoyageNum'
print (@PaySql)
exec(@PaySql)
end
--这个代码是查询全部审核通过的
select BusinessNum
from (
select b.BusinessNum,count(*) as totalNum,SUM(case when f.Flag_ManagerCheckCost='1' then 1 else 0 end) as checkNum
from bus_BusinessDetail b
inner join cost_FeeInfo f on b.BusinessNum=f.BusinessNum
group by b.BusinessNum
)t where totalNum=checkNum and totalNum>0
--所以这段代码应该这样改：
declare @paySql varchar(5000)
set @paySql ='select
c.BusinessNum,
c.InputMan [录入人],
c.InputDate [录入时间],
c.DeliveryListNum [提单号],
c.ApproveNum [核销单号],
c.CustomsNum [海关编号],
CONVERT(VARCHAR(10),c.DeclareDate,120) [申报日期],
b.Consignor [付费单位],
c.Operate_Name [货主],
c.ConveyanceName [船名],
c.VoyageNum [航次],
(sum(case f.CostType when ''0'' then f.ShouldMoney else 0 end )-sum(case f.CostType when ''1'' then f.ShouldMoney else 0 end)) [收入]
from bus_BusinessDetail b,customs_CustomsDeclaration c,cost_FeeInfo f
where b.BusinessNum=c.BusinessNum
and b.BusinessNum=f.BusinessNum
and b.BusinessNum in
(
select BusinessNum
from (
select b.BusinessNum,count(*) as totalNum,SUM(case when f.Flag_ManagerCheckCost=''1'' then 1 else 0 end) as checkNum
from bus_BusinessDetail b
inner join cost_FeeInfo f on b.BusinessNum=f.BusinessNum
group by b.BusinessNum
)t where totalNum=checkNum and totalNum>0
)
'
