我有 MariaDB 版本 10.2.14-MariaDB。我有一个看起来像这样的表:
CREATE TABLE `card_data_link` (
`card_id` INT(11) NULL DEFAULT NULL,
`data_id` INT(11) NULL DEFAULT NULL,
INDEX `card_id` (`card_id`) USING BTREE,
INDEX `data_id` (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
它有这些数据:
card_id data_id
"63" "21"
"34" "3"
"34" "21"
"34" "21"
它加入了这个:
CREATE TABLE `card_data` (
`data_id` INT(11) NOT NULL AUTO_INCREMENT,
`data` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
PRIMARY KEY (`data_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51
;
它的数据如下所示:
data_id data
"21" "{R}"
"3" "{3}"
如果我运行此查询:
select
cdl.card_id,
cdl.data_id,
cd.data
from
card_data_link cdl
left join
card_data cd on cd.data_id = cdl.data_id
group by
cdl.card_id
,cd.data_id
结果:
card_id data_id data
"34" "3" "{3}"
"34" "21" "{R}"
"63" "21" "{R}"
有没有办法让我获得第二个
"34" "21" "{R}"
行显示,因此它返回:
card_id data_id data
"34" "3" "{3}"
"34" "21" "{R}"
"34" "21" "{R}"
"63" "21" "{R}"
就像它在做一个选择不同的,即使我没有把不同的。我没有正确分组吗?谢谢!
GROUP BY
SELECT
根据其参数聚合语句的结果值。从 SQL 中删除
GROUP BY
子句和参数将允许出现重复项。对于 MariaDB 推荐
sql_mode=ONLY_FULL_GROUP_BY
(非默认,参考:手册),以确保 SQL 语句相对于GROUP BY
.