SQL Server:按列与另一列
问题描述:
从下面的表中,我想获取itemName和电子邮件,其中相同的itemName已通过电子邮件发送到不同的电子邮件地址。相同的电子邮件可以接收不同的itemNames。相同的itemname可以出现在表格的多个位置,它们并不总是按id排序。 ItemNames是独一无二的,因为它可以通过itemname进行自我加入。SQL Server:按列与另一列
我想:
我尝试了许多使用ROW_NUMBER,按组,有等的查询,但不能得到它的权利。
有人能帮忙吗?
的样本数据:
declare @t table (id int, itemname nvarchar(50), emailto nvarchar(50))
insert into @t
values (1, 'item1', 'email1') --include 1 & 2 because same item went to different emails
, (2, 'item1', 'email2')
, (3, 'item2', 'email1') --exclude because even though email1 received an email before, item2 went to a sinle email
, (4, 'item3', 'email3') --exclude 4, 5, 6 becuase all item3 went to the same email
, (5, 'item3', 'email3')
, (6, 'item3', 'email3')
, (7, 'item4', 'email6')
, (8, 'item4', 'email6') --include 8 & 9, only reason to exclude 7 is to get a distinct list of itemName and email pairs
, (9, 'item4', 'email7')
, (10, 'item3', 'email3') --exclude 10 becuase all item3 went to the same email, this is the same item from 4, 5, 6
;with expectedOutput as
(
select
t.itemname, t.emailto
from @t t
where
t.id IN (1, 2, 8, 9)
)
select *
from expectedOutput
/*
Expected output:
itemname emailto
item1 email1
item1 email2
item4 email6
item4 email7
*/
答
下面是做到这一点的一种方式 - 使用CTE来获取所有已发送给多个电子邮件中的项目,然后加入该CTE与原始表:
;WITH Items AS
(
SELECT itemname
FROM @t
GROUP BY itemname
HAVING COUNT(DISTINCT emailto) > 1
)
SELECT DISTINCT t.itemname, emailto
FROM @t t
INNER JOIN Items i ON t.itemname = i.itemname
结果:
itemname emailto
item1 email1
item1 email2
item4 email6
item4 email7
+0
就是这样,谢谢。 – curious
答
假设你是什么升ooking是唯一的电子邮件和项目对。
with expectedOutput as
(select distinct
t.itemname,
t.emailto
from @t t),
steptwo as (
select tt.itemname, count(distinct tt.emailto) as nemails
from expectedOutput tt
group by tt.itemname
)
select tw.itemname,e.emailto from steptwo tw join expectedOutput e
on tw.itemname = e.itemname
WHERE nemails > 1
产生
item1 email1
item1 email2
item4 email6
item4 email7
我们都在那里。
加1样本数据,请继续发布预期的输出以及 – TheGameiswar
先生好奇我现在很好奇为什么软件开发人员将SQL Server调用为mssql,即使您知道基于您使用的标签的正确名称? :) –
@TheGameiswar预期的结果也在这里的sql脚本... –