SQL查询不返回预期结果

问题描述：

这里是我稍微擦过的SQL查询（只是列/表/数据库名称）。我保持结构与生产相同。这是从我的MySQL日志中获取的查询。SQL查询不返回预期结果

SELECT  master.pk AS pk, 
      master.vendor_id AS vendorId, 
      master.vendor_text AS vendorName, 
      master.device_id AS deviceId, 
      master.device_did AS deviceDid, 
      master.device_text AS deviceName, 
      master.class_id AS classId, 
      master.class_text AS className, 
      master.oem_id AS oemId, 
      master.oem_did AS oemDid, 
      master.oem_text AS oemName, 
      master.oem_vendor_text AS oemVendor, 
      master.comment AS comment, 
      master.v1 AS v1, 
      master.v2 AS v2, 
      master.status AS status, 
      dev_mod_join.mod_text AS module 
FROM  master 
LEFT JOIN dev_mod_join 
     ON master.device_did = dev_mod_join.dev_id 
WHERE ( 
      master.device_text LIKE CONCAT("%", 'search term', "%") 
     OR master.vendor_text LIKE CONCAT("%", 'search term', "%") 
     OR master.oem_text LIKE CONCAT("%", 'search term', "%") 
     OR master.oem_vendor_text LIKE CONCAT("%", 'search term', "%") 
    ) 
GROUP BY master.vendor_text 
LIMIT  0, 25

由于某些原因，这只会返回每个供应商的一个结果。这很奇怪。每个供应商可以有多个设备。如果我搜索一个我认识的设备很多的供应商，它仍然只会为该供应商返回一个设备（它也会返回其他供应商提供的其他设备，但其他设备与搜索字词相匹配，但只能提供一个供应商）。

我错过了任何东西，我认为它与“OR”有关，但从我所知道的情况来看，情况并非如此。

一如既往的帮助表示赞赏。

编辑：OMG，我可能只是想通了。我使用GROUP BY而不是ORDER BY。

哇，一直在寻找这一段时间，像SO 5分钟，我得到它

那'GROUP BY'条款是在其他SQL口味非法的。我认为你应该尝试删除它并向我们报告。 – bernie 2010-10-08 06:10:38

这确实是它是什么。谢谢。 – Tim 2010-10-08 06:19:28

答

查询似乎是声音在大多数情况下，看起来很可疑的只有一部分是由该组。尝试使用不同的select子句而不是group by。

像这样：

SELECT DISTINCT  
      master.pk AS pk, 
      master.vendor_id AS vendorId, 
      master.vendor_text AS vendorName, 
      master.device_id AS deviceId, 
      master.device_did AS deviceDid, 
      master.device_text AS deviceName, 
      master.class_id AS classId, 
      master.class_text AS className, 
      master.oem_id AS oemId, 
      master.oem_did AS oemDid, 
      master.oem_text AS oemName, 
      master.oem_vendor_text AS oemVendor, 
      master.comment AS comment, 
      master.v1 AS v1, 
      master.v2 AS v2, 
      master.status AS status, 
      dev_mod_join.mod_text AS module 
FROM  master 
LEFT JOIN dev_mod_join 
     ON master.device_did = dev_mod_join.dev_id 
WHERE ( 
      master.device_text LIKE CONCAT("%", 'search term', "%") 
     OR master.vendor_text LIKE CONCAT("%", 'search term', "%") 
     OR master.oem_text LIKE CONCAT("%", 'search term', "%") 
     OR master.oem_vendor_text LIKE CONCAT("%", 'search term', "%") 
    )

还不能接受，但这是100％。摆脱了GROUP BY，切换到ORDER BY并添加了DISTINCT。谢谢！ – Tim 2010-10-08 06:20:12

SQL查询不返回预期结果

相关推荐