我有一个漫画数据库,里面有漫画、演职员表和贡献者详细信息。我正在尝试使用此SQL查询过滤我的表:
选择参数必须 * 显示所有内容,但似乎没有在此处显示
SELECT
cc.*,
ct.*,
cpub.*,
c.*,
cs.*
FROM
comic_types AS ct,
comics_publisher AS cpub,
comics AS c,
comics_series AS cs,
comic_credits AS cc
WHERE
c.comic_indexkey = cc.comic_index_key
AND cpub.publisher_index_key = c.comic_publisher
AND c.comic_series = cs.comics_seriesindexkey
AND ct.comic_typeindexkey = c.comic_type
AND cpub.publisher_index_key = c.comic_publisher
ORDER BY
comic_title ASC,
comic_series ASC
LIMIT 0, 15
限制值放置在那里以进行分页。
一些漫画具有相同的贡献者,但角色不同(例如,漫画A将B作为作者和插画家(。这意味着我有两个相同的漫画结果。我使用了一个数组和继续语句来使重复的记录不显示。
$comics_in_table = array();
if (in_array($comic['comic_indexkey'], $comics_in_table)) {
continue;
}
这造成的问题是分页每页仅显示 5 个而不是 15 个,大概是因为它检测到 10 个结果已被"隐藏">
到目前为止,在善意的帮助下,创建了这个:
SELECT
cc.*,
ct.*,
cpub.*,
c.*,
cs.*
FROM
comic_types AS ct,
comics_publisher AS cpub,
comics AS c,
comics_series AS cs,
comic_credits AS cc
WHERE
c.comic_indexkey = cc.comic_index_key
AND cpub.publisher_index_key = c.comic_publisher
AND c.comic_series = cs.comics_seriesindexkey
AND ct.comic_typeindexkey = c.comic_type
AND cpub.publisher_index_key = c.comic_publisher
GROUP BY
cc.comic_index_key
ORDER BY
c.comic_title ASC,
c.comic_series ASC
LIMIT 0, 15
并且 SQL 返回此问题:
#1055 - SELECT list 的表达式 #1 不在 GROUP BY 子句中,并且包含非聚合列 'sewters.cc.credit_index_key',该列在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=不兼容only_full_group_by
尝试使用
SELECT DISTINCT cc.*, ct.* ...
或附加一个
GROUP BY fieldname
在 SELECT 查询的末尾,其中fieldname
是要分组到一个条目中的数据库列。