我有桌子
Sport Name gender
----------------------
tennis John M
football David M
football Jane F
football Brad M
我希望的结果是
Count Sport Gender
1 Tennis M
0 tennis F
2 football M
1 football F
到目前为止我能得到的就像
Count Sport Gender
4 Tennis M
4 tennis F
4 football M
4 football F
或者我只能数1个条件,例如网球和马累。
我已经阅读了聚合函数,但仍然无法实现。我想不出单独解决它的解决方案。
这是一种方法,CROSS JOIN
+ LEFT OUTER JOIN
+COUNT
SELECT a.sport,
b.gender,
Count(c.sport) AS count
FROM (SELECT DISTINCT sport
FROM yourtable) a
CROSS JOIN (SELECT 'M' AS Gender
UNION ALL
SELECT 'F') b
LEFT OUTER JOIN yourtable c
ON a.sport = c.sport
AND b.gender = c.gender
GROUP BY a.sport,
b.gender
演示:
表架构和数据
CREATE TABLE yourtable
(
Sport VARCHAR(50),
NAME VARCHAR(50),
gender CHAR(1)
);
INSERT INTO yourtable VALUES ('tennis','John','M' );
INSERT INTO yourtable VALUES ('football','David','M' );
INSERT INTO yourtable VALUES ('football','Jane','F' );
INSERT INTO yourtable VALUES ('football','Brad','M' );
结果
╔══════════╦════════╦═══════╗
║ sport ║ gender ║ count ║
╠══════════╬════════╬═══════╣
║ football ║ M ║ 2 ║
║ football ║ F ║ 1 ║
║ tennis ║ M ║ 1 ║
║ tennis ║ F ║ 0 ║
╚══════════╩════════╩═══════╝
这里的关键是,由于您希望能够获得0计数,因此CROSS JOIN
即使表中不存在符合该条件的行(例如,没有一位女网球选手的行),也需要获取所有可能的组合。 )。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句