SQL查询 - 确定谁只有一个礼物记录
问题描述:
我想知道我怎么能有一群人最初选择中编写一个SQL脚本,这样一:SQL查询 - 确定谁只有一个礼物记录
SELECT [RECORDS].[CONSTITUENT_ID]
,[RECORDS].[FIRST_NAME]
,[RECORDS].[LAST_NAME]
,[DATEADDED]
,[DTE]
,[Amount]
,[REF]
,[TYPE]
FROM [re7].[dbo].[GIFT]
INNER JOIN [re7].[dbo].[RECORDS]
ON GIFT.CONSTIT_ID LIKE RECORDS.ID
WHERE ([DTE] BETWEEN '2/7/2015' AND '2/8/2015')
ORDER BY [DATEADDED] DESC
只选择个人谁是“第一时间捐助者”(或谁只有一个礼物[RE7] [DBO]。[礼物]。
[RECORDS]是所有成分表。
[礼物]是所有的表录音礼品
上述查询的输出,仅仅是一个表: CONSTITUENT_ID,名字,姓氏,DATEADDED,DTE,金额,楼盘,TYPE
我非常希望看到相同的输出格式,但我想在[re7]中查询仅选择只有1个GIFT(按他们的记录ID)的CONSTITUENT_ID [dbo]。[GIFT]。
对于缺乏显示的数据,我表示歉意。我希望我可以更好地描述....
答
SELECT [RECORDS].[CONSTITUENT_ID]
,[RECORDS].[FIRST_NAME]
,[RECORDS].[LAST_NAME]
,[DATEADDED]
,[DTE]
,[Amount]
,[REF]
,[TYPE]
FROM [re7].[dbo].[GIFT]
INNER JOIN [re7].[dbo].[RECORDS]
ON GIFT.CONSTIT_ID LIKE RECORDS.ID
WHERE ([DTE] BETWEEN '2/7/2015' AND '2/8/2015')
AND GIFT.CONSTIT_ID IN (
SELECT CONSTIT_ID FROM re7.dbo.Gift GROUP BY CONSTIT_ID HAVING COUNT(*) = 1
) /* another option is to add a subquery to the query you already had */
ORDER BY [DATEADDED] DESC
+0
这与其他答案基本相同。但是,将其并入现有查询稍微容易一些。 – shawnt00 2015-02-12 01:39:37
答
该解决方案只是简单地选择所有只做过一次捐赠并随后加入的成分,从而限制了结果集。
SELECT
r.[CONSTITUENT_ID]
,r.[FIRST_NAME]
,r.[LAST_NAME]
,[DATEADDED]
,[DTE]
,[Amount]
,[REF]
,[TYPE]
FROM
(select [CONSTIT_ID] from [re7].[dbo].[GIFT] group by [CONSTIT_ID] having count([CONSTIT_ID]) = 1) g1
inner join [re7].[dbo].[GIFT] g
on g.[CONSTIT_ID] = g1.[CONSTIT_ID]
INNER JOIN [re7].[dbo].[RECORDS] r
ON g.CONSTIT_ID LIKE r.RECORDS.ID
WHERE ([DTE] BETWEEN '2/7/2015' AND '2/8/2015')
ORDER BY [DATEADDED] DESC
你能提供一些示例数据和预期的结果。 – 2015-02-12 00:40:44
你为什么要用这种方式使用'ON GIFT.CONSTIT_ID LIKE RECORDS.ID'? – 2015-02-12 01:28:28