首页 \ 问答 \ mysql左连接只从左表中选择最高(mysql left join selecting only highest from left table)

mysql左连接只从左表中选择最高(mysql left join selecting only highest from left table)

我试过调整来自LEFT JOINmysql的 SELECT最大行的解决方案:如何INNER JOIN一个表但是限制加入到具有最高投票或计数的1个结果? 但无法弄清楚。

我正在加入两个表,正在加入的表有两个重复的行,唯一的区别是字段'page_id' - 我想要一个具有最高page_id号的表

tagID  page_id  companyID  teamID  companyID
1510   289      16         9       16
1418   163      16         9       16

(还有其他我希望的文字字段,但这些字段不用于匹配,所以在这里排除它们)

我的原始查询是

SELECT * FROM `cms_company_tags` 
LEFT JOIN `cms_companies` ON `cms_companies`.`companyID`=`cms_company_tags`.`companyID` 
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC 

它选择了两行

tagID  page_id  companyID  teamID  newsID  companyID
1510   289      16         9       0       16
1418   163      16         9       0       16

我试过了

Select cms_company_tags.*, cms_companies.*
From cms_company_tags
    Left Join cms_companies
        On cms_companies.companyID = cms_company_tags.companyID
            And cms_companies.page_id = (
                        Select Max( t.page_id )
                        From cms_companies As t
                        Where t.page_id = cms_company_tags.page_id
                GROUP BY cms_company_tags.tagID
                ORDER BY cms_company_tags.tagID DESC 
                        )
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC

SELECT * FROM `cms_company_tags` 
LEFT JOIN `cms_companies` ON `cms_companies`.`companyID`=`cms_company_tags`.`companyID` 
AND `cms_companies`.`page_id` = (SELECT MAX(page_id) AS pageID from `cms_companies` where `cms_companies`.`page_id` = `cms_company_tags`.`page_id`)                             
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC

两者都归来了

tagID  page_id  companyID  teamID  newsID  companyID
1510   289      16         9       0       NULL
1418   163      16         9       0       16

所有文本字段也是NULL

我想要唯一最高的page_id。 我可以使用一个重复的行,如果它是最高的那个,那么文本字段为NULL,而不是最低的那个,因为我正在得到如何。


编辑:

虽然这个解决方案返回LOWEST page_id但它确实过滤掉了副本,幸运的是我所需的文本部分没有受到影响。 在这里发帖希望这个部分解决方案对某人有用

SELECT * FROM cms_company_tags INNER JOIN(SELECT companyID,companyName,page_path,MAX(page_id)AS MaxPageID FROM cms_companies GROUP BY cms_companies.page_id)MaxPages ON cms_company_tags.companyID = MaxPages.companyID AND cms_company_tags.page_id = MaxPages.MaxPageID AND cms_company_tags.teamID = 9按MaxPages排序。 companyName ASC


I've tried adapting solutions from SELECT biggest row from a LEFT JOIN and mysql: How to INNER JOIN a table but limit join to 1 result with the highest vote or count? but can't figure it out.

I'm joining two tables, the one being joined has two duplicate rows, the only difference is field 'page_id' - I want the one with the highest page_id number

tagID  page_id  companyID  teamID  companyID
1510   289      16         9       16
1418   163      16         9       16

(there are other text fields I want but these aren't used in matching so excluded them here)

My original query was

SELECT * FROM `cms_company_tags` 
LEFT JOIN `cms_companies` ON `cms_companies`.`companyID`=`cms_company_tags`.`companyID` 
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC 

which selected both rows

tagID  page_id  companyID  teamID  newsID  companyID
1510   289      16         9       0       16
1418   163      16         9       0       16

I've tried

Select cms_company_tags.*, cms_companies.*
From cms_company_tags
    Left Join cms_companies
        On cms_companies.companyID = cms_company_tags.companyID
            And cms_companies.page_id = (
                        Select Max( t.page_id )
                        From cms_companies As t
                        Where t.page_id = cms_company_tags.page_id
                GROUP BY cms_company_tags.tagID
                ORDER BY cms_company_tags.tagID DESC 
                        )
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC

and

SELECT * FROM `cms_company_tags` 
LEFT JOIN `cms_companies` ON `cms_companies`.`companyID`=`cms_company_tags`.`companyID` 
AND `cms_companies`.`page_id` = (SELECT MAX(page_id) AS pageID from `cms_companies` where `cms_companies`.`page_id` = `cms_company_tags`.`page_id`)                             
WHERE `cms_company_tags`.`teamID`='9'
ORDER BY `cms_companies`.`companyName` ASC

Both of which return

tagID  page_id  companyID  teamID  newsID  companyID
1510   289      16         9       0       NULL
1418   163      16         9       0       16

With all the text fields being NULL too

I want the only the highest page_id. I could live with a duplicate row with NULL for the text field if it were the highest one and not the lowest one as I'm getting how.


EDIT:

Although this solution from returns the LOWEST page_id it does filter out the duplicate, luckily for me the text parts I need weren't affected. Posting here in the hope this partial solution is useful to someone

SELECT * FROM cms_company_tags INNER JOIN ( SELECT companyID, companyName, page_path, MAX(page_id) AS MaxPageID FROM cms_companies GROUP BY cms_companies.page_id ) MaxPages ON cms_company_tags.companyID = MaxPages.companyID AND cms_company_tags.page_id = MaxPages.MaxPageID AND cms_company_tags.teamID = 9 ORDER BY MaxPages.companyName ASC


原文:https://stackoverflow.com/questions/19185584
更新时间:2022-05-16 14:05

最满意答案

首先:你为什么要加入? 您只是从左表中选择,因此左连接到另一个表不会影响结果。 内部cms_companies会影响结果(通过省略cms_companies不存在的cms_companies ),但左cms_companies不会。

无论如何,要获得公司ID和团队ID的最高页面ID,请尝试以下方法:

SELECT companyID, teamID, MAX(page_ID)
FROM cms_company_tags
GROUP BY companyID, teamID

然后从cms_company_tags获取唯一行,只需加入“最高ID”作为子查询:

SELECT tagID, page_id, companyID, teamID
FROM cms_company_tags
INNER JOIN (
  SELECT companyID, teamID, MAX(page_ID) AS MaxPageID
  FROM cms_company_tags
  GROUP BY companyID, teamID
) MaxPages ON
  cms_company_tags.companyID = MaxPages.companyID AND
  cms_company_tags.teamID = MaxPages.teamID
  cms_company_tags.page_id = MaxPages.MaxPageID

此查询返回所有公司/团队的最大页面和其他信息。 您可以添加WHERE teamID = 9以将结果限制为第9组。


First off: why are you joining? You're only selecting from the left table, so left joining to another table won't affect the results. An inner join would affect results (by omitting companies that don't exist in cms_companies), but a left join won't.

At any rate, to get the highest page ID by company ID and team ID, try this:

SELECT companyID, teamID, MAX(page_ID)
FROM cms_company_tags
GROUP BY companyID, teamID

Then to get the unique rows from cms_company_tags, just join to the "highest by ID" as a subquery:

SELECT tagID, page_id, companyID, teamID
FROM cms_company_tags
INNER JOIN (
  SELECT companyID, teamID, MAX(page_ID) AS MaxPageID
  FROM cms_company_tags
  GROUP BY companyID, teamID
) MaxPages ON
  cms_company_tags.companyID = MaxPages.companyID AND
  cms_company_tags.teamID = MaxPages.teamID
  cms_company_tags.page_id = MaxPages.MaxPageID

This query returns the max page and other information for all companies/teams. You can add WHERE teamID = 9 to limit results to team 9.

相关问答

更多
  • SELECT * FROM table1 INNER JOIN ( SELECT MAX(table1.id) AS id FROM table1 INNER JOIN table2 ON table2.client = table1.client AND table2.campaign=table1.campaign and table2.enabled != 'disabled' GROUP BY table1.client, table1.campaign ) AS m ON m.id = table ...
  • 神奇的加入! 我喜欢它。 下一版MySQL的必备功能。 :-) 与此同时,这就是你所做的。 SELECT m.id, m.cust_id, a.value AS author, r.value AS release, p.value AS price, m.filename, m.hidden FROM media m LEFT JOIN other_table a ON m.id = a.id AN ...
  • MySQL按组使用第一行,因此您必须先订购它,然后才能使用group by: SELECT * FROM (SELECT * FROM `loadhistory` ORDER BY user_id DESC) history GROUP BY date 因此,您可以使用以下查询作为解决方案: SELECT h.date,h.bal as balance, t.amount as 'amount paid' FROM (SELECT * FROM (SELECT * FROM `loadhistory ...
  • 您可能需要第一次加入的JOIN和ON子句。 就像是: SELECT t1.field1, t1.field2, t2.field1, t3.field1 FROM table1 t1 INNER JOIN table2 t2 on t1.ID = t2.ID LEFT JOIN table3 t3 ON t3.field1 = t2.field1 注意,INNER JOIN可能需要是LEFT JOIN,从你的问题中不清楚。 You probably want a JOIN an ...
  • 首先:你为什么要加入? 您只是从左表中选择,因此左连接到另一个表不会影响结果。 内部cms_companies会影响结果(通过省略cms_companies不存在的cms_companies ),但左cms_companies不会。 无论如何,要获得公司ID和团队ID的最高页面ID,请尝试以下方法: SELECT companyID, teamID, MAX(page_ID) FROM cms_company_tags GROUP BY companyID, teamID 然后从cms_company_t ...
  • 您可以非常轻松地链接联接: SELECT ... FROM a LEFT JOIN b on a.somefield=b.somefield LEFT JOIN c on b.otherfield=c.otherfield You can chain joins quite easily: SELECT ... FROM a LEFT JOIN b on a.somefield=b.somefield LEFT JOIN c on b.otherfield=c.otherfield
  • 有一个错误(不明确的列ID)。 两个表都有id列。 您需要添加带id的表别名。 试试 - $sql = "SELECT * FROM people p LEFT JOIN images i ON i.people_ID = p.id WHERE p.id = '1'"; There is an error(ambiguous column id). Both tables have id column. You need to add the table alias with id. try with ...
  • 您可以列出IN列表中的列: LEFT OUTER JOIN inventory ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5) DEMO You can list columns in an IN list: LEFT OUTER JOIN inventory ON Inventory.SKU IN (Mas ...
  • 一种方法是首先收集你的25行,并在第二步中加入它们。 你可以尝试这样的事情: WITH accounts AS ( SELECT * FROM [dbo].[ClientAccounts] ORDER BY [dbo].[ClientAccounts].[ID] ASC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY ) SELECT accounts.*, [dbo].[AccountLink].[RepId] FROM acco ...
  • 您可以在左连接中使用OR,如下所示: LEFT JOIN member_requests AS r ON m.member_id = r.recipient_id OR m.member_id = r.author_id 但是,您的where子句也需要更改: SELECT m.member_id, m.display_name r.author_id, r.recipient_id, r.status FROM members AS m LEFT JOIN mem ...