SQL允许一个字段返回值

Question

在我的办公室，属于两个部门的都是普通人。对于查询，我希望对其进行限制，以便每个人只与一个部门相关联。

例如，下面是两个HR表:

table1

ID   |  Last_Name | Department
--------------------------------
 1        Doe        Informatics
 2        Miller     Database Management
 2        Miller     Informatics
 3        Johnson    Engineering
 4        Mitchell   Database Management
 4        Mitchell   Engineering

表

ID   |  Date_of_sale
--------------------------------
 1        8-2-2012        
 1        1-4-2003     
 2        5-23-2000     
 2        1-17-2003
 2        12-30-2001
 2        9-8-2013
 3        4-19-2013
 4        3-8-2015
 4        11-8-2013
 4        2-12-2007

我想这样做:

SELECT Last_Name, FIRST(Department), Date_of_Sale 
From column1, 
FROM column1 JOIN column2 ON column1.id=column2.id
GROUP BY Last_Name 

返回:

          Last_Name | Department |     Date_of_sale 
       ----------------------------------------
        Doe         Informatics            8-2-2012
        Doe         Informatics            1-4-2003
        Miller      Database Management    5-23-2000
        Miller      Database Management    1-17-2003
        Miller      Database Management    12-30-2001
        Miller      Database Management    9-8-2013    
        Johnson     Engineering            4-19-2013
        Mitchell    Database Management    3-8-2015
        Mitchell    Database Management    11-8-2013
        Mitchell    Database Management    2-12-2007

每个人现在只有一个部门与他们相关联。

Answer 1

在不了解RDBMS的情况下给出具体的答案并不容易。一个适用于大多数系统的通用答案是使用一个嵌套的选择和聚合:

SELECT c1.ID, c1.Last_name, c1.Department, c2.Date_of_Sale
FROM
    (SELECT
       ID
       ,Last_name
       ,MIN(Department) as Department
    FROM
       column1
    GROUP BY
       ID
       ,Last_name) c1
    INNER JOIN column2 c2
    ON c1.ID = c2.ID

然后在SQL-SERVER和大多数其他支持公共表表达式和窗口函数的RDBMS上，您可以创建一个分区的ROW_NUMBER()来选择您想要的(注意cte也可以是嵌套的select)。

;WITH cte AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (PARTITION BY  ID ORDER BY Department) as RowNumber
    FROM
       column1
)
SELECT
    c1.Id
    ,c1.Last_name
    ,c1.Department
    ,c2.Date_of_Sale
FROM
    cte c1
    INNER JOIN column2 c2
    ON c1.Id = c2.Id
WHERE
    c1.RowNumber = 1

这是一种典型的方式，如果您将以某种方式在您将返回的部门上添加一个存在，例如记录创建日期…但是，如果支持窗口函数，并且您并不真正关心哪个部门，您可以抓取分区的MIN()或MAX()，这将只是1 select语句中记录的升序。

SELECT DISTINCT
    c1.ID
    ,c1.Last_name
    ,MIN(c1.Department) OVER (PARTITION BY c1.ID) as Department
    ,c2.Date_of_Sale
FROM
    column1 c1
    INNER JOIN column2 c2
    ON c1.ID = c2.ID

Answer 2

有几种方法可以做到这一点，这取决于您正在使用的SQL类型以及您希望如何为每个姓氏选择部门。

这里有几个使用MySQL的例子。

1. 选择首先列出的部门:

   SELECT id, last_name, 
   SUBSTRING_INDEX(GROUP_CONCAT(department), ',', 1) AS Department
   FROM column1
   GROUP BY id, last_name
   

2. 按字母顺序选择部门:

   SELECT id, last_name, MIN(department)
   FROM column1
   GROUP BY id, last_name
   

测试在这里:http://sqlfiddle.com/#!9/67bf3/8

Answer 3

使用子查询获取每个用户的第一个部门，然后连接到第二个表:

SELECT  t1.Last_Name, 
        t1.Department, 
        t2.Date_of_Sale 
    FROM (select id, Last_Name, MIN(Department) Department from column1 group by id, Last_Name) t1
    JOIN column2 t2 ON 
        t1.id = t2 .id