主页 / dba / 问题 / 240582
Accepted
learning
Asked: 2019-06-15 06:01:21 +0800 CST

当2个表中存在重复记录时获取单个记录

  • 772

我有 2 张桌子。TableA是销售额，TableB是潜在客户表。我正在尝试查看潜在客户中的人是否TableB进行了购买，并且他们的名字是否出现在销售TableA中。如果他们这样做，我只想要出现在两张桌子上的人的名字。

表A（销售）

 customer   school            email     address    state    phone   Sales
 john doe   hight middle    [email protected]   111 updown   wi     2222222  $500 
 john doe   hight middle    [email protected]   111 updown   wi     2222222  $100 
 zow hi     hight middle    [email protected]  111 updown   wi     2222222  $200 
 calispo    dwight elem     [email protected] 34 jimm elis CA     35450456 $1,000 
 Jordan     clift high       [email protected]  1 unversity GA     11111111 $100

表 B（导联）

 customer       school           email       address    state
 john doe      hight middle    [email protected]    111 updown     wi
 Kawi          hight middle     [email protected]     111 updown    wi
 calispo       dwight elem    [email protected]   34 jimm elis   CA

我想看到的：

 customer   school           email       address    state
 john doe   hight middle    [email protected]   111 updown   wi     2222222   $500
 alispo     dwight elem     [email protected] 34 jimm elis CA     35450456 $1,000

我尝试了这个查询，但它返回了我想要的信息的副本

 SELECT a.email as [sales_school]
  ,b.email[leadds_insti]
  ,a.*
  ,b.*
  FROM [dbo].[Sales] a 
  right JOIN leads b ON a.email= b.email
sql-server join

2 个回答

  1. Best Answer

    您可以尝试为每封电子邮件找到最高销售额，然后将其与潜在客户一起加入：

    WITH maxsales AS
(SELECT 
sales.*,
ROW_NUMBER() (PARTITION BY sales.email ORDER BY Sales.Sales DESC) AS rn
FROM [dbo].[Sales]
) 
SELECT maxsales.email as [sales_school]
  ,leads.email[leadds_insti]
  ,maxsales .*
  ,leads.*
FROM maxsales 
INNER JOIN leads ON leads.email= maxsales.email
WHERE maxsales.rn = 1
    • 1

  2. 如果您只想要销售表中的潜在客户名称，那么应该这样做

    SELECT TableB.*
FROM TableB
WHERE TableB.email IN
(SELECT email from TableA)
    • 1

相关问题