回复人：feng2(蜀山风云) ( ) 信誉：100 | 2006-8-17 17:40:52 | 得分: 15 删除 | |
| 从表面看来，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') | |||
回复人：licsth() ( ) 信誉：99 | 2006-8-17 17:46:17 | 得分: 0 删除 | |
| sql的意图是：保留每种SHOHIN_KBN（区分）最新3组数据； 要求类似：按照考试科目，保留每种科目的最高前3名的分数； 说明：因为最终目的不是显示（select），是删除（delete）操作，所以不能表之间关联，只能写成类似 delete T_KSI_TRN where SHOHIN_KBN ¦ ¦ TEKIYO_FROM in ...... 的样子！所以回答者注意，如有更好写法请指点！！！ | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 17:50:32 | 得分: 0 删除 | |
| feng2(蜀山风云) 兄，先前多次在csdn上见过了，谢谢你的关注。 问题不在(0120060811,0120060812,0120060813)改成（'0120060811','0120060812','0120060813')。 主要是那个“select count(*) over(partition by A.SHOHIN_KBN order by A.TEKIYO_FROM desc) rw ...”中的好像叫‘析构函数’我不太理解它的内核！ | |||
| 回复人：feng2(蜀山风云) () 信誉：100 | 2006-8-17 18:09:45 | 得分: 0 删除 | |
| hehe，你说明一下需求就明白多了； 前面我说的都是表面现象，不太清楚你的要求。 -------------------------------------------------------- sql的意图是：保留每种SHOHIN_KBN（区分）最新3组数据； 要求类似：按照考试科目，保留每种科目的最高前3名的分数； -------------------------------------------------------- 按你这种说法， B.rw >= 4是去掉前三的取法； B.rw < 4 才是取前三。 | |||
回复人：CreatBird(fengniu) ( ) 信誉：100 | 2006-8-17 18:12:08 | 得分: 0 删除 | |
| 楼主的意思是保留前三名的数据。因为那是好学生。楼主想要把坏学生从数据库中删除。 | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 18:17:13 | 得分: 0 删除 | |
| to：feng2(蜀山风云) ---------------- 按你这种说法， B.rw >= 4是去掉前三的取法； B.rw < 4 才是取前三。 ---------------- 一样的。如果用B.rw >= 4，我在delete的时候就写成 not in； 如果用B.rw < 4，就 in。 | |||
| 回复人：feng2(蜀山风云) () 信誉：100 | 2006-8-17 18:19:08 | 得分: 0 删除 | |
| rw是考试科目(排名)，TEKIYO_FROM desc是高分排在前， 所以B.rw >=4 是去掉前三的取法； | |||
| 回复人：feng2(蜀山风云) () 信誉：100 | 2006-8-17 18:20:42 | 得分: 0 删除 | |
| B.rw >= 4 可以用来删除分数倒数3名的记录，相应的用order by A.TEKIYO_FROM | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-17 18:25:34 | 得分: 0 删除 | |
| 楼主可以先把每个科目的前三名的学生id取出来，然后拼成字符串，例如： str = "'001','002','003' " , 然后，delete时，where 学生id in (str) | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 18:25:44 | 得分: 0 删除 | |
| 看样子，星星们都回去修了。 其实我子现在有个方案： 先select 取出实际的前3组数据（key）到后台，之后再把3组数据（key）做成字符串传到delete中就可以了。 但这样就不能由一条sql实现了，看看有没有一条delete就能搞定的高人了？ | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-17 18:26:20 | 得分: 25 删除 | |
| 1. count(...)over(partition by ... order by ...) 是分析函数 按照partition by 后的字段进行分组统计count(...)里的字段 2. 不理解为什么不直接 count(...) over(partition by ...) ,count 的时候是否 order by 没有太大的作用啊？ | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-17 18:27:11 | 得分: 10 删除 | |
| to feng2：楼主关心的不是怎么取前三，而是如何delete删除前三。 | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 18:29:15 | 得分: 0 删除 | |
| feng2(蜀山风云) 兄，取前3/后4的sql已经是那个子sql完成了；所以（风云) 兄关注点应该是为何2个逻辑相同的sql执行结果不一样？ | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 18:33:47 | 得分: 0 删除 | |
| to:xiaoxiao1984(笨猫儿^_^) 多日不见了，先前都是4角的时候还与你冲过刺，我慢了下来！ -------------- 2. 不理解为什么不直接 count(...) over(partition by ...) ,count 的时候是否 order by 没有太大的作用啊？ -------------- 是为了倒叙，取出最高的3组分数，为B.rw >= 4 服务的！ | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-17 18:34:18 | 得分: 0 删除 | |
| 要求类似：按照考试科目，保留每种科目的最高前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 ) 楼主贴出来的需求没有看得很明白 | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-17 18:37:44 | 得分: 0 删除 | |
| 嘎嘎，count的时候即使进行order by 的话，意义也不是很大的（对结果没有影响） 猜测应该进行按照某字段进行分组排序，删除每组中非前三名的纪录？ 不知道理解的是否正确？ | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 18:47:23 | 得分: 0 删除 | |
| 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 | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-17 19:00:16 | 得分: 0 删除 | |
| 下面的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 ) | |||
| 回复人：licsth() () 信誉：99 | 2006-8-17 19:37:20 | 得分: 0 删除 | |
| 我好像有点思路为什么了！ 子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 ) ----------------- | |||
| 回复人：feng2(蜀山风云) () 信誉：100 | 2006-8-17 20:27:50 | 得分: 0 删除 | |
| 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 | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-18 9:27:22 | 得分: 0 删除 | |
| 呵呵，不一样的原因在于你要求什么样子的排序阿，每组的前三名怎么定义的，如果出现并列，怎么排名阿 用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 ...)就可以了 | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 9:27:40 | 得分: 0 删除 | |
| to:feng2(蜀山风云) --------------- row_number() .... 选出的是无重叠排序记录； count() .... 选出的是有重叠排序记录。 --------------- 还真没注意row_number()/count()的区别，但先前是考虑到重复数据的问题，所以有下面的语句： ... from (select distinct SHOHIN_KBN, TEKIYO_FROM from T_KSI_TRN) A) B ... | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 9:35:40 | 得分: 0 删除 | |
| 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 ) | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 9:48:12 | 得分: 0 删除 | |
| to：FOREVER_SJK() 老兄你干嘛，做广告也得有点公德，公共场所的‘牛皮癣’咋还贴到这了。没意思了，下次注意点啊！ 改了还是可以做好同志的嘛！ | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 10:00:37 | 得分: 0 删除 | |
| 看来用一个delete处理后4名的问题是不行了，因为工作需要就先用我上面的方法，先select前3名的key，然后再用字符串传到delete的where中了。 今天下班前再结贴大给大家！等等高人指点！！！ 谢谢：风云 、笨猫、fengniu | |||
| 回复人：kingtoo008() () 信誉：100 | 2006-8-18 10:34:42 | 得分: 0 删除 | |
| 路过．．． ------------------------------------------------------------------------------------ 100M.Net空间+50M企业邮局=60元/年 100M.Net空间+国际顶级域名＝100元/年 国际顶级域名.com.net.cn=50元/年 本站申请域名可绑定免费10M Asp.Net空间 1000M.Net空间 + 100M MsSql数据库 + 1000M企业邮局 + 顶级域名=600元/年 数据库 企业邮局 网站推广 整机租用 美国空间 网站建设 均有售 还有很多优惠套餐提供给各个用户层. 有意者可联系电话：021-64802212 传真：021-64802212 咨询信箱：info@kingtoo.com 咨询OICQ：68311305,379620139 81778640 | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 10:40:36 | 得分: 0 删除 | |
| 请风云 、笨猫留下你们的mail，我已经把数据和sql执行的不同结果，做了画面copy，给你们发过去。请你们分析一下。我不明白，为什么会是这个样子。 难道这种方法不好使吗？ 怎样解决如何删除每种科目的最高前3名以外的数据？ | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-18 10:58:31 | 得分: 0 删除 | |
| 邮箱已经短消息发过去了，嘎嘎 | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 11:54:27 | 得分: 0 删除 | |
| 不好意思，吃饭回来了 | |||
| 回复人：kingtoo009() () 信誉：100 | 2006-8-18 12:18:04 | 得分: 0 删除 | |
| 学习中 ------------------------------------------------------------------------------------ 100M.Net空间+50M企业邮局=60元/年 100M.Net空间+国际顶级域名＝100元/年 国际顶级域名.com.net.cn=50元/年 本站申请域名可绑定免费10M Asp.Net空间 1000M.Net空间 + 100M MsSql数据库 + 1000M企业邮局 + 顶级域名=600元/年 数据库 企业邮局 网站推广 整机租用 美国空间 网站建设 均有售 还有很多优惠套餐提供给各个用户层. 有意者可联系电话：021-64802212 传真：021-64802212 咨询信箱：info@kingtoo.com 咨询OICQ：68311305,379620139 81778640 | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-18 13:32:51 | 得分: 0 删除 | |
| 呵呵，不是不可以使用这种方法，而是楼主使用的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条记录 | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-18 13:43:59 | 得分: 0 删除 | |
| CreatBird(fengniu) 和楼主是一个人么，迷糊了？ | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 13:54:43 | 得分: 0 删除 | |
| to 笨猫：我和他是对面！！！ 你的回答，我试了一下，没有问题。我再让他们帮忙测试一下。 你能不能简单讲一下，count(*)和dense_rank(), row_number() 的区别呀？ 你是不是oracle公司的呀！！！这么厉害！！！想你学习！ | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 14:21:38 | 得分: 0 删除 | |
| to: 笨猫 不好意思，刚才他们提示我用dense_rank(),可以得出正确的结果；是我忽略了你答案里的这个函数！ 但有一点还是有疑虑：为什么分别使用count(*)和dense_rank()的sql结果一样, 但作为delete的条件时，执行的结果就不一样？ | |||
| 回复人：feng2(蜀山风云) () 信誉：100 | 2006-8-18 14:50:14 | 得分: 0 删除 | |
| 你分别使用 dense_rank() count() row_number() rank() 进行分组排序，就会发觉它们的妙用。 究竟是该用什么函数，就看你的业务逻辑了。 | |||
| 回复人：xiaoxiao1984(笨猫儿^_^) () 信誉：100 | 2006-8-18 14:52:26 | 得分: 0 删除 | |
| 1. row_number() over() 按照给定的字段进行分组排序,排序的时候如果出现并列排名的时候,仍然按照顺序排名,把并列排名依次往后排名,即出现的排名(不管是否存在并列的情况)都是1，2，3，4... 2. dense_rank()over() 作用同row_number()over()，区别在于如果出现并列排名，出现的排名为：1，2，2，3，3，4，5，6... | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 14:55:01 | 得分: 0 删除 | |
| 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 | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 14:56:13 | 得分: 0 删除 | |
| 上面的那段sql拷贝错了，呵呵 RANK, DENSE_RANK RANK 和 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 | |||
| 回复人：licsth() () 信誉：99 | 2006-8-18 15:02:05 | 得分: 0 删除 | |
| 看来，使用dense_rank()over()/row_number()over()即使取出相同的记录集，作为delete的where条件执行的结果都是不同的。 此问题暂时到此了，有时间我再查delete中的in/not in 有啥限制！ 谢谢：风云、笨猫、fengniu | |||
| 回复人：CreatBird(fengniu) () 信誉：100 | 2006-8-18 15:26:42 | 得分: 0 删除 | |
| 执行下面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的时候，结果就不一样了？ | |||
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
