Oracle中如何用SQL筛选出同一时间食用两种或以上水果的姓名

Question

假设有一张表，结构如下：
Nmae Date Fruit
小明 2021/10/10 苹果
小明 2021/10/10 香蕉
小明 2021/10/10 葡萄
小明 2021/10/22 雪梨
小明 2021/10/11 樱桃
小明 2021/10/10 香蕉
小明 2022/10/20 水蜜桃
小红 2021/10/20 苹果
小红 2021/10/21 雪梨
小红 2021/10/22 香蕉
小红 2021/10/21 香蕉
小红 2021/10/20 水蜜桃
小红 2021/10/21 葡萄
小刚 2021/10/25 香蕉
小刚 2021/10/21 苹果
小刚 2021/10/25 水蜜桃
小刚 2021/10/25 葡萄
小刚 2021/10/21 香蕉

现在有一个业务需求，需求概括起来就是需要用SQL筛选出同一个Date里面吃了“苹果、雪梨、葡萄、香蕉”当中两种或以上的人员姓名，并输出以下表结构：
Name Date Fruit_1 Fruit_2 Frute_3 Frute_4

我个人思路是:

with a as
 (select t.name, t.date, t.frute
    from table t
   where t.frute in ('苹果', '雪梨', '葡萄', '香蕉')),

b as
 (select a.name, a.date, a.frute frute_1, t2.frute frute_2
    from a
    join table t2
      on a.date = t2.date
     and a.name = t2.name
     and t2.frute != t1.frute
     and t2.frute in ('苹果', '雪梨', '葡萄', '香蕉')),

c as
 (select b.name, b.date, b.frute_1, b.frute_2, t3.frute frute_3
    from b
    left join table t3
      on b.date = t3.date
     and t3.name = b.name
     and t3.frute != b.frute_1
     and t3.fruit != frute_2
     and t3.frute in ('苹果', '雪梨', '葡萄', '香蕉')),

d as
 (select c.name, c.date, c.frute_1, c.frute_2, c.frute_3, c.frute frute_4
    from c
    left join table t4
      on c.date = t4.date
     and t4.name = c.name
     and t4.frute != c.frute_1
     and t4.fruit != c.frute_2
     and t4.frute != c.frute_3
     and t4.frute in ('苹果', '雪梨', '葡萄', '香蕉'))

select * from c

如果在原表结构右边再新增一列num，表示每天吃的水果种类的对应数量，输出时候也需要将这个每天吃的水果种类的对应数量一并输出，SQL语句该怎么去改写呢？

我感觉我的思路好复杂且有错，真正执行在业务几千万行的大表也会很慢，请教各位这个需求的SQL如何写才更好吗？

Answer 1

你的思路是正确的，但是你的语句有一些问题。

首先，你在 with a as 中使用了表名为 table，你需要更改为实际的表名。

其次，在 with b as 中，你使用了 t1.frute 和 t2.frute，但是在 with a as 中，你只使用了一张表，没有定义 t1。

最后，在 with c as 和 with d as 中，你使用了 c.frute_1, c.frute_2, c.frute_3, c.frute_4，但是你没有在之前的 with b as 和 with c as 中定义这些字段。

修改这些问题之后，你的代码应该能够正确地运行。

这里是一个示例语句：

WITH a AS (
    SELECT t.name, t.date, t.fruit
    FROM table t
    WHERE t.fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
),
b AS (
    SELECT a.name, a.date, a.fruit fruit_1, t2.fruit fruit_2
    FROM a
    JOIN table t2
        ON a.date = t2.date
        AND a.name = t2.name
        AND t2.fruit != a.fruit
        AND t2.fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
),
c AS (
    SELECT b.name, b.date, b.fruit_1, b.fruit_2, t3.fruit fruit_3
    FROM b
    JOIN table t3
        ON b.date = t3.date
        AND t3.name = b.name
        AND t3.fruit != b.fruit_1
        AND t3.fruit != b.fruit_2
        AND t3.fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
),
d AS (
    SELECT c.name, c.date, c.fruit_1, c.fruit_2, c.fruit_3, t4.fruit fruit_4
    FROM c
    JOIN table t4
        ON c.date = t4.date
        AND t4.name = c.name
        AND t4.fruit != c.fruit_1
        AND t4.fruit != c.fruit_2
AND t4.fruit != c.fruit_3
AND t4.fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
)
SELECT DISTINCT d.name, d.date, d.fruit_1, d.fruit_2, d.fruit_3, d.fruit_4
FROM d

这段语句使用了 CTE（Common Table Expression，公共表表达式）的语法，从 a 到 d 分别对数据进行筛选，最后选取符合条件的数据。

你可以根据实际情况，来修改下面的语句。

在上面的语句中，我使用了 JOIN 语句来连接数据，并对数据进行筛选。

但是，在实际使用中，如果你的数据量很大，这种方法可能会导致性能问题。

另外的一种方法是使用子查询。例如：

SELECT name, date
FROM table
WHERE date IN (
    SELECT date
    FROM table
    WHERE fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
    GROUP BY date, name
    HAVING COUNT(DISTINCT fruit) >= 2
)
GROUP BY name, date

这段语句先选取出所有符合条件的日期，再根据日期进行筛选。

这种方法可能比之前的方法更高效，因为它不需要连接大量的数据。

需要注意的是，如果你使用子查询，你需要把表名换成你所使用的实际表名。

这只是一种可能的解决方案，还有其他方法，可能更加简洁和高效，请根据实际情况来选择。

另外一种解决方案是使用 window 函数。

使用 window 函数可以在一个查询中统计每个人每天吃的水果数量，并进行筛选。

例如：

SELECT name, date, 
       SUM(CASE WHEN fruit IN ('苹果', '雪梨', '葡萄', '香蕉') THEN 1 ELSE 0 END) 
         OVER (PARTITION BY name, date) as count_fruit
FROM table
WHERE fruit IN ('苹果', '雪梨', '葡萄', '香蕉')
HAVING count_fruit >= 2

这段语句使用了 SUM 和 CASE 函数来统计每个人每天吃的水果数量，并对符合条件的数据进行筛选。

这种方法也是一种高效的解决方案，可以避免使用连接和子查询等方法带来的性能问题。

这些给出的解决方案都是可行的，可以根据实际情况来选择使用。

当然，还有其他方法可以解决这个问题，比如使用 PIVOT 和 UNPIVOT 操作，或者使用其他第三方工具来实现。

使用 PIVOT 和 UNPIVOT 可以将行数据转换为列数据，方便查询和统计。

SELECT *
FROM
  (SELECT name, date, fruit
   FROM table
   WHERE fruit IN ('苹果', '雪梨', '葡萄', '香蕉'))
PIVOT
  (COUNT(fruit)
   FOR fruit
   IN ('苹果', '雪梨', '葡萄', '香蕉'))

这段语句首先筛选出符合条件的数据，然后使用 PIVOT 操作将数据转换为列，最后根据需要进行筛选。

需要注意的是，PIVOT 和 UNPIVOT 操作需要使用 Oracle 11g 或更高版本。

这些方法都是可行的，可以根据实际使用。望采纳。