从两个表中选择
从两个表中选择
提问于 2017-06-06 22:08:21
我有两张桌子Table A和Table B。
我想选择两个月之间的每个Code和year=2017的总和列TableA作为A和TableB作为B，但我想选择所有行从TableB作为C where Year=2017没有过滤器的月份。
下面是我尝试过的：
SELECT A.Annee as Year,
code_ville as Code,
SUM(UniteVendu) AS Unit,
SUM(CAVendu) AS CA,
SUM(B.UniteVentePrev) AS Unit_P,
SUM(B.CAVentePrev) AS Ca_P,
SUM(C.UniteVentePrev) AS Unit_PPP,
SUM(C.CAVentePrev) AS Ca_PPP
FROM TableA A
left join TableB B on A.code=B.codeP
and A.Annee=PP.Annee
and A.mois =B.mois
left join TableB C on A.code=C.codeP
and A.Annee=C.Annee
where A.Annee = 2017
and A.mois >= 1
and A.mois <= 3
GROUP BY R.Annee,
code_ville但是我得到了不正确的结果。
回答 1
Stack Overflow用户
发布于 2017-06-06 22:59:35
我想我理解你的需求，如果我理解的话，它们实际上是相当简单的。最初，您需要获取Year和Code组合的列表，以便可以从它们构建最终结果数据集，将其join到TableA和TableB中，并执行一些条件sum计算。
请注意，我已经添加了一些附加数据来显示我的方法的全部功能：
-- Built test data
declare @a table([Year] int,[Month] int,Code int, Unit int, CA decimal(20,2));
declare @b table([Year] int,[Month] int,CodeP int, Unit_P int, CA_P decimal(20,0));
insert into @a values (2017,1,280,1,298259.42),(2017,1,281,0,0),(2017,2,280,1,298220.99),(2017,2,281,0,0),(2017,3,282,0,0),(2017,3,280,1,298033.09),(2017,4,280,1,298000.00);
insert into @b values (2017,1,280,1,250000),(2017,1,280,3,950000),(2017,3,281,1,250000),(2017,3,282,1,250000),(2017,6,282,1,250000);
-- Derived table to get all combinations of Year and Code across both tables
with y as
(
select [Year]
,Code
from @a
union -- Use of UNION ensures that a unique list is returned.
select [Year]
,CodeP
from @b
)
select y.[Year]
,y.Code
,sum(case when a.[Month] between 1 and 3 then a.Unit else 0 end) as Unit
,sum(case when a.[Month] between 1 and 3 then a.CA else 0 end) as CA
,sum(case when b.[Month] between 1 and 3 then b.Unit_P else 0 end) as Unit_P
,sum(case when b.[Month] between 1 and 3 then b.CA_P else 0 end) as CA_P
,isnull(sum(a.Unit),0) as Unit_PPP
,isnull(sum(a.CA),0) as CA_PPP
from y -- Then join this list onto both tables to get the totals
left join @a a
on(y.[Year] = a.[Year]
and y.Code = a.Code
)
left join @b b
on(y.[Year] = b.[Year]
and y.Code = b.CodeP
)
group by y.[Year]
,y.Code
order by y.[Year]
,y.Code;输出：
+------+------+------+------------+--------+---------+----------+------------+
| Year | Code | Unit | CA | Unit_P | CA_P | Unit_PPP | CA_PPP |
+------+------+------+------------+--------+---------+----------+------------+
| 2017 | 280 | 6 | 1789027.00 | 16 | 4800000 | 8 | 2385027.00 |
| 2017 | 281 | 0 | 0.00 | 2 | 500000 | 0 | 0.00 |
| 2017 | 282 | 0 | 0.00 | 1 | 250000 | 0 | 0.00 |
+------+------+------+------------+--------+---------+----------+------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44392297
复制相关文章
相似问题
腾讯云开发者
