星星帮忙：2个sql(1)、（2）为何不能等价？

Question

sql(1):
------------------------------->
 select SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in (0120060811,0120060812,0120060813)
-------------------------------<
sql(2):
------------------------------->
 select SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in 
       (Select B.SHOHIN_KBN || B.TEKIYO_FROM
          from (select count(*) over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw,
                       A.SHOHIN_KBN,
                       A.TEKIYO_FROM
                  from (select distinct SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN) A) B
         where B.rw >= 4)
-------------------------------<备注子sql：
------------------------------->
(Select B.SHOHIN_KBN || B.TEKIYO_FROM
 from (select count(*) over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw,
                       A.SHOHIN_KBN,
                       A.TEKIYO_FROM
       from (select distinct SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN) A) B
 where B.rw >= 4)
-------------------------------<
单独执行结果为：(0120060811,0120060812,0120060813)

Answer 1

从表面看来，sql(1)和sql(2)的效率不等价，
:)
另外，sql(1)有点问题：
select SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in (0120060811,0120060812,0120060813)
改为
select SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in ('0120060811','0120060812','0120060813')

Answer 2

sql的意图是：保留每种SHOHIN_KBN（区分）最新3组数据；
要求类似：按照考试科目，保留每种科目的最高前3名的分数；说明：因为最终目的不是显示（select），是删除（delete）操作，所以不能表之间关联，只能写成类似
delete T_KSI_TRN
where SHOHIN_KBN || TEKIYO_FROM  in ......
的样子！所以回答者注意，如有更好写法请指点！！！

Answer 3

feng2(蜀山风云) 兄，先前多次在csdn上见过了，谢谢你的关注。
问题不在(0120060811,0120060812,0120060813)改成（'0120060811','0120060812','0120060813')。
主要是那个“select count(*) over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw ...”中的好像叫‘析构函数’我不太理解它的内核！

Answer 4

hehe，你说明一下需求就明白多了；
前面我说的都是表面现象，不太清楚你的要求。--------------------------------------------------------
sql的意图是：保留每种SHOHIN_KBN（区分）最新3组数据；
要求类似：按照考试科目，保留每种科目的最高前3名的分数；
--------------------------------------------------------按你这种说法，
B.rw >= 4是去掉前三的取法；
B.rw < 4 才是取前三。

Answer 5

to：feng2(蜀山风云) 
----------------
按你这种说法，
B.rw >= 4是去掉前三的取法；
B.rw < 4 才是取前三。
----------------
一样的。如果用B.rw >= 4，我在delete的时候就写成 not in；
如果用B.rw < 4，就 in。

Answer 6

rw是考试科目(排名)，TEKIYO_FROM desc是高分排在前，
所以B.rw>=4 是去掉前三的取法；

Answer 7

B.rw >= 4 可以用来删除分数倒数3名的记录，相应的用order by A.TEKIYO_FROM

Answer 8

楼主可以先把每个科目的前三名的学生id取出来，然后拼成字符串，例如：
str = "'001','002','003'" , 然后，delete时，where 学生id in (str)

Answer 9

看样子，星星们都回去修了。其实我子现在有个方案：
 先select 取出实际的前3组数据（key）到后台，之后再把3组数据（key）做成字符串传到delete中就可以了。但这样就不能由一条sql实现了，看看有没有一条delete就能搞定的高人了？

Answer 10

1. count(...)over(partition by ... order by ...) 是分析函数
   按照partition by 后的字段进行分组统计count(...)里的字段2. 不理解为什么不直接 count(...) over(partition by ...) ,count 的时候是否 order by 没有太大的作用啊？

Answer 11

to feng2：楼主关心的不是怎么取前三，而是如何delete删除前三。

Answer 12

feng2(蜀山风云) 兄，取前3/后4的sql已经是那个子sql完成了；所以（风云) 兄关注点应该是为何2个逻辑相同的sql执行结果不一样？

Answer 13

to:xiaoxiao1984(笨猫儿^_^) 
多日不见了，先前都是4角的时候还与你冲过刺，我慢了下来！--------------
2. 不理解为什么不直接 count(...) over(partition by ...) ,count 的时候是否 order by 没有太大的作用啊？
--------------
是为了倒叙，取出最高的3组分数，为B.rw >= 4 服务的！

Answer 14

要求类似：按照考试科目，保留每种科目的最高前3名的分数delete from tab_score where rowid in 
(
select rowid from 
(select student , score, row_number()over(partition by Subject order by score desc) as rid from tab_score )
where rid >=4
)楼主贴出来的需求没有看得很明白

Answer 15

嘎嘎，count的时候即使进行order by 的话，意义也不是很大的（对结果没有影响）
猜测应该进行按照某字段进行分组排序，删除每组中非前三名的纪录？
不知道理解的是否正确？

Answer 16

to:xiaoxiao1984(笨猫儿^_^) 
是你那样的想法；
但你的sql和我发的类似，执行结果是不一样的：
假设你的子sql
----------
select rowid from 
(select student , score, row_number()over(partition by Subject order by score desc) as rid from tab_score )
where rid >=4
----------
得出的数据是（7，8，9）；
sql1：delete from tab_score where rowid in （7，8，9）
与
sql2：delete from tab_score where rowid in 
(
select rowid from 
(select student , score, row_number()over(partition by Subject order by score desc) as rid from tab_score )
where rid >=4
)
应该执行的结果不一样！你用如下的类似数据试试：student   score
----------------
01        1
01        2
01        3
01        3
01        7
01        7
01        8
01        9
01        9
01        7

Answer 17

下面的sql1和sql2的执行结果不相同，记录数不同
sql1：select * from tab_score where rowid in （7，8，9）
与
sql2：select * from tab_score where rowid in 
(
select rowid from 
(select student , score, row_number()over(partition by Subject order by score desc) as rid from tab_score )
where rid >=4
)

Answer 18

我好像有点思路为什么了！
子sql中不能使用rownum 之类作为删除的条件。
如子sql：
----------
       (Select B.SHOHIN_KBN || B.TEKIYO_FROM
          from T_KSI_TRN b
          where rownum <= 2          
        )
----------
取出(0120060811,0120060812,0120060813)；以下2个sql是不等的：
sql(1):
-----------------
delete T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in (0120060811,0120060812,0120060813)
-----------------
sql(2):
-----------------
delete T_KSI_TRN
 WHERE SHOHIN_KBN || TEKIYO_FROM  in 
       (Select B.SHOHIN_KBN || B.TEKIYO_FROM
          from T_KSI_TRN b
          where rownum <= 2          
        )
-----------------

Answer 19

row_number() ....      选出的是无重叠排序记录；
count()      ....      选出的是有重叠排序记录。
---------- ----------- ----------
         1           9          2
         1           9          2
         1           8          3
         1           7          6
         1           7          6
         1           7          6
         1           3          8
         1           3          8
         1           2          9
         1           1         10
---------- ----------- ----------
         1           9          1
         1           9          2
         1           8          3
         1           7          4
         1           7          5
         1           7          6
         1           3          7
         1           3          8
         1           2          9
         1           1         10

Answer 20

呵呵，不一样的原因在于你要求什么样子的排序阿，每组的前三名怎么定义的，如果出现并列，怎么排名阿用row_number的时候，并列的人排名仍然有先后的，例如a,b并列第一名，可能a的排名是1，b的排名是2，所以删除的时候可能会多删除一些人员的纪录；用dense_rank()over()的话，前三名并不代表着3名学生，有可能是4名或者5名等用dense_rank()over(partition by ... order by ...)替换row_number()over(partition by ... order by ...)就可以了

Answer 21

to:feng2(蜀山风云) 
---------------
row_number() ....      选出的是无重叠排序记录；
count()      ....      选出的是有重叠排序记录。
---------------
还真没注意row_number()/count()的区别，但先前是考虑到重复数据的问题，所以有下面的语句：
...
 from (select distinct SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN) A) B
...

Answer 22

to:xiaoxiao1984(笨猫儿^_^) 
其实你关注的地方有点错了，前3名的问题已经可以解决了，但是想删除第4名之后的数据就出问题了！
也就是：
 delete  student   score
 from tab_score 
 WHERE score  in (第4名，第5名，...，第10名)<>不等价：
 delete  student   score
 from tab_score 
  (
   select rowid from 
   (select student , score, row_number()over(partition by Subject order by score desc) as rid from tab_score )
   where rid >=4
  )

Answer 23

to：FOREVER_SJK() 
老兄你干嘛，做广告也得有点公德，公共场所的‘牛皮癣’咋还贴到这了。没意思了，下次注意点啊！
改了还是可以做好同志的嘛！

Answer 24

看来用一个delete处理后4名的问题是不行了，因为工作需要就先用我上面的方法，先select前3名的key，然后再用字符串传到delete的where中了。
今天下班前再结贴大给大家！等等高人指点！！！
谢谢：风云 、笨猫、fengniu

Answer 25

请风云 、笨猫留下你们的mail，我已经把数据和sql执行的不同结果，做了画面copy，给你们发过去。请你们分析一下。我不明白，为什么会是这个样子。难道这种方法不好使吗？怎样解决如何删除每种科目的最高前3名以外的数据？

Answer 26

呵呵，不是不可以使用这种方法，而是楼主使用的count(*)有问题楼主尝试一下：
select aaa.* 
    from t_ksi_trn AAA
  where AAA.rowid  in 
     (select t.rowid 
        from  t_ksi_trn t ,
        (
          select b.shohin_kbn, b.tekiYo_from 
            from (select dense_rank()over(partition by a.shohin_kbn order by a.tekiyo_from desc) rw,
                         /*count(*)over(partition by a.shohin_kbn order by a.tekiyo_from desc) rw,*/
                         a.shohin_kbn,
                         a.tekiyo_from 
                     from (select distinct shohin_kbn, tekiyo_from 
                              from t_ksi_trn) A)B
                   where b.rw >=4 
              )c
       where t.shohin_kbn = c.shohin_kbn
         and t.tekiyo_from = c.tekiyo_from 
     )得到的就是楼主想要的16条记录，而不是40条记录

Answer 27

CreatBird(fengniu) 和楼主是一个人么，迷糊了？

Answer 28

to 笨猫：我和他是对面！！！
你的回答，我试了一下，没有问题。我再让他们帮忙测试一下。
你能不能简单讲一下，count(*)和dense_rank(), row_number()
的区别呀？
你是不是oracle公司的呀！！！这么厉害！！！想你学习！

Answer 29

to: 笨猫
不好意思，刚才他们提示我用dense_rank(),可以得出正确的结果；是我忽略了你答案里的这个函数！但有一点还是有疑虑：为什么分别使用count(*)和dense_rank()的sql结果一样, 但作为delete的条件时，执行的结果就不一样？

Answer 30

你分别使用
dense_rank()
count()
row_number()
rank() 
进行分组排序，就会发觉它们的妙用。
究竟是该用什么函数，就看你的业务逻辑了。

Answer 31

1. row_number() over() 按照给定的字段进行分组排序,排序的时候如果出现并列排名的时候,仍然按照顺序排名,把并列排名依次往后排名,即出现的排名(不管是否存在并列的情况)都是1，2，3，4...2. dense_rank()over()  作用同row_number()over()，区别在于如果出现并列排名，出现的排名为：1，2，2，3，3，4，5，6...

Answer 32

http://blog.csdn.net/teng_s2000/archive/2006/02/24/608548.aspx
在网上搜索了一下，大牛们可以看看。SELECT speaker, track, score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
  NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC以下为结果集：speaker    track      score       rownum tile
---------- ---------- ----------- ------ ----
Jessica    Dev        9           1      1
Ron        Dev        9           2      1
Suzanne    DB         9           3      1
Kathy      Sys        8           4      1
Michele    Sys        8           5      2
Mike       DB         8           6      2
Kevin      DB         7           7      2
Brian      Sys        7           8      2
Joe        Dev        6           9      3
Robert     Dev        6           10     3
Dan        Sys        3           11     3

Answer 33

上面的那段sql拷贝错了，呵呵RANK, DENSE_RANKRANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：SELECT speaker, track, score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
  RANK() OVER(ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC以下为结果集：speaker    track      score       rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica    Dev        9           1      1   1
Ron        Dev        9           2      1   1
Suzanne    DB         9           3      1   1
Kathy      Sys        8           4      4   2
Michele    Sys        8           5      4   2
Mike       DB         8           6      4   2
Kevin      DB         7           7      7   3
Brian      Sys        7           8      7   3
Joe        Dev        6           9      9   4
Robert     Dev        6           10     9   4
Dan        Sys        3           11     11  5

Answer 34

看来，使用dense_rank()over()/row_number()over()即使取出相同的记录集，作为delete的where条件执行的结果都是不同的。
此问题暂时到此了，有时间我再查delete中的in/not in 有啥限制！
谢谢：风云、笨猫、fengniu

Answer 35

执行下面sql:
select distinct SHOHIN_KBN, TEKIYO_FROM
  from T_KSI_TRN
SHOHIN_KBN TEKIYO_FROM
01 20060811
01 20060812
01 20060813
01 20060814
01 20060819
01 20060820
02 20060811
02 20060812
02 20060813执行下面sql:
select dense_rank() over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw,
       rank() over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw1,
       ROW_NUMBER() over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw2,
       count(*) over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw3,
       A.SHOHIN_KBN,
       A.TEKIYO_FROM
   from (select distinct SHOHIN_KBN, TEKIYO_FROM
            from T_KSI_TRN
        ) A
RW RW1 RW2 RW3 SHOHIN_KBN TEKIYO_FROM
1 1 1 1 01 20060820
2 2 2 2 01 20060819
3 3 3 3 01 20060814
4 4 4 4 01 20060813
5 5 5 5 01 20060812
6 6 6 6 01 20060811
1 1 1 1 02 20060814
2 2 2 2 02 20060813
3 3 3 3 02 20060812
4 4 4 4 02 20060811这几个函数执行的结果没有区别，但是为什么进行delete的时候，结果就不一样了？