試圖一次加入 4 - 5 張桌子，因為想要獲取存盤在第 5 張桌子上的 5 張桌子中的多個資料，我正在嘗試計算已加入錦標賽的玩家總數，這作業正常，但主要問題是我在這里面臨的是，當主表中沒有資料時，它仍然回傳我 1 行，所有欄位為空，除了顯示 0 的總玩家，因為它顯示不回傳任何行，任何人都可以幫助他們下面是我的查詢
$getTournamentData = $this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
$getTournamentData = $this->db->join('categories', 'categories.id = tournament.category_id');
$getTournamentData = $this->db->join('games', 'games.game_id = tournament.game_id');
$getTournamentData = $this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
$getTournamentData = $this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id', 'left');
$dataCond['created_by'] = $this->session->userdata('user_id');
if($id != null) {
$dataCond['tournament.id'] = $id;
}
$getTournamentData = $this->db->where($dataCond);
$getTournamentData = $this->db->get('tournament');
所以作為回報，total_players 顯示為 0，其余全部為空，因為表中沒有插入任何資料，但顯示它不應該從資料庫回傳任何資料
uj5u.com熱心網友回復：
您在子句中將聚合函式 ( count()) 與普通列名混合在一起，這會產生意想不到的結果。SELECT請參閱：為什么不能在單個 SELECT 中混合聚合值和非聚合值？
您可以通過添加一個包含GROUP BY子句中所有列名的SELECT子句來解決此問題，但具有 的列名除外count()。請務必完全輸入tournament.*in 中的所有列名GROUP BY，因此請改用tournament.id, tournament.category_id, tournament.game_idetc：
SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id
JOIN tournament_players ON tournament_players.tournamentID = tournament.id
GROUP BY
tournament.id, tournament.category_id, tournament.game_id,
-- add other tournament colums here --
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug
在 CodeIgniter (3) 中，這將轉化為：
$this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
$this->db->from('tournament');
$this->db->join('categories', 'categories.id = tournament.category_id');
$this->db->join('games', 'games.game_id = tournament.game_id');
$this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
$this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id');
$this->db->group_by('tournament.id, tournament.category_id, tournament.game_id,
/* add other tournament columns here */
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug');
或者，您可以使用子選擇，在這種情況下，您可以洗掉tournament_players表的連接：
SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, (
SELECT count(id)
FROM tournament_players
WHERE tournament_players.tournamentID = tournament.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id
$this->db->query()在 CodeIgniter 中使用 with 。
我顯然沒有測驗過這些查詢，所以可能會有錯誤。希望這可以幫助您入門。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/497860.html