SQL Server：从连接表中的项获取行和总和

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column amount in table giftregistrypurchases for the respective item in table giftregistryitems.

我想返回表giftregistryitems中的所有行,并附加一列,其中包含表giftregistryitems中表项giftregistrypurchases中列值的总和。

What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the amount for THAT item, based on giftregistrypurchases.itemid = giftregistryitems.id:

我尝试了什么,但是对于purchaseamount返回NULL,我希望purchaseamount是该项目的金额总和,基于giftregistrypurchases.itemid = giftregistryitems.id:

SELECT 
   (SELECT 
        SUM(amount) 
    FROM
        giftregistrypurchases gps 
    WHERE  
        registryid = gi.registryid 
        AND gp.itemid = gps.itemid) as purchasedamount, * 
FROM 
    giftregistryitems gi
LEFT JOIN 
    giftregistrypurchases gp ON gp.registryid = gi.id
WHERE 
    gi.registryid = 2

How can I achieve what I need?

我怎样才能达到我的需要?

These are my table definitions and data.

这些是我的表定义和数据。

A little explanation:

一点解释:

[giftregistry] is the general description of the list, the bridal couple in this is the only owner. They can add a text they want to send to the guests.

[giftregistry]是列表的一般描述,这对新婚夫妇是唯一的所有者。他们可以添加他们想要发送给客人的文本。

[giftregistryitems] contains the items the bridal couple is requesting. Here column amount is the total amount they want of that item, e.g. 6 coffee cups.

[giftregistryitems]包含新婚夫妇要求的项目。此处列数是他们想要该项目的总金额,例如6个咖啡杯。

[giftregistryemails] contains the guests.

[giftregistryemails]包含客人。

[giftregistrypurchases] contains which guest has purchased which amount of which item, e.g. guest nr. 3 has purchased 5 coffee cups (of the total 6 requested).

[giftregistrypurchases]包含哪个客人购买了哪个项目,例如客人nr。 3购买了5个咖啡杯(总共要求6个咖啡杯)。

USE [tt]
GO
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,
[listuuid] [nvarchar](50) NOT NULL CONSTRAINT [DF_giftregistry_listuuid] DEFAULT (newid()),
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](50) NOT NULL,
[description] [nvarchar](500) NULL,
[invitetext] [nvarchar](1000) NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistry_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistry] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[giftregistryemails] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryemails](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[email] [nvarchar](150) NOT NULL,
[pwd] [nvarchar](10) NULL,
[sentdate] [datetime] NULL CONSTRAINT [DF_giftregistryemails_sentdate] DEFAULT (((1)/(1))/(1900)),
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistryemails_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistryemails] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[giftregistryitems] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[title] [nvarchar](500) NOT NULL,
[ogimg] [nvarchar](250) NULL,
[description] [nvarchar](500) NULL,
[URL] [nvarchar](500) NULL,
[trackingURL] [nvarchar](500) NULL,
[amount] [tinyint] NOT NULL,
[price] [int] NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistryitems_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistryitems] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[giftregistrypurchases] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistrypurchases](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[itemid] [int] NOT NULL,
[emailid] [int] NOT NULL,
[amount] [tinyint] NOT NULL,
[createdate] [datetime] NOT NULL,
CONSTRAINT [PK_giftregistrypurchases] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[giftregistry] ON 

GO
INSERT [dbo].[giftregistry] ([id], [listuuid], [userid], [title], [description], [invitetext], [createdate]) VALUES (2, N'83875b62-3cc1-4516-b932-6e60a116cbff', N'32DD30EB-1691-457B-9FF5-FC41D687E579', N'My list', N'We''re getting married', N'Hey you guys coming too?', CAST(N'2015-04-29 21:21:53.813' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistry] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistryemails] ON 

GO
INSERT [dbo].[giftregistryemails] ([id], [registryid], [name], [email], [pwd], [sentdate], [createdate]) VALUES (2, 2, N'Peter', N'peter@hotmail.com', N'1234', CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'2015-05-02 22:01:53.783' AS DateTime))
GO
INSERT [dbo].[giftregistryemails] ([id], [registryid], [name], [email], [pwd], [sentdate], [createdate]) VALUES (3, 2, N'Eric', N'eric@outlook.com', N'1234', CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'2015-05-02 22:01:53.783' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistryemails] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistryitems] ON 

GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], [URL], [trackingURL], [amount], [price], [createdate]) VALUES (9, 2, N'1111', N'http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg', N'Surface Pro 3', N'http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697', NULL, 1, 114998, CAST(N'2015-05-05 20:06:57.143' AS DateTime))
GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], [URL], [trackingURL], [amount], [price], [createdate]) VALUES (10, 2, N'Coffee cups', N'http://www.trouwen-feestartikelen.nl/images/rozenblaadjes-burgundy.jpg', N'Great cups', N'https://www.coffee.com/cups', NULL, 6, 25, CAST(N'2015-05-05 20:57:05.900' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistryitems] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistrypurchases] ON 
GO
INSERT [dbo].[giftregistrypurchases] ([id], [registryid], [itemid], [emailid], [amount], [createdate]) VALUES (3, 2, 9, 2, 5, CAST(N'2015-05-09 11:18:12.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistrypurchases] OFF
GO

ALTER TABLE [dbo].[giftregistrypurchases] ADD CONSTRAINT [DF_giftregistrypurchases_createdate] DEFAULT (getdate()) FOR [createdate]
GO
ALTER TABLE [dbo].[giftregistryemails] WITH CHECK ADD CONSTRAINT [FK_giftregistryemails_giftregistry] FOREIGN KEY([registryid])
REFERENCES [dbo].[giftregistry] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistryemails] CHECK CONSTRAINT [FK_giftregistryemails_giftregistry]
GO
ALTER TABLE [dbo].[giftregistryitems] WITH CHECK ADD CONSTRAINT [FK_giftregistryitems_giftregistry] FOREIGN KEY([registryid])
REFERENCES [dbo].[giftregistry] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistryitems] CHECK CONSTRAINT [FK_giftregistryitems_giftregistry]
GO
ALTER TABLE [dbo].[giftregistrypurchases] WITH CHECK ADD CONSTRAINT [FK_giftregistrypurchases_giftregistryitems] FOREIGN KEY([itemid])
REFERENCES [dbo].[giftregistryitems] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistrypurchases] CHECK CONSTRAINT [FK_giftregistrypurchases_giftregistryitems]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the unique code for this list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistry', @level2type=N'COLUMN',@level2name=N'listuuid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the generated password for this list and this user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistryemails', @level2type=N'COLUMN',@level2name=N'pwd'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The user pasted URL is converted to a tracking URL where possible' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistryitems', @level2type=N'COLUMN',@level2name=N'trackingURL'
GO

Desired results:

purchasedamount id  registryid  title   ogimg   description URL amount  price   createdate
5   4   2   Coffee cups http://www.trouwen-feestartikelen.nl/images/rozenblaadjes-burgundy.jpg  Great cups  https://www.coffee.com/cups 6   25  57:05.9
0   5   2   Surface Pro 3   http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg  Surface Pro 3   http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697 1   114998  06:57.1

2 个解决方案

#1

2 good ways to do that:

2个好方法:

1) group by (a lot of things)

1)分组(很多东西)

SELECT    
    SUM(gi.amount) as amount,
    gp.* 
FROM 
    giftregistryitems gi
LEFT JOIN 
    giftregistrypurchases gp ON gp.registryid = gi.id
WHERE 
    gi.registryid = 2
group by
    gp.Field1,
    gp.Field2 --All the gp fields :)

2) subquery (and i'd do this if i were you)

2)子查询(如果我是你,我会这样做)

select
    *,
    (select sum(gi.amount) from giftregistryitems gi where gp.registryid = gi.id)
from
    giftregistrypurchases gp

cheers!

#2

SELECT gi.ID, SUM(gps.amount) as purchasedamount
FROM 
    giftregistryitems gi
LEFT JOIN 
    giftregistrypurchases gp ON gp.registryid = gi.id
WHERE 
    gi.registryid = 2
group by gi.id

That is a bad naming convention if registryid means something different in the two tables.

如果registryid意味着两个表中的内容不同,那么这是一个错误的命名约定。

#1