我有一个 CarComparison 网站，可以从其他汽车网站获取信息。它提取的其中一个提要来自一个站点，该站点允许放置广告的人多次更新它。通常每 10 到 14 天更新一次汽车。

无论如何，我对他们数据的唯一访问是通过 RSS 提要，我从中解析并提取可用数据。我每分钟都拿到它，里面通常有 15 辆左右的新车。

当我进行导入时，没有简单的方法来查看汽车是否已经在系统中。我确实捕获了原始 ID，以便稍后查看。

我运行的连接表的查询是:

SELECT DISTINCT cc_detail.original_id, cc_detail.year, cc_detail.price, cc_detail.make, cc_detail.model, cc_detail.referrer_site, wposts . *
FROM cc_posts wposts
LEFT JOIN cc_posts_detail cc_detail ON ( wposts.ID = cc_detail.post_id )
WHERE 1 =1
AND (
cc_detail.year >1949
)
AND (
cc_detail.price >0
)
AND cc_detail.referrer_site = 'CarSiteX'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'
AND wposts.post_date < NOW( )
AND cc_detail.year <=2011
AND wposts.post_title NOT LIKE 'Ac%'
AND cc_detail.make != ''
AND cc_detail.model != ''
AND (
cc_detail.price +0
) >100
AND (
wposts.post_date > "2011/01/02 "
)
ORDER BY cc_detail.original_id ASC
LIMIT 30 , 300

问题是我无法弄清楚如何更改查询，以便每个 original_id 值只提取一行。 CarSiteX 上的下注者已经更新了他/她的汽车几次，而我每次都因为同一辆车而排成一排。我确实有唯一的 original_id 那么如何更改上述查询以仅获取 cc_posts_detail 表中每个 original_id 值的最新行？

下面是一些显示问题的示例行:

original_id  year  price  make  model  referrer_site  ID  post_author  post_date  post_date_gmt  post_content  post_title  post_excerpt  post_status  comment_status  ping_status  post_password  post_name  to_ping  pinged  post_modified  post_modified_gmt  post_content_filtered  post_parent  guid  menu_order  post_type  post_mime_type  comment_count
1143583  2000  2900  lexus  is200  CarSitex  9633341  1  2011-01-19 05:34:01  2011-01-19 12:34:01     2000 Manual 2.0 Petrol 136k miles NCT  039 d 0...  Lexus Is200 2000     publish  open  open     lexus-is200-2000-        2011-01-19 05:34:01  2011-01-19 12:34:01     0     0  post     0
1149513  1997  2000  mitsubishi  colt  CarSitex  8978523  1  2011-01-05 12:26:01  2011-01-05 19:26:01     1600cc mivec twin cam 16valve. 175 bhp.Four br...  Mitsubishi Colt 1997     publish  open  open     mitsubishi-colt-1997-        2011-01-05 12:26:01  2011-01-05 19:26:01     0     0  post     0
1149513  1997  2000  mitsubishi  colt  CarSitex  9416296  1  2011-01-14 12:04:01  2011-01-14 19:04:01     1600cc mivec twin cam 16valve. 175 bhp.Four br...  Mitsubishi Colt 1997     publish  open  open     mitsubishi-colt-1997-        2011-01-14 12:04:01  2011-01-14 19:04:01     0     0  post     0
1156791  2004  5950  ford  focus  CarSitex  9163527  1  2011-01-08 10:04:01  2011-01-08 17:04:01     2004 FORD FOCUS 1.4 4 DOOR 78333 MILES NCT D 1...  Ford Focus 2004     publish  open  open     ford-focus-2004-        2011-01-08 10:04:01  2011-01-08 17:04:01     0     0  post     0

看到有两辆三菱小马是同一辆车....

抱歉，如果我提供了太多信息，或者这个问题太多了……这是新问题。任何帮助表示赞赏!

cc_post_details 结构:

id  int(4) 
referrer_site   varchar(100) 
original_id     bigint(8) 
dealer  varchar(255) 
make    varchar(100) 
model   varchar(100) 
colour  varchar(100) 
year    varchar(8) 
engine_size     int(4) 
mileage     int(4) 
price   int(4) 
location    varchar(100) 
fuel_type   varchar(50) 
body_type   varchar(50) 
transmission    varchar(50) 
doors   int(4) 
image_base_url  varchar(255) 
image_main  text 
image_thumb     text 
post_id     int(4) 
date_added  datetime 
underscore_beepbeep_pos     int(11)

cc_posts 结构

    ID  bigint(20) 
post_author     bigint(20) 
post_date   datetime 
post_date_gmt   datetime 
post_content    longtext 
post_title  text 
post_excerpt    text 
post_status     varchar(20) 
comment_status  varchar(20) 
ping_status     varchar(20) 
post_password   varchar(20) 
post_name   varchar(200) 
to_ping     text 
pinged  text 
post_modified   datetime 
post_modified_gmt   datetime 
post_content_filtered   text 
post_parent     bigint(20) 
guid    varchar(255) 
menu_order  int(11) 
post_type   varchar(20) 
post_mime_type  varchar(100) 
comment_count   bigint(20) 

最佳答案

在发布新的重复项时使用触发器仅删除每条旧记录怎么样？

CREATE TRIGGER REMOVE_OLD
ON CC_POST_DETAILS
BEFORE INSERT
AS
DECLARE @O_ID BIGINT(8), @MAKE VARCHAR(100), @MODEL VARCHAR(100)
BEGIN

SELECT @O_ID = INSERTED.ORIGINAL_ID FROM INSERTED
SELECT @MAKE = INSERTED.MAKE FROM INSERTED
SELECT @MODEL = INSERTED.MODEL FROM INSERTED

DELETE * FROM CC_POST_DETAILS
WHERE CC_POST_DETAILS.ORIGINAL_ID = @O_ID
AND CC_POST_DETAILS.MAKE = @MAKE
AND CC_POST_DETAILS.MODEL = @MODEL

END

我没有看到连接发生的确切位置，因此您也想将其作为变量读入，以便更新您的其他表。如果需要，您可以在删除每个旧文件之前运行插入将其放入存档。

关于sql - 连接两个表的工作查询是完美的，除了我无法摆脱重复项，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4858309/