php - php 中的 codeigniter 交叉表

Question

我使用以下代码

  Sample Gender Handedness    1   Female  Right-handed    2   Male    Left-handed    3   Female  Right-handed    4   Male    Right-handed    5   Male    Left-handed    6   Male    Right-handed    7   Female  Right-handed    8   Female  Left-handed    9   Male    Right-handed    10  Female  Right-handed

              Left-             handed    Right-                       handed Total    Males   2   3   5    Females 1   4   5    Total   3   7   10

我有上面的表，我需要得到第二个表。我需要使用交叉表，但我不知道该怎么做。有人能帮助我吗。我在 codeigniter 中执行此操作。

Answer 1

您可以通过查询来完成

SELECT gender,
       SUM(CASE WHEN Handedness = 'Left-handed' THEN 1 ELSE 0 END) left_handed,
       SUM(CASE WHEN Handedness = 'Right-handed' THEN 1 ELSE 0 END) right_handed,
       COUNT(*) total
  FROM Table1
 GROUP BY gender WITH ROLLUP

输出:

| GENDER | LEFT_HANDED | RIGHT_HANDED | TOTAL ||--------|-------------|--------------|-------|| Female |           1 |            4 |     5 ||   Male |           2 |            3 |     5 || (null) |           3 |            7 |    10 | -- this is your total produced by WITH ROLLUP

Here is SQLFiddle demo

---

I'm not an expert in CI but your code might look like

$sql = "SELECT gender,
               SUM(CASE WHEN Handedness = 'Left-handed' THEN 1 ELSE 0 END) left_handed,
               SUM(CASE WHEN Handedness = 'Right-handed' THEN 1 ELSE 0 END) right_handed,
               COUNT(*) total
          FROM Table1
         GROUP BY gender WITH ROLLUP";
$data = $this->db->query($sql)->result_array();