17,082
社区成员
发帖
与我相关
我的任务
分享跪求SQL
需求
两张表 A表有10个20岁的男人字段 id age timestamp
B表10个20岁女人 字段 id age timestamp
如何用SQL将10个男人和10个女人一一配对，可以按时间降序配对，关键是要11配对
求大神！！！
两张表 A表有10个20岁的男人字段 id age timestamp
B表10个20岁女人 字段 id age timestamp
如何用SQL将10个男人和10个女人一一配对，可以按时间降序配对，关键是要11配对
求大神！！！
...全文
请发表友善的回复…
发表回复
chengccy 2017-03-08
- 打赏
- 举报
建议用PLSQL做;
WITH T_MAN AS
(SELECT '张三' AS NAME, '上海' AS CITY, '程序员' AS JOB
FROM DUAL
UNION ALL
SELECT '李四' AS NAME, '上海' AS CITY, '美工' AS JOB
FROM DUAL /* union all
select '王五' as name, '上海' as city, '教师' as job from dual */
),
T_WOMAN AS
(SELECT '小丽' AS NAME, '上海' AS CITY, '策划' AS JOB
FROM DUAL
UNION ALL
SELECT '小红' AS NAME, '上海' AS CITY, '程序员' AS JOB
FROM DUAL
UNION ALL
SELECT '小冰' AS NAME, '上海' AS CITY, '个体户' AS JOB
FROM DUAL),
T_PD AS
(SELECT A.NAME AS M_NAME, B.NAME AS W_NAME, ROWNUM AS R
FROM T_MAN A
FULL JOIN T_WOMAN B
ON A.CITY = B.CITY
AND A.JOB <> B.JOB),
T_PD_NEW(M_NAME,
W_NAME,
R,
PD_HIS,
FLAG) AS
(SELECT M_NAME, W_NAME, R,
CAST(',' || M_NAME || ',' || W_NAME || ',' AS VARCHAR2(4000)), 1
FROM T_PD
WHERE R = 1
UNION ALL
SELECT B.M_NAME, B.W_NAME, A.R + 1,
CASE
WHEN INSTR(A.PD_HIS, ',' || B.M_NAME || ',') > 0 OR
INSTR(A.PD_HIS, ',' || B.W_NAME || ',') > 0 THEN
A.PD_HIS
ELSE
A.PD_HIS || B.M_NAME || ',' || B.W_NAME || ','
END,
CASE
WHEN INSTR(A.PD_HIS, ',' || B.M_NAME || ',') > 0 OR
INSTR(A.PD_HIS, ',' || B.W_NAME || ',') > 0 THEN
0
ELSE
1
END
FROM T_PD_NEW A, T_PD B
WHERE A.R + 1 = B.R)
SELECT T1.*, T2.*
FROM (SELECT *
FROM T_PD_NEW
WHERE FLAG = 1) T
--回头找出光棍
FULL JOIN T_MAN T1
ON T1.NAME = T.M_NAME
FULL JOIN T_WOMAN T2
ON T2.NAME = T.W_NAME
陈大鱼 2017-03-07
- 打赏
- 举报
不局限于SQL有没有其他思路呢？？？
陈大鱼 2017-03-05
- 打赏
- 举报
如果全部都能配对这个语句比较简洁，也能达到要求[/quote]
中国的婚配制度不允许这样搞~~[/quote]因为用的是full join，不能配对的会空在那儿，不影响的。[/quote]
是的，但俩男俩女能配出4对来
宋哥 2017-03-04
- 打赏
- 举报
如果全部都能配对这个语句比较简洁，也能达到要求[/quote]
中国的婚配制度不允许这样搞~~[/quote]因为用的是full join，不能配对的会空在那儿，不影响的。
陈大鱼 2017-03-03
- 打赏
- 举报
如果全部都能配对这个语句比较简洁，也能达到要求[/quote]
中国的婚配制度不允许这样搞~~
陈大鱼 2017-03-02
- 打赏
- 举报
不要沉啊，答不上也可以探讨下么。讲讲思路
深蓝冰心 2017-03-02
- 打赏
- 举报
如果全部都能配对这个语句比较简洁，也能达到要求
陈大鱼 2017-03-01
- 打赏
- 举报
请看六楼~~
陈大鱼 2017-03-01
- 打赏
- 举报
大家你好，加一个列确实可以解决上面的需求，但我还想增加条件，还望大家帮忙解惑
1. 男女表增加两列 id age timestamp city job 增加了城市和 工作
2. 匹配的时候，我不光按timestamp降序，我还想让他们的城市相同，工作不相同作为匹配条件。也就是城市相同，工作不同的男女，按照timestamp降序进行1对1关联
还请大神求助，答完结帖~
xiaoguanzhao 2017-03-01
- 打赏
- 举报
配个对：
with a as
(select '马大哈' id, 20 age, '20140102' ts
from dual
union all
select '西门庆', 25, '20150602'
from dual
union all
select '武大', 28, '20110223'
from dual),
b as
(select '金莲' id, 30 age, '20141122' ts
from dual
union all
select '马容', 22, '20150302'
from dual
union all
select '小白菜', 43, '20170123'
from dual)
select * from (
select rank() over( order by ts desc ) num,id,age,ts from a) t
left join (
select rank() over( order by ts desc ) num,id,age,ts from b) t1
on t.num=t1.num;
js14982 2017-03-01
- 打赏
- 举报
select a.*,b.*
from (select t.*,t.rownum rm from a t) a,
(select t.*,t.rownum rm from b t) b
where arm=b.rm;
嘚嗒搬运工 2017-03-01
- 打赏
- 举报
强势围观，给我也配一个~~~
卖水果的net 2017-03-01
- 打赏
- 举报
-- 借 1# 数据，凑个人数
SQL>
SQL> with a as
2 (select 1 id, 20 age, '20140102' ts
3 from dual
4 union all
5 select 2, 25, '20150602'
6 from dual
7 union all
8 select 3, 28, '20110223'
9 from dual),
10 b as
11 (select 1 id, 30 age, '20141122' ts
12 from dual
13 union all
14 select 2, 22, '20150302'
15 from dual
16 union all
17 select 3, 43, '20170123'
18 from dual)
19 select
20 rm,
21 max(decode(tp,1,id)) id_a,
22 max(decode(tp,2,id)) id_b,
23 max(decode(tp,1,age)) age_a,
24 max(decode(tp,2,age)) age_b,
25 max(decode(tp,1,ts)) ts_a,
26 max(decode(tp,2,ts)) ts_b
27 from (
28 select id, age, ts, ROW_NUMBER() over(order by ts desc) rm,1 tp from a
29 union all
30 select id, age, ts, ROW_NUMBER() over(order by ts desc) rm,2 tp from b
31 )
32 group by rm;
RM ID_A ID_B AGE_A AGE_B TS_A TS_B
---------- ---------- ---------- ---------- ---------- -------- --------
1 2 3 25 43 20150602 20170123
2 1 2 20 22 20140102 20150302
3 3 1 28 30 20110223 20141122
SQL>
jdsnhan 2017-03-01
- 打赏
- 举报
婚介？ 非诚勿扰？
with a as
(select 1 id, 20 age, '20140102' ts
from dual
union all
select 2, 25, '20150602'
from dual
union all
select 3, 28, '20110223'
from dual),
b as
(select 1 id, 30 age, '20141122' ts
from dual
union all
select 2, 22, '20150302'
from dual
union all
select 3, 43, '20170123'
from dual)
select aa.id, aa.age, bb.id, bb.age, aa.ts, bb.ts
from (select id, age, ts, ROW_NUMBER() over(order by ts desc) rm from a) aa,
(select id, age, ts, ROW_NUMBER() over(order by ts desc) rm from b) bb
where aa.rm = bb.rm
宋哥 2017-03-01
- 打赏
- 举报
select aa.*,bb.* from
(select a.* from a order by city,ts) aa
full join
(select b.* from b order by city,ts) bb on aa.city=bb.city陈大鱼 2017-03-01
- 打赏
- 举报
那就匹配时间降序前两个 第三个男的光棍
js14982 2017-03-01
- 打赏
- 举报
那如果数量不匹配的呢？比如a工作有3个男的，b工作只有2个女的
