WITH T AS(
SELECT '张三'
NAME ,'苹果' FRUIT FROM DUAL
UNION
SELECT '李四'
NAME ,'香蕉' FRUIT FROM DUAL
UNION
SELECT '王五'
NAME ,'橘子' FRUIT FROM DUAL
UNION
SELECT '王五'
NAME ,'橘子' FRUIT FROM DUAL
UNION
SELECT '李四'
NAME ,'橘子' FRUIT FROM DUAL
UNION
SELECT '张三'
NAME ,'橘子' FRUIT FROM DUAL)SELECT
NAME
,COUNT(CASE WHEN FRUIT='苹果' THEN 1 END ) AS 苹果
,COUNT(CASE WHEN FRUIT='香蕉' THEN 1 END ) AS 香蕉
,COUNT(CASE WHEN FRUIT='桔子' THEN 1 END ) AS 桔子
,COUNT(1)FROM
TGROUP BY NAME复制代码
|