我正在使用 mysql 数据库,我正在实现此查询:
SELECT
LS.id AS livestock_species_id,
LS.parent_livestock_species_id AS parent_livestock_species_id,
LS.livestock_species_name_en AS livestock_species_name_en,
LSN.livestock_species_name AS livestock_species_name,
LSN.description AS description,
LS.image_link AS image_link
FROM LivestockDetails AS LD
INNER JOIN LivestockSpecies AS LS
ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName AS LSN
ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3
WHERE
LD.ls_vaccination_id is not null
上一个查询返回以下结果集:
livestock_species_id parent_livestock_species_id livestock_species_name_en livestock_species_name description image_link
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
2 Chicken Inkoko Inkoko https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221
2 Chicken Inkoko Inkoko https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
1 Cow Inka Inka https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c
2 Chicken Inkoko Inkoko https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221
2 Chicken Inkoko Inkoko https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221
您可以看到返回的记录是重复的,我需要没有这些重复。
所以我的想法是使用组成的子句。
我的问题是尝试以这种方式进行操作(按 livestock_species_id 检索字段(:
SELECT
LS.id AS livestock_species_id,
LS.parent_livestock_species_id AS parent_livestock_species_id,
LS.livestock_species_name_en AS livestock_species_name_en,
LSN.livestock_species_name AS livestock_species_name,
LSN.description AS description,
LS.image_link AS image_link
FROM LivestockDetails AS LD
INNER JOIN LivestockSpecies AS LS
ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName AS LSN
ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3
WHERE
LD.ls_vaccination_id is not null
GROUP BY
livestock_species_id
mySQL返回以下错误消息:
42000 Expression#1的选择列表不在组中,并包含非聚集列的" digital_services_db.ls.id"',该列在功能上不依赖于"组中的列"子句。这与sql_mode = hell
为什么?怎么了?我想念什么?我该如何解决这种情况以避免重复?
您需要通过(或汇总(指定组中的所有列。在您的情况下,由于您拥有重复的记录,因此可以这样做:
GROUP BY livestock_species_id,parent_livestock_species_id,
livestock_species_name_en, livestock_species_name,
description,image_link
或者您可以使用不同的:
SELECT DISTINCT
LS.id AS livestock_species_id,
LS.parent_livestock_species_id AS parent_livestock_species_id,
LS.livestock_species_name_en AS livestock_species_name_en,
LSN.livestock_species_name AS livestock_species_name,
LSN.description AS description,
LS.image_link AS image_link
与我的评论有关:防止重复项首先出现 - 我猜想LivestockDetails
中有多行与同一物种有关。但是,在查询中,您无法从该表中访问任何数据。
如果您只想报告LivestockDetails
中至少有一排的物种,请改用EXISTS
检查:
SELECT
LS.id AS livestock_species_id,
LS.parent_livestock_species_id AS parent_livestock_species_id,
LS.livestock_species_name_en AS livestock_species_name_en,
LSN.livestock_species_name AS livestock_species_name,
LSN.description AS description,
LS.image_link AS image_link
FROM LivestockSpecies AS LS
LEFT JOIN LivestockSpeciesName AS LSN
ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3
WHERE
EXISTS (SELECT * from LivestockDetails LD
WHERE LD.live_stock_species_id = LS.id
and LD.ls_vaccination_id is not null)
这应该产生更好的结果(如果优化器做得很好(,因为我们首先不会生成重复项。
(如果存在检查的相关子查询不正常,则您也可能想尝试:
SELECT
LS.id AS livestock_species_id,
LS.parent_livestock_species_id AS parent_livestock_species_id,
LS.livestock_species_name_en AS livestock_species_name_en,
LSN.livestock_species_name AS livestock_species_name,
LSN.description AS description,
LS.image_link AS image_link
FROM (SELECT DISTINCT live_stock_species_id
FROM LivestockDetails
WHERE ls_vaccination_id is not null) AS LD
INNER JOIN LivestockSpecies AS LS
ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName AS LSN
ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3
至少尽早停止重复(