m0_57748717 2023-01-18 21:57 采纳率: 76.3%
浏览 108
已结题

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如何写才更好吗?

  • 写回答

7条回答 默认 最新

  • weixin_53295371 2023-01-20 11:30
    关注

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

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

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(6条)

报告相同问题?

问题事件

  • 系统已结题 1月31日
  • 已采纳回答 1月23日
  • 修改了问题 1月19日
  • 赞助了问题酬金15元 1月18日
  • 展开全部

悬赏问题

  • ¥15 基于RTKLIB框架写的精密单点定位-AR
  • ¥15 PFENet的预训练权重
  • ¥15 程序哪有错误怎么改?
  • ¥15 交换机和交换机之间的链路带宽以及主机带宽的理解
  • ¥15 ai创想家对战模式代码
  • ¥15 集合A由3个2行4列二维数组构成,从集合A中任意取一个二维数组元素、如果该二维数组元素的对应列位置的上、下两数都是奇数,而且仅有2个列是奇数/奇数,则该数组有意义,并放入集合B中打印输出。
  • ¥15 电信IPV6 无法外网访问吗
  • ¥15 有偿求效果比较好的遥感影像匹配的c++代码
  • ¥15 博主,你好,我下载了你的智能网联汽车辅助驾驶安全信息检测系统,现在不会运行,可以教我吗,
  • ¥15 怎么在excle输入下列公式