求救：只用sql语句能解决这个要求吗？

Question

本帖最后由 chlhp 于 2010-06-09 00:53:10 编辑

Answer 1

思想，把那些相同id, address 为A的最大时间比id相同，address为B的最小时间还要小的找出来，同时把只在一个地址里出现的记录找出来，然后进行排序即可。下边是测试代码，供参考：
mysql> create table t3(id char(4), time datetime, address char(2));
Query OK, 0 rows affected (0.00 sec)mysql> insert into t3 values('0202', '2010-6-2 8:37:02', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0058', '2010-6-2 8:37:02', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0084', '2010-6-2 8:37:01', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('B300', '2010-6-2 8:37:02', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0058', '2010-6-2 8:37:29', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0084', '2010-6-2 8:37:29', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('B300', '2010-6-2 8:37:06', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('8AF6', '2010-6-2 8:37:16', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0202', '2010-6-2 8:37:57', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0058', '2010-6-2 8:37:57', 'B');
Query OK, 1 row affected (0.02 sec)mysql> insert into t3 values('0084', '2010-6-2 8:37:57', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('8AF6', '2010-6-2 8:37:57', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('B300', '2010-6-2 8:37:56', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('8AF6', '2010-6-2 8:38:24', 'B');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('B300', '2010-6-2 8:38:07', 'A');
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 values('0202', '2010-6-2 8:38:24', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0009', '2010-6-2 8:38:24', 'A');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 values('0058', '2010-6-2 8:38:24', 'A');
Query OK, 1 row affected (0.00 sec)mysql> select id, time, address from t3 where id in
    ->   (
    ->     select a.id from (select id, max(time) t, address from t3 where address='A' group by id) a , (select id, min(time) t, address from t3 where address='B' group by id) b where a.id = b.id and a.t<b.t
    ->     union
    ->     select distinct id from t3 a where not exists (select * from t3 b where b.address!=a.address and b.id = a.id)
    ->   )
    -> order by id, address, time;
+------+---------------------+---------+
| id   | time                | address |
+------+---------------------+---------+
| 0009 | 2010-06-02 08:38:24 | A       |
| 0084 | 2010-06-02 08:37:01 | A       |
| 0084 | 2010-06-02 08:37:29 | B       |
| 0084 | 2010-06-02 08:37:57 | B       |
| 8AF6 | 2010-06-02 08:37:16 | B       |
| 8AF6 | 2010-06-02 08:37:57 | B       |
| 8AF6 | 2010-06-02 08:38:24 | B       |
+------+---------------------+---------+
7 rows in set (0.00 sec)mysql>

Answer 2

不好意思，看错了标题，下边是oracle的测试代码，供参考：
SQL> create table t3(id char(4), time date, address char(2));表已创建。SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';会话已更改。SQL> insert into t3 values('0202', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:01', 'A');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:29', 'B');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:29', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:06', 'A');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:37:16', 'B');已创建 1 行。SQL> insert into t3 values('0202', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:56', 'B');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:38:24', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:38:07', 'A');已创建 1 行。SQL> insert into t3 values('0202', '2010-6-2 8:38:24', 'A');已创建 1 行。SQL> insert into t3 values('0009', '2010-6-2 8:38:24', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:38:24', 'A');已创建 1 行。SQL> select id, time, address from t3 where id in
  2    (
  3      select a.id from (select id, max(time) t from t3 where address='A' group by id) a , (select id, min(time) t from t3 where address='B' group by id) b where a.id = b.id and a.t<b.t
  4      union
  5      select distinct id from t3 a where not exists (select * from t3 b where b.address!=a.address and b.id = a.id)
  6    )
  7  order by id, address, time;ID   TIME                AD
---- ------------------- --
0009 2010-06-02 08:38:24 A
0084 2010-06-02 08:37:01 A
0084 2010-06-02 08:37:29 B
0084 2010-06-02 08:37:57 B
8AF6 2010-06-02 08:37:16 B
8AF6 2010-06-02 08:37:57 B
8AF6 2010-06-02 08:38:24 B已选择7行。SQL>

Answer 3

iihero辛苦了！谢谢先！但是，例如id为B300的记录有四条：B300 2010-6-2 8:37:02 A
B300 2010-6-2 8:37:06 A
B300 2010-6-2 8:37:56 B
B300 2010-6-2 8:38:07 A因为存在
B300 2010-6-2 8:37:06 A
B300 2010-6-2 8:37:56 B所以 B300 也应该算作是符合要求的一条id，但你的结果好像没有列出来呀？！

Answer 4

select id,time,address from (
select id,time,lead(time) over(partition by id order by id) time1,
address,lead(address) over(partition by id order by id) address1
from(
select *   from testA order by id,time,address
)
) where address = 'A' and address1 = 'B'

Answer 5

多谢六楼提醒，现在已有人回复，改不了帖子了，下次我会注意的！想要找出以下结果：0058   2010-6-2  8:37:02   A 
0058   2010-6-2  8:37:29   B0084   2010-6-2  8:37:01   A 
0084   2010-6-2  8:37:29   B0202   2010-6-2  8:37:02   A
0202   2010-6-2  8:37:57   BB300   2010-6-2  8:37:06   A
B300   2010-6-2  8:37:56   B 

Answer 6

SQL> select id,time,address from
  2  (
  3  select * from
  4  (
  5  select t3.*,
  6  lag(address) over(partition by id order by time) b_address,
  7  lag(time) over(partition by id order by time) b_time
  8  from t3
  9  )
 10  where address = 'B ' and b_address = 'A '
 11  )
 12  union all
 13  select id,b_time,b_address from
 14  (
 15  select * from
 16  (
 17  select t3.*,
 18  lag(address) over(partition by id order by time) b_address,
 19  lag(time) over(partition by id order by time) b_time
 20  from t3
 21  )
 22  where address = 'B ' and b_address = 'A '
 23  )
 24  order by id,time
 25  ;
 
ID   TIME                 ADDRESS
---- -------------------- -------
0058 2010-6-2 8:37:02     A
0058 2010-6-2 8:37:29     B
0084 2010-6-2 8:37:01     A
0084 2010-6-2 8:37:29     B
0202 2010-6-2 8:37:02     A
0202 2010-6-2 8:37:57     B
B300 2010-6-2 8:37:06     A
B300 2010-6-2 8:37:56     B
 
8 rows selected
 
SQL> select * from t3;
 
ID   TIME                 ADDRESS
---- -------------------- -------
0202 2010-6-2 8:37:02     A
0058 2010-6-2 8:37:02     A
0084 2010-6-2 8:37:01     A
B300 2010-6-2 8:37:02     A
0058 2010-6-2 8:37:29     B
0084 2010-6-2 8:37:29     B
B300 2010-6-2 8:37:06     A
8AF6 2010-6-2 8:37:16     B
0202 2010-6-2 8:37:57     B
0058 2010-6-2 8:37:57     B
0084 2010-6-2 8:37:57     B
8AF6 2010-6-2 8:37:57     B
B300 2010-6-2 8:37:56     B
8AF6 2010-6-2 8:38:24     B
B300 2010-6-2 8:38:07     A
0202 2010-6-2 8:38:24     A
0009 2010-6-2 8:38:24     A
0058 2010-6-2 8:38:24     A
 
18 rows selected

Answer 7

select id,time,address from
(
select * from
(
select t3.*,
lag(address) over(partition by id order by time) b_address,
lag(time) over(partition by id order by time) b_time
from t3
)
where address = 'B' and b_address = 'A'
)
union all
select id,b_time,b_address from
(
select * from
(
select t3.*,
lag(address) over(partition by id order by time) b_address,
lag(time) over(partition by id order by time) b_time
from t3
)
where address = 'B' and b_address = 'A'
)
order by id,time

Answer 8

由于存在B300 2010-6-2 8:38:07 A这个时间比B300 2010-6-2 8:37:56 B大啊。所以应该滤掉，对吗？

Answer 9

回复10楼:
    
    不能过滤掉，因为是按时间从早到晚（升序）的顺序计算，只要出现了配对情况，就算是一对符合条件的记（算是就近原则吧，先关注和前面记录的匹配情况）。    B300 2010-6-2 8:37:02 A
    B300 2010-6-2 8:37:06 A
    B300 2010-6-2 8:37:56 B
    B300 2010-6-2 8:38:07 A

Answer 10

没有验证的，但逻辑应该是这样
SELECT
    a.id
FROM
    (
        SELECT
            id,
            MAX(SUBSTR(TIME,1,10)) ma
        FROM
            table_name
        WHERE
            addree = 'B'
        GROUP BY
            id
    ) AS b,
    (
        SELECT
            id,
            MIN(SUBSTR(TIME,1,10)) mi
        FROM
            table_name
        WHERE
            addree='A'
        GROUP BY
            id
    )AS a
WHERE
    a.id=b.id
AND b.ma>=a.mi;

Answer 11

谢谢大家，感谢wh62592855，这几天有事没来得及结贴，sorry！  
9、10楼正解！