查看: 6186|回复: 13

[SQL] 分组数据,每个分组取10条数据,保持排序同时不能出现列重复数据

[复制链接]
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
发表于 2018-4-26 11:36 | 显示全部楼层 |阅读模式
本帖最后由 hqliu41 于 2018-4-26 13:52 编辑

取100个asset_id(每个asset_id按顺序排列好),每个asset_id上笛卡尔关联10个account_id(每个account_id也按顺序排列好,asset_id和account_id的关联需要满足一定条件)
即进行分组,每个asset_id为一组(组有顺序要求),每组10条数据(也可能越往后满足条件的条数越少)。
【要求】
1、asset_id和account_id的关联需要满足一定条件,不是纯粹的全关联;
2、按序号每组优先挑选满足自己需要的account_id,每组10条数据中account_id不能出现前面的组里面已经存在的account_id。

请教一下各位大神,这个SQL要怎么写。  我写到下面就不知道该如何继续了。

【简化描述如下】
从A表中取10个asset_id,每个asset_id笛卡儿积关联2个account_id(存在顺序要求),取不重复的数据。

详细结果见期望结果
A表

B表
ASSET_ID
rownum

ACCOUNT_ID
rownum
1
1

1
1
2
2

2
2
3
3

3
3
4
4

4
4
5
5

5
5
6
6



7
7



8
8



9
9



10
10













笛卡儿积关联



ASSET_ID
ACCOUNT_ID



1
1



1
2



1
3



1
4



1
5



2
1



2
2



2
3



2
4



2
5



3
1



3
2



3
3



3
4



3
5



….








期望结果
1x2



ASSET_ID
ACCOUNT_ID



1
1



1
2



2
3



2
4



3
5







【原需求】见附件
【原需求SQL】见下
SELECT  a.asset_id asset_id,
   
           b.account_id account_id,
   
           a.project_type  project_type,
   
           a.rnoA,
   
           b.rnoB,
   
           DENSE_RANK() OVER(ORDER BY  a.rnoA) AS rn0,
   
           ROW_NUMBER() OVER(PARTITION BY  a.rnoA ORDER BY b.rnoB ASC) rn1
   
      FROM (SELECT a.*, rownum AS  rnoA
   
              FROM (SELECT iaq.asset_id  asset_id,
   
                            iaq.project_type project_type,
   
                           iaq.end_date  asset_end_date,
   
                           iaq.rate  asset_rate,
   
                            fla.create_time asset_create_time,
   
                           fla.loan_money  - fla.already_money remain_money,
   
                            iaq.status,
   
                           fla.deadline  deadline,
   
                           fla.user_id  user_id
   
                      FROM  intel_asset_queue iaq
   
                      LEFT JOIN  fron_loan_application fla
   
                        ON fla.id =  iaq.asset_id
   
                     WHERE 1 = 1
   
                       AND fla.inner_type  = 0
   
                       AND iaq.ctr_status  != 9
   
                       AND fla.loan_money  >= fla.already_money
   
                       AND  fla.loan_status = 4
   
                       AND fla.start_date  < SYSDATE - 1
   
                       AND fla.end_date  > SYSDATE
   
                     ORDER BY  iaq.create_time ASC, --规则1+规则4
   
                               iaq.rate        DESC --规则5
   
                    ) a
   
             WHERE rownum <= 100)  a,
   
           (SELECT b.*, rownum AS rnoB
   
              FROM (SELECT ita.id             account_id,
   
                            ita.usable_balance usable_balance,
   
                            ita.end_date        account_end_date,
   
                            ita.create_time     account_create_time,
   
                            ita.join_money      account_join_money,
   
                            ita.ctr_status,
   
                            itp.deadline,
   
                           ita.user_id
   
                      FROM  intel_tender_account ita
   
                      LEFT JOIN  intel_tender_plan itp
   
                        ON itp.id =  ita.plan_id
   
                     WHERE 1 = 1
   
                       AND ita.ctr_status  != 9
   
                       AND  ita.tender_start_date < SYSDATE - 1
   
                       AND  ita.tender_end_date > SYSDATE
   
                       AND  ita.usable_balance >= 100
   
                     ORDER BY  ita.ctr_status DESC, ita.create_time) b
   
             WHERE rownum <= 1000)  b
   
     WHERE 1 = 1
   
       AND a.user_id != b.user_id
   
       AND CASE
   
             WHEN ABS(a.remain_money -  b.usable_balance) = 0 THEN
   
              1
   
             WHEN ABS(a.remain_money -  b.usable_balance) >= 100 THEN
   
              1
   
             WHEN ABS(a.remain_money -  b.usable_balance) < 100 AND
   
                  a.remain_money > 2 *  100 AND b.usable_balance > 2 * 100 THEN
   
              1
   
             ELSE
   
              0
   
           END = 1
   
       AND NOT EXISTS
   
     (SELECT 1
   
              FROM  intel_tender_match_queue t
   
             WHERE t.asset_id =  a.asset_id
   
               AND t.account_id =  b.account_id
   
               AND t.create_time >  SYSDATE - (1 / 1440 * 3))
   
       AND (a.deadline, b.deadline)  IN
   
           (SELECT DISTINCT  ipr.asset_deadline, ipr.account_deadline
   
              FROM intel_prematch_rule  ipr
   
             WHERE ipr.status = 1
   
               AND (ipr.group_on_time IS  NULL OR ipr.group_on_time < sysdate)
   
               AND (ipr.group_off_time IS  NULL OR ipr.group_off_time > sysdate))



表数据.xlsx

62.51 KB, 下载次数: 16

论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-26 13:32 来自手机 | 显示全部楼层
用文本而不是附件显示结果

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
 楼主| 发表于 2018-4-26 13:53 | 显示全部楼层
〇〇 发表于 2018-4-26 13:32
用文本而不是附件显示结果

我更新了一个简化需求描述,希望得到一个比较简单高效的SQL写法指导。

原需求可能较为复杂。

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2018-4-26 14:21 来自手机 | 显示全部楼层
with a as(select level s,level rn from dual connect by level<=10), b as(select level c,level rn2 from dual connect by level<=5) select s,c from a,b where ceil(rn2/2)=rn order by 1;

使用道具 举报

回复
论坛徽章:
532
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2018-4-26 21:43 | 显示全部楼层
参见置顶帖提问的智慧给出CREATE, INSERT脚本。
结果中要求ACCOUNT_ID不重复,那么结果和B表的行数一样多?A表不一定要全连上?是这个意思吗?

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
 楼主| 发表于 2018-4-26 23:44 | 显示全部楼层
newkid 发表于 2018-4-26 21:43
参见置顶帖提问的智慧给出CREATE, INSERT脚本。
结果中要求ACCOUNT_ID不重复,那么结果和B表的行数一样多 ...

对的,基本是i这个意思

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
 楼主| 发表于 2018-4-26 23:45 | 显示全部楼层
〇〇 发表于 2018-4-26 14:21
with a as(select level s,level rn from dual connect by level

我学习一下。 正在理解,谢谢回复。

使用道具 举报

回复
论坛徽章:
4
奥运会纪念徽章:摔跤
日期:2008-07-27 10:13:53授权会员
日期:2008-08-08 10:10:59生肖徽章2007版:狗
日期:2009-02-24 15:44:37生肖徽章2007版:猪
日期:2009-03-03 10:48:54
发表于 2018-4-27 22:00 | 显示全部楼层
写成函数或存储过程,不是很方便?写这么复杂

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
 楼主| 发表于 2018-4-28 12:35 | 显示全部楼层
〇〇 发表于 2018-4-26 13:32
用文本而不是附件显示结果

如果我是这种情况,该怎么处理呢?


A表
rownum表示优先级排序

B表
rownum表示优先级排序
ASSET_ID
money
rownum

ACCOUNT_ID
money
rownum
1
300
1

1
100
1
2
200
2

2
100
2
3
200
3

3
100
3




4
150
4




5
200
5




6
200
6




7
250
7







笛卡儿积规则
还需要满足(a.money - b.money = 0 OR a.money - b.money >= 100)

A.ASSET_ID
A.money
A.rownum
B.ACCOUNT_ID
B.money
B.rownum

1
300
1
1
100
1

1
300
1
2
100
2

1
300
1
3
100
3

1
300
1
4
150
4

1
300
1
5
200
5

1
300
1
6
200
6

2
200
2
1
100
1

2
200
2
2
100
2

2
200
2
3
100
3

2
200
2
5
200
5

2
200
2
6
200
6

3
200
3
1
100
1

3
200
3
2
100
2

3
200
3
3
100
3

3
200
3
5
200
5

3
200
3
6
200
6








期望结果
1条A,对应2条B;且B.ACCOUNT_ID不重复

A.ASSET_ID
A.money
A.rownum
B.ACCOUNT_ID
B.money
B.rownum

1
300
1
1
100
1

1
300
1
2
100
2

2
200
2
3
100
3

2
200
2
5
200
5

3
200
3
6
200
6

使用道具 举报

回复
论坛徽章:
1
优秀写手
日期:2014-12-24 06:00:14
 楼主| 发表于 2018-4-28 12:37 | 显示全部楼层
本帖最后由 hqliu41 于 2018-4-28 12:41 编辑
〇〇 发表于 2018-4-26 14:21
with a as(select level s,level rn from dual connect by level

以及,如果是下面这种情况呢:
A表
rownum表示优先级排序

B表
rownum表示优先级排序
ASSET_ID
money
rownum

ACCOUNT_ID
money
rownum
1
300
1

1
100
1
2
200
2

2
100
2
3
200
3

3
100
3




4
150
4




5
200
5




6
200
6




7
250
7







笛卡儿积规则
还需要满足(a.money - b.money = 0 OR a.money - b.money >= 100)

A.ASSET_ID
A.money
A.rownum
B.ACCOUNT_ID
B.money
B.rownum

1
300
1
1
100
1

1
300
1
2
100
2

1
300
1
3
100
3

1
300
1
4
150
4

1
300
1
5
200
5

1
300
1
6
200
6

2
200
2
1
100
1

2
200
2
2
100
2

2
200
2
3
100
3

2
200
2
5
200
5

2
200
2
6
200
6

3
200
3
1
100
1

3
200
3
2
100
2

3
200
3
3
100
3

3
200
3
5
200
5

3
200
3
6
200
6








期望结果
1条A,对应3条B;且B.ACCOUNT_ID可以有1条重复(注意:对应的3,和重复值1都可以自定义)

A.ASSET_ID
A.money
A.rownum
B.ACCOUNT_ID
B.money
B.rownum

1
300
1
1
100
1

1
300
1
2
100
2

1
300
1
3
100
3

2
200
2
3
100
3

2
200
2
5
200
5

2
200
2
6
200
6

3
200
3
6
200
6




























































































































































































































































使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表