结果 a : SELECT 目的地, 收货人姓名 AS 客户, SUM(货款) AS 总金额
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名 结果 b : SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名) b ON a.目的地 = b.目的地, a.客户 = b.客户要求: select a.*,b.总运费 from (a left outer join b) union (select 客户 from b where 客户 not in a )谁帮帮忙啊........
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名 结果 b : SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名) b ON a.目的地 = b.目的地, a.客户 = b.客户要求: select a.*,b.总运费 from (a left outer join b) union (select 客户 from b where 客户 not in a )谁帮帮忙啊........
select 目的地,
收货人姓名 as 客户,
sum(isnull(case when 货款 > 0 and 实收货款总额 <= 0 then 货款 else 0 end,0)) as 总金额,
sum(isnull(case when 运费结清 = 0 and 总运费 > 0 then 总运费 else 0 end ,0)) as 总运费
from 运单表
group by
目的地, 收货人姓名
order by
目的地, 收货人姓名
我不理解你的这句union (select 客户 from b where 客户 not in a )
什么意思呢?是不是要另外一个结果集
要表示在一个结果集里吗?
from (SELECT 目的地, 收货人姓名 AS 客户, SUM(货款) AS 总金额
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名) as a
left join
(SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名) as b
on a.目的地 = b.目的地, a.客户 = b.客户
union all
select 目的地, 客户,0,总运费
from (SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名) as b
where 客户 not in
(select distinct 客户 from (SELECT 目的地, 收货人姓名 AS 客户, SUM(货款) AS 总金额
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名) as a)这样?
收货人姓名 as 客户,
sum(isnull(case when 货款 > 0 and 实收货款总额 <= 0 then 货款 else 0 end,0)) as 总金额,
sum(isnull(case when 运费结清 = 0 and 总运费 > 0 then 总运费 else 0 end ,0)) as 总运费
from 运单表
group by
目的地, 收货人姓名
having sum(isnull(case when 货款 > 0 and 实收货款总额 <= 0 then 货款 else 0 end,0))<>0
or sum(isnull(case when 运费结清 = 0 and 总运费 > 0 then 总运费 else 0 end ,0))<>0
order by
目的地, 收货人姓名
select a.*,b.总运费
from (SELECT 目的地, 收货人姓名 AS 客户, SUM(货款) AS 总金额
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名
having SUM(货款)<>0) as a
left join
(SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名
having SUM(总运费)<>0) as b
on a.目的地 = b.目的地, a.客户 = b.客户
union all
select 目的地, 客户,0,总运费
from (SELECT 目的地, 收货人姓名 AS 客户, SUM(总运费) AS 总运费
FROM 运单表
WHERE (运费结清 = 0) AND (总运费 > 0)
GROUP BY 目的地, 收货人姓名
having SUM(总运费)<>0) as b
where 客户 not in
(select distinct 客户 from (SELECT 目的地, 收货人姓名 AS 客户, SUM(货款) AS 总金额
FROM 运单表
WHERE 货款 > 0 AND 实收货款总额 <= 0
GROUP BY 目的地, 收货人姓名
having SUM(货款)<>0) as a)