我遇到了一个连接问题,我试图连接同一个表两次。
CREATE TABLE [dbo].[Accounts](
[Account] [nvarchar](10) NULL,
[Value] [real] NULL,
[Period] [nvarchar](8) NULL)
INSERT INTO [dbo].[Accounts] VALUES ('AC1', 100, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC2', -100, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC3', 200, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC4', -200, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC1', 100, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC1', -100, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC2', 200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC2', -200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC5', 100, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC5', -100, '201407')
INSERT INTO [dbo].[Accounts] VALUES ('AC6', 200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC6', -200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC3', 200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC3', -200, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC7', 300, '201408')
INSERT INTO [dbo].[Accounts] VALUES ('AC8', -300, '201408')
我尝试存档的第一个结果是将所有帐户都放在第一列中,并将每个周期的值放在单独的列中。我尝试了很多不同的加入方式,但都不起作用。我的最后一份是下面的,但它复制了我第一期的一些记录,没有显示一些账户。
SELECT A1.account, A1.value, A2.value FROM Accounts A1
FULL OUTER JOIN accounts A2 on A1.account = A2.account
WHERE A1.Period = '201407' and A2.period = '201408'
我试图归档的最后一个查询应该会产生这样的结果:
ACCOUNT P1 P2
AC1 100 0
AC2 -100 0
AC3 200 0
AC4 -200 NULL
AC5 0 NULL
AC6 0 0
AC7 NULL 300
AC8 NULL -300
我做错了什么?我尝试了所有可能的联接类型,并选择了neasted:(.
非常感谢您的帮助。
Tom,不需要联接就可以实现您想要的结果。尝试以下代码:
select account
,P1=sum(case when period = '201407' then value else null end)
,P2=sum(case when period = '201408' then value else null end)
from Accounts
where period in ('201407', '201408')
group by account
感谢您的快速回答。它正在工作:)
与此同时,我还找到了另一个同样有效的解决方案。分析你的我认为它比我的更有效率。
谢谢!
select distinct(x.account) konto, a.v1, b.v2 from accounts X
left join(select account a1, sum(value) v1 from accounts where Period = '201407' group by account) a on a.a1 = x.account
left join(select account a2, sum(value) v2 from accounts where Period = '201408' group by account) b on b.a2 = x.account