我有一个查询,我正在运行该查询以消除两个学期的重复项。不幸的是,我必须结合chpid,secno,lname,fname和电子邮件,以使记录唯一进行比较。
该查询为我尝试完成的工作提供了一个起点。
SELECT DISTINCT
'1' as Counter
chpid,
SECNO,
LNAME,
FNAME,
EmailAddress
FROM
Eligmember
WHERE
((FORMYEAR = 2018 AND FORMSEMESTER = 4) OR
(FORMYEAR = 2019 AND FORMSEMESTER = 2))
AND presentmem <> 'Y'
返回 58000 条记录
Counter chpid SECNO LNAME FNAME EmailAddress
---------------------------------------------------------------
1 1 2 Banes Moore MMB@test.test
1 1 2 Baron Lana LRB@test.org
这很好开始,但我想做的是计算每个 chpid 中有多少。我只需要每个chpid中每个人的计数,但我必须选择chpid,secno,lname,fname和电子邮件以使它们唯一。
有什么建议吗?
不幸的是,SQL Server 不支持具有多个表达式的count(distinct)
。 一种方法是子查询:
SELECT chpid, COUNT(*)
FROM (SELECT DISTINCT chpid, SECNO, LNAME, FNAME, EmailAddress
FROM Eligmember
WHERE ((FORMYEAR = 2018 and FORMSEMESTER = 4) OR
(FORMYEAR = 2019 and FORMSEMESTER = 2)
) and
presentmem <> 'Y'
) c
GROUP BY chpid;
这是一个简单的 GROUP BY 场景:
SELECT
Count(*) as Counter
,chpid
,max(SECNO) as SECNO
,LNAME
,FNAME
,max(EmailAddress) as EmailAddress
FROM
Eligmember
WHERE
((FORMYEAR = 2018 AND FORMSEMESTER = 4) OR
(FORMYEAR = 2019 AND FORMSEMESTER = 2))
AND presentmem <> 'Y'
GROUP BY
chpid
,LNAME -- for each person within the chpid
,FNAME -- ^
;
这"结合了chpid,secno,lname,fname和电子邮件,使记录唯一用于比较。 它还生成所需的结果集。 这假设 fname、lname 和电子邮件地址之间存在 1:1 的关系;如果没有,则需要按电子邮件地址删除最大值和组。