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

假设有一张表，结构如下：
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如何写才更好吗？

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

首先，你在 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 或更高版本。

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

用having count解试试，
group by name having count(fruit) > 1

试试下面这个代码,因为一个人在10月1号吃了两个水果,10月也可能吃了两种水果,所以名字需要去重

SELECT distinct name
FROM 表名
GROUP BY name,date
HAVING count(fruit)>=2

select Name ,Date
from table n
where n.Fruit in('苹果','雪梨','葡萄','香蕉')
group by Name ,Date
having count(distinct Fruit)>=2

SQL语句如下：

SELECT Name, Date, 
MAX(CASE WHEN Fruit = '苹果' THEN Fruit END) AS Fruit_1,
MAX(CASE WHEN Fruit = '雪梨' THEN Fruit END) AS Fruit_2,
MAX(CASE WHEN Fruit = '葡萄' THEN Fruit END) AS Fruit_3,
MAX(CASE WHEN Fruit = '香蕉' THEN Fruit END) AS Fruit_4
FROM table
GROUP BY Name, Date
HAVING COUNT(DISTINCT Fruit) >= 2;

在 Oracle 中，可以使用以下 SQL 语句来筛选出同一时间食用两种或以上水果的姓名：

SELECT name
FROM table
WHERE time IN (SELECT time
               FROM table
               GROUP BY time
               HAVING COUNT(DISTINCT fruit) > 1)
GROUP BY name
HAVING COUNT(DISTINCT time) > 1;

其中：

table 为表名。
time 为时间列名。
fruit 为水果列名。
name 为姓名列名。
首先使用子查询筛选出食用水果种类大于1的时间，再在外层查询中使用这些时间筛选出在这些时间中食用水果种类大于1的姓名。
为了使结果更准确，可以在上述 SQL 语句中加入其他条件，例如：

按照时间范围筛选，如：
AND time BETWEEN '2022-01-01' AND '2022-12-31'
按照特定水果筛选，如：
AND fruit IN ('Apple', 'Banana')
按照特定姓名筛选，如：
AND name = 'John Smith'

这些条件可以放在上述 SQL 语句中的合适位置，以筛选出更符合需求的结果。

另外，还可以使用 JOIN 操作将多个表连接起来，以便获取更多关于用户、水果等信息。

可以使用group by和having来实现。

select name, date, 
       listagg(frute, ',') within group (order by frute) as fruits
from table
where frute in ('苹果', '雪梨', '葡萄', '香蕉')
group by name, date
having count(distinct frute) >= 2;

这样就可以得到每个人每天食用了多少种水果，并且筛选出了那些吃了两种或以上水果的人。