选择具有唯一列值的一行

Question

这是数据

id    name     start_date    end_date    merchant_id
===================================================
111   name1    25-nov-11    31-jan-12    9999
222   name2    23-nov-11    25-dec-11    9999
333   name3    25-nov-11    25-nov-12    9999
444   name4    20-nov-11    20-nov-11    9999
555   name5    25-nov-11    25-dec-11    8888
666   name6    19-oct-11    20-nov-11    8888
777   name7    20-nov-11    20-jun-12    8888

我需要获取按照 start_date（降序）排序的所有行，其中 start_date <= today 且 end_date >= today，但每个 merchant_id 限制为1。这意味着如果查询找到多个行，则只返回第一个。

---

测试脚本

CREATE TABLE DEAL
(
  ID            VARCHAR2(40 BYTE)       NOT NULL,
  NAME          VARCHAR2(255 BYTE),
  START_DATE    DATE,
  END_DATE      DATE,
  MERCHANT_ID   NUMBER(22),
 CONSTRAINT DEAL PRIMARY KEY (ID)
);

INSERT ALL
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('111','name1','25-nov-11','31-jan-12','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('222','name2','23-nov-11','25-dec-11','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('333','name3','25-nov-11','25-nov-12','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('444','name4','20-nov-11','20-nov-11','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('555','name5','25-nov-11','25-dec-11','8888')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('666','name6','19-oct-11','20-nov-11','8888')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('777','name7','20-nov-11','20-jun-12','8888')
SELECT * FROM dual;

运行这个代码：

SELECT DISTINCT merchant_id, id, name, start_date, end_date FROM deal WHERE start_date <= trunc(sysdate) AND end_date >= trunc(sysdate) ORDER BY start_date DESC;

由于相同的商家ID被多次返回，因此无法返回所需的结果：

MERCHANT_ID  ID  NAME  START_DATE  END_DATE 
===========================================
9999         111  name1  25-NOV-11  31-JAN-12  
9999         333  name3  25-NOV-11  25-NOV-12 
8888         555  name5  25-NOV-11  25-DEC-11  
9999         222  name2  23-NOV-11  25-DEC-11  
8888         777  name7  20-NOV-11  20-JUN-12

- BC atguystats

---

你可以使用BETWEEN子句代替start_date<=today和end_date>=today。 - Muhammad Saifuddin

我一直在尝试使用distinct、limit、group by等语句，但都没有成功。我可以得到所有符合起始和结束日期的行，但不知道如何每个merchant_id只返回1行。 - BC atguystats

你使用哪个数据库？如果你使用的是Oracle，一个解决方案是使用分析函数。 - Giovanni

你使用的是哪个版本的SQL (mySQL) 或 Microsoft SQL 或者 Oracle 等等？ - Sparky

@BCatguystats，根据您发布的表格，您能否也发布一下您期望查询结果是什么？ - bakoyaro

Answer 1

以下的Oracle查询应该可以满足您的需求：

SELECT *
FROM (
    SELECT TABLE1.*, DENSE_RANK() OVER(PARTITION BY MERCHANT_ID ORDER BY START_DATE DESC, ID) R
    FROM TABLE1
    WHERE SYSDATE BETWEEN START_DATE AND END_DATE
)
WHERE R = 1
ORDER BY START_DATE DESC

基本上，它首先按日期筛选行，然后忽略所有与相同的MERCHANT_ID共享第一个行。

请注意，“第一个”是根据降序START_DATE顺序定义的。如果两行具有相同的START_DATE，则使用ID顺序解决“争议”。

- Branko Dimitrijevic

Answer 2

嗨，类似这样的代码可能有效：

select id,    name,     start_date,    end_date from (
select id,    name,     start_date,    end_date, ROW_NUMBER()
   OVER (PARTITION BY merchant_id ORDER BY merchant_id) AS rnum from your_table
   where start_date<=trunc(sysdate) and end_date>=trunc(sysdate))
   where rnum=1
order by start_date desc

如果您提供一个简单的测试脚本来创建和填充表格，我可以给您正确的查询语句。

- Giovanni

---

测试脚本已添加到原始帖子中。 - BC atguystats

Answer 3

根据您运行的SQL类型（如MySQL、SQL Server等），将有不同的答案。

例如，在SQL Server中，您可以说“为每个商家分配一个从1开始的数字，并始终选择1号”。

在通用SQL中，您需要更加含蓄。看起来您的ID列保证是唯一的？如果是这样，其中一种表达问题的方式是“获取在日期范围内的记录，满足同一商家在相同日期范围内没有更高的ID”。

您已经知道如何通过大于小于过滤今天的日期吗？我认为如何实现这一点的答案取决于您使用的SQL类型：

所以请尝试：

SELECT * from myFavoriteTable
  WHERE today() < end_date and today > start_date -- for this date criteria use whatever works
  AND NOT EXISTS ( SELECT * from myFavoriteTable as TooLow
      WHERE today() < end_date and today() > start_date -- as above
      AND TooLow.Merchant = myFavoriteTable.merchant
      AND TooLow.id > myFavoriteTable.id)

- Levin Magruder

Answer 4

所有其他答案似乎都过于复杂了...您可以在Oracle中使用DISTINCT修饰符来获取特定列的一个唯一行。以下是一个查询，您可以使用它来检索您想要的数据，假设您发布的表结构如下：

SELECT DISTINCT merchant_id, id, name, start_date, end_date FROM table WHERE start_date >= today AND end_date < today ORDER BY start_date DESC

- bakoyaro

---

这个查询没有返回所需的结果 SELECT DISTINCT merchant_id, id, name, start_date, end_date FROM deal WHERE start_date <= trunc(sysdate) AND end_date >= trunc(sysdate) ORDER BY start_date DESC; - BC atguystats

不为单个列返回不同的行 - Sasi