SQL查询...多个最大值选择.需要帮助 [英] SQL query ...multiple max value selection. Help needed

问题描述

Business World 1256987每月10 2009-10-28

Business World 1256987 monthly 10 2009-10-28

商业世界1256987每月10 2009-09-23

Business World 1256987 monthly 10 2009-09-23

Business World 1256987每月10 2009-08-18

Business World 1256987 monthly 10 2009-08-18

Linux 4 U 456734每月25 2009-12-24

Linux 4 U 456734 monthly 25 2009-12-24

Linux 4 U 456734每月25 2009-11-11

Linux 4 U 456734 monthly 25 2009-11-11

Linux 4 U 456734每月25 2009-10-28

Linux 4 U 456734 monthly 25 2009-10-28

我通过查询得到以下结果:

I get this result with the query:

SELECT DISTINCT ljm.journelname,ljm. subscription_id,
    ljm.frequency,ljm.publisher, ljm.price, ljd.receipt_date 
FROM lib_journals_master ljm,
    lib_subscriptionhistory 
    lsh,lib_journal_details ljd 
WHERE ljd.journal_id=ljm.id 
ORDER BY ljm.publisher

---

我需要的是每本期刊的最新日期?

---

What I need is the latest date in each journal?

我尝试了以下查询:

SELECT DISTINCT ljm.journelname, ljm.subscription_id,
    ljm.frequency, ljm.publisher, ljm.price,ljd.receipt_date
FROM lib_journals_master ljm,
    lib_subscriptionhistory lsh,
    lib_journal_details ljd
WHERE ljd.journal_id=ljm.id 
AND ljd.receipt_date = (
    SELECT max(ljd.receipt_date) 
    from lib_journal_details ljd)

---

但是它给了我整列中的最大值.我需要的结果将有两个日期(每本杂志的最大值)，但是此查询只给我一个日期?

---

But it gives me the maximum from the entire column. My needed result will have two dates (maximum of each magazine), but this query gives me only one?

推荐答案

如果需要日期的最大值，则应使用分组依据". 应该看起来像这样:

You should use Group By if you need the Max from date. Should look something like this:

SELECT 
   ljm.journelname
   , ljm.subscription_id
   , ljm.frequency
   , ljm.publisher
   , ljm.price
   , **MAX(ljd.receipt_date)**
FROM 
   lib_journals_master ljm
   , lib_subscriptionhistory lsh
   , lib_journal_details ljd
WHERE 
   ljd.journal_id=ljm.id 
GROUP BY 
   ljm.journelname
   , ljm.subscription_id
   , ljm.frequency
   , ljm.publisher
   , ljm.price

这篇关于SQL查询...多个最大值选择.需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！