双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

跪求SQL

2017-02-28 11:19:00 707 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

陈大鱼墨衍会员 · AI 创作全网分发关注 + 加入社区

SQL 别名和sql连接通过使用 SQL，可以为表名称或列名称指定别名。阅读详情

需求
两张表 A表有10个20岁的男人字段 id age timestamp
B表10个20岁女人字段 id age timestamp

如何用SQL将10个男人和10个女人一一配对，可以按时间降序配对，关键是要11配对
求大神！！！

...全文

基于FPGA低频方波测量-频率与占空比基于FPGA低频方波测量-频率与占空比阅读详情

陈大鱼

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

引用 14 楼 iamlaosong 的回复:

[quote=引用 13 楼 liarboss 的回复:] [quote=引用 12 楼 qq_28111429 的回复:] [quote=引用 10 楼 iamlaosong 的回复:]
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

如果全部都能配对这个语句比较简洁，也能达到要求[/quote] 中国的婚配制度不允许这样搞~~[/quote]因为用的是full join，不能配对的会空在那儿，不影响的。[/quote] 是的，但俩男俩女能配出4对来

宋哥 2017-03-04

打赏
举报

引用 13 楼 liarboss 的回复:

[quote=引用 12 楼 qq_28111429 的回复:] [quote=引用 10 楼 iamlaosong 的回复:]
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

如果全部都能配对这个语句比较简洁，也能达到要求[/quote] 中国的婚配制度不允许这样搞~~[/quote]因为用的是full join，不能配对的会空在那儿，不影响的。

陈大鱼 2017-03-03

打赏
举报

引用 12 楼 qq_28111429 的回复:

[quote=引用 10 楼 iamlaosong 的回复:]

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

如果全部都能配对这个语句比较简洁，也能达到要求[/quote] 中国的婚配制度不允许这样搞~~

陈大鱼 2017-03-02

打赏
举报

不要沉啊，答不上也可以探讨下么。讲讲思路

深蓝冰心 2017-03-02

打赏
举报

引用 10 楼 iamlaosong 的回复:

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

打赏
举报

引用 1 楼 jdsnhan 的回复:

婚介？非诚勿扰？



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

引用 2 楼 wmxcn2000 的回复:


-- 借 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>

引用 4 楼 js14982 的回复:

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;
凑个热闹，没有排序，按行顺序匹配

引用 5 楼 xiaoguanzhao 的回复:

配个对： 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;

请看六楼~~

陈大鱼 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

打赏
举报

引用 8 楼 js14982 的回复:

那如果数量不匹配的呢？比如a工作有3个男的，b工作只有2个女的

那就匹配时间降序前两个第三个男的光棍

js14982 2017-03-01

打赏
举报

那如果数量不匹配的呢？比如a工作有3个男的，b工作只有2个女的

✦双11狂欢大促✦

跪求SQL

相关推荐

双11狂欢大促