写出查询语句:
表名:score
字段:ddate(日期),res(结果)
日期 | 结果 |
---|---|
2021-5-31 | 胜 |
2021-5-31 | 胜 |
2021-5-31 | 负 |
2021-5-31 | 负 |
2021-5-30 | 胜 |
2021-5-30 | 负 |
2021-5-30 | 负 |
要求:
日期 胜 负
2021-5-30 1 2
2021-5-31 2 2
解决
其一:
SELECT
ddate,
decode(res, '胜', num) AS "胜",
decode(res, '负', num) AS "负"
FROM
(
SELECT
ddate,
res,
COUNT(*) num
FROM
score
GROUP BY
ddate,
res
) t
GROUP BY
ddate
其二:
SELECT
ddate,
CASE
WHEN res = '胜' THEN
num
END AS "胜",
CASE
WHEN res = '负' THEN
num
END AS "负"
FROM
(
SELECT
ddate,
res,
COUNT(*) num
FROM
score
GROUP BY
ddate,
res
ORDER BY
ddate
) t
GROUP BY
ddate