急求SQL语句~~ 大伙帮忙

Question

简化实际情况,看以下关系
------------------------------
表 cardstat
cardno         name          active
1              张三          1
1              李四          0
2              王五          0
2              赵六          0
------------------------------------
0表示已经不再用此卡,1表示在用此卡,一张卡只有0状态,是表示卡已经退了,但还没有新人用的情况
active=1,只会对应一个人,而等于0,则可能对应多人--------------------------------------
表 persons
id      name
1       张三
2       李四
3       王五
4       赵六
--------------------------------------需求如下:
原语句:select * from cardstat where active=1 and name in (select name from persons);现在需求有变动,如果卡有人在用即active=1,则选出这个人,如果这个卡没有人在用,则从active=0的用户,选出ID值较高的一个来,放入
结果集根据以上的表内容,期望的结果如下:cardno         name          active
1              张三          1
2              赵六          0
求这条语句该怎么写,先谢谢了!@!!

Answer 1

SELECT cardno,name,active
  FROM (SELECT c.*, row_number() over(PARTITION BY c.cardno ORDER BY c.active DESC, p.id DESC) rn
          FROM cardstat c, persons p
         WHERE c.name = p.name)
 WHERE rn = 1;

Answer 2

create view cp 
 as select c.cardno cardno, c.name cname,c.active cactive, p.id pid
    from cardstat c,  persons p 
    where c.name = p.name and c.name not in (
                                          select name from cardstat where cardno in 
                                          (select cardno from cardstat active=1)
                                             );select * from cardstat where active=1 
union
select * from cardstat where name in ( 
select name from persons where id in (
select max(pid) from cp group by  cardno));

Answer 3

表 cardstat
cardno name active表 persons
id nameselect id,name,active
(select b.id,a.name,a.active
from cardstat a,persons b
where a.name=b.name and a.active=1
union
select b.id,a.name,a.active
from cardstat a,persons b
where a.name=b.name and b.id in(select min(b.id)
from cardstat t,persons s
where t.name=s.name and t.active=0))
--分析函数
select id,name,active
(select b.id,a.name,a.active,row_number() over(partition by a.cardno order by a.active desc ,b.id desc) rn
from cardstat a,persons b)=1
where rn

Answer 4

--分析函数
select id,name,active
(select b.id,a.name,a.active,row_number() over(partition by a.cardno order by a.active desc ,b.id desc) rn
from cardstat a,persons b)
where rn=1--位置搞错了