SQL - 最接近 Now() 但唯一的行 [英] SQL - Closest rows to Now() but unique

问题描述

所以我遇到了一个小问题.我有 2 个表，一个包含商家信息、名称等，另一个包含事件.

1. 我每隔几天将价格上传到事件表，因此有很多重复的内容只能通过其 TIMESTAMP 列(上传日期)来区分.

2. 我需要选择最近上传的价格，因此不能选择其他的.

   <块引用>

   事件姓名 日期 价格 商家测试 2012-02-26 £10 ShopX测试 2012-02-26 £11 ShopY测试 2012-02-26 £9 ShopX - 最新价格

   IE.选择最新的价格并确保每个商家都是独一无二的，因此每个商家只有 1 个价格.

这是我目前所拥有的:

SELECT *来自 wp_events, wp_merchantsWHERE 日期 = '2012-02-26'AND eventname = 'testevent'和`上传日期` >现在( )AND wp_events.merchant = wp_merchants.merchant_nameORDER BY 上传日期 DESC

解决方案

试试这个:

从事件 e1 中选择 e1.merchant, e1.price左加入事件 e2on (e1.merchant = e2.merchant and e1.date 

这会让你得到这个结果:

+----------+-------+|商家 |价格 |+------------+-------+|店铺 |11 英镑 ||店铺X |9 英镑 |+------------+-------+

因此，您必须添加所需的联接和过滤器.

这不是您要找的吗?

从 wp_events e1 中选择 e1.merchant, e1.price左加入 wp_events e2on (e1.merchant = e2.merchant and e1.uploaddate 

So I am having a slight issue. I have 2 tables, one contains the merchant information, name etc and another contains Events.

1. I upload prices to the event table every few days so there is a lot of duplicate content only distinguished through its TIMESTAMP column(Uploaddate).

2. I need to select the prices for the most recent uploads and therefore the others not to be selected.

   Event
   name    date            price      merchant
   test    2012-02-26      £10        ShopX
   test    2012-02-26      £11        ShopY
   test    2012-02-26      £9         ShopX     -   LATEST PRICE
   

   IE. Select prices which are the most up-to-date and make sure each merchant is unique, so only 1 price per merchant.

Here is what I have so far:

SELECT *
FROM wp_events, wp_merchants
WHERE DATE =  '2012-02-26'
AND eventname =  'testevent'
AND  `uploaddate` > NOW( ) 
AND wp_events.merchant = wp_merchants.merchant_name
ORDER BY uploaddate DESC 

解决方案

Give this a try:

select e1.merchant, e1.price from event e1
left join event e2
on (e1.merchant = e2.merchant and e1.date < e2.date)
where e2.merchant is null

This will get you this result:

+----------+-------+
| MERCHANT | PRICE |
+----------+-------+
| ShopY    | £11   |
| ShopX    | £9    |
+----------+-------+

So, you'll have to add the joins and filters you need.

Edit:

Isn't this what you're looking for?

select e1.merchant, e1.price from wp_events e1
left join wp_events e2
on (e1.merchant = e2.merchant and e1.uploaddate < e2.uploaddate)
where e2.merchant is null

这篇关于SQL - 最接近 Now() 但唯一的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！