mysql - 查找表中的重复行

Question

我有一个合作伙伴列表，每个合作伙伴项目都有值 partner_main=0。输入合作伙伴项目内容的人知道合作伙伴名称 partner_name 是否出现两次或更多次，如果是，他会在其中一行上设置 partner_main=1。

如果有多个合作伙伴具有相同的 partner_name，则具有条目 partner_main=1 的合作伙伴应仅显示在其他不存在的合作伙伴行旁边重复。

Mysql代码:

SELECT *, COUNT(partner_name)
FROM partner
WHERE partner_name IN 
       (
         SELECT partner_name 
         FROM partner partner_main=1
       )
GROUP BY partner_name
HAVING (COUNT(partner_name) > 1)
UNION
SELECT *, COUNT(partner_name)
FROM partner
WHERE partner_name NOT IN 
       (
         SELECT partner_name 
         FROM partner partner_main=1
       )
GROUP BY partner_name
HAVING (COUNT(partner_name) > 1)

错误日志显示:

[26-Feb-2014 17:28:56 GMT] PHP Fatal error: Uncaught exception 'Exception' with message 'Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 )
UNION
SELECT *, COU' at line 3 (SELECT *, COUNT(partner_name)
FROM mm_partner
WHERE partner_name IN (SELECT partner_name FROM mm_partner partner_main=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 )
UNION
SELECT *, COUNT(partner_name)
FROM mm_partner
WHERE partner_name NOT IN (SELECT partner_name FROM mm_partner partner_main=1)
GROUP BY partner_name
HAVING ( COUNT(partner_name) > 1 ))' thrown in /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php on line 282
#0 /wwwroot.wwwnew/system/modules/core/library/Contao/Database/Statement.php(253): Contao\Database\Statement->query()
#1 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Rules/SimpleQuery.php(75): Contao\Database\Statement->execute(Array)
#2 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Filter.php(102): MetaModels\Filter\Rules\SimpleQuery->getMatchingIds()
#3 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/Filter/Setting/Collection.php(212): MetaModels\Filter\Filter->getMatchingIds()
#4 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(201): MetaModels\Filter\Setting\Collection->getParameterFilterWidgets(Array, Array, Object(MetaModels\FrontendIntegration\FrontendFilterOptions))
#5 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/FrontendFilter.php(76): MetaModels\FrontendIntegration\FrontendFilter->getFilters()
#6 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(74): MetaModels\FrontendIntegration\FrontendFilter->getMetaModelFrontendFilter(Object(MetaModels\FrontendIntegration\Module\Filter))
#7 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): MetaModels\FrontendIntegration\Module\Filter->compile()
#8 /wwwroot.wwwnew/system/modules/metamodels/MetaModels/FrontendIntegration/Module/Filter.php(63): Contao\Module->generate()
#9 /wwwroot.wwwnew/system/modules/core/elements/ContentModule.php(63): MetaModels\FrontendIntegration\Module\Filter->generate()
#10 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(490): Contao\ContentModule->generate()
#11 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(184): Contao\Controller->getContentElement(Object(Contao\ContentModel), 'main')
#12 /wwwroot.wwwnew/system/modules/core/modules/Module.php(148): Contao\ModuleArticle->compile()
#13 /wwwroot.wwwnew/system/modules/core/modules/ModuleArticle.php(59): Contao\Module->generate()
#14 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(405): Contao\ModuleArticle->generate(false)
#15 /wwwroot.wwwnew/system/modules/core/library/Contao/Controller.php(229): Contao\Controller->getArticle(Object(Contao\ArticleModel), false, false, 'main')
#16 /wwwroot.wwwnew/system/modules/core/pages/PageRegular.php(138): Contao\Controller->getFrontendModule('0', 'main')
#17 /wwwroot.wwwnew/index.php(239): Contao\PageRegular->generate(Object(Contao\PageModel), true)
#18 /wwwroot.wwwnew/index.php(407): Index->run()
#19 {main}

Answer 1

下面的方法有效吗？

SELECT *, 
       COUNT(partner_name)
  FROM partner
 WHERE partner_name IN (
              SELECT partner_name 
                FROM partner 
               WHERE partner_main=1
       )
GROUP BY 
       partner_name
HAVING COUNT(partner_name) > 1
UNION ALL
SELECT *, 
       COUNT(partner_name)
  FROM partner
 WHERE partner_name IN (
               SELECT partner_name 
                 FROM partner 
                WHERE partner_main=0
       )
GROUP BY 
       partner_name
HAVING COUNT(partner_name) = 1