求复杂sql 语句

[hotheartlewis]

就一个表table1
nid, sid, bank
c1为键,同一个sid可能对应多个bank,其中bank还有可能为空.
求按照sid排列的所有第一个bank值非空的(如该值都为空,则返回sid最小的一条记录).
e.g.:
nid, sid, bank
1,1,NULL
2,2,1
3,2,3
4,1,5
5,3,NULL
6,1,2
7,2,NULL
返回:
2,2,1
4,1,5
5,3,null
就是说返回结果集的sid不重复,同时满足上面的条件.

[lff642]

SID不重复,还有其他规则吗?

[hery2002]

-->生成测试数据
 
declare @tb table([nid] int,[sid] int,[bank] nvarchar(1))
Insert @tb
select 1,1,null union all
select 2,2,N'1' union all
select 3,2,N'3' union all
select 4,1,N'5' union all
select 5,3,N'0' union all
select 6,1,N'2' union all
select 7,2,N'0'
Select * from @tb t
where not exists (select 1 from @tb where [sid] =t.[sid] and [nid]<t.[nid] and bank is not null)  and bank is not null
order by sid
/*
nid         sid         bank
----------- ----------- ----
4           1           5
2           2           1
5           3           0
*/

[Herb2]

select * from tb a where nid = (select top 1 nid from tb where sid=a.sid order by insull(bank,999999),sid)

[lff642]

大师.好像有问题哦.

[hery2002]

[Quote=引用 4 楼 lff642 的回复:]
大师.好像有问题哦.
[/Quote]
有什么问题?

[lff642]

create table tb (nid int , sid int, bank int)

insert into tb select 1,1,NULL 
insert into tb select 2,2,1 
insert into tb select 3,2,3 
insert into tb select 4,1,5 
insert into tb select 5,3,NULL 
insert into tb select 6,1,2 
insert into tb select 7,2,NULL

--不知是不是我的测试数据有问题.我感觉好像都有错.

[lff642]

[Quote=引用 5 楼 hery2002 的回复:]
引用 4 楼 lff642 的回复:
大师.好像有问题哦.

有什么问题?
[/Quote]

你是使用0来代替NULL

[Herb2]

select * FROM TB A WHERE NID = (SELECT  TOP 1 NID FROM TB WHERE sid =a.sid where bank is not null)
union
select * from tb a where nid = (select top 1 nid from tb where sid = a.sid order by sid)
                        and not exists(select 1 from tb where sid= a.sid and bank is not null)

[octwind]

select * from tb t where bank is not null and  not exists(select 1 from tb where sid=t.sid and bank<t.bank) order by sid

[lff642]

[Quote=引用 8 楼 Herb2 的回复:]
SQL codeselect * FROM TB A WHERE NID = (SELECT TOP 1 NID FROM TB WHERE sid =a.sid where bank is not null)
union
select * from tb a where nid = (select top 1 nid from tb where sid = a.sid order by sid)
and not exists(select 1 from tb where sid= a.sid and bank is not null)
[/Quote]

(SELECT TOP 1 NID FROM TB WHERE sid =a.sid where bank is not null)这里要改成
-->(SELECT TOP 1 NID FROM TB WHERE sid =a.sid and bank is not null)

[lff642]

(select top 1 nid from tb where sid = a.sid order by sid)
-->
这里使用order by 有问题吧.

[Herb2]

set nocount on
create table tb (nid int , sid int, bank int)

insert into tb select 1,1,NULL 
insert into tb select 2,2,1 
insert into tb select 3,2,3 
insert into tb select 4,1,5 
insert into tb select 5,3,NULL 
insert into tb select 6,1,2 
insert into tb select 7,2,NULL
go
select * from tb a 
where nid =(select top 1 nid from tb where sid = a.sid order by case when bank is null then 9999+sid else 9999 end,nid)
go
drop table tb
/*
nid         sid         bank        
----------- ----------- ----------- 
          2           2           1 
          4           1           5 
          5           3 NULL

*/

[lff642]

--修改:

create table tb (nid int , sid int, bank int)

insert into tb select 1,1,NULL 
insert into tb select 2,2,1 
insert into tb select 3,2,3 
insert into tb select 4,1,5 
insert into tb select 5,3,NULL 
insert into tb select 6,1,2 
insert into tb select 7,2,NULL



select * FROM TB A WHERE NID = (SELECT  TOP 1 NID FROM TB WHERE sid =a.sid and  bank is not null)
union
select * from tb a where nid = (select top 1 nid from tb where sid = a.sid )
                        and not exists(select 1 from tb where sid= a.sid and bank is not null)

drop table tb

[lff642]

12楼的强啊.学习.

[中国风]

declare @T table([nid] int,[sid] int,[bank] int)
Insert @T
select 1,1,null union all
select 2,2,1 union all
select 3,2,3 union all
select 4,1,5 union all
select 5,3,null union all
select 6,1,2 union all
select 7,2,null
 
Select 
	* 
from 
	@T t
where
	[nid]=(select top 1 [nid] from @T where sid=t.sid order by case when [bank] is not null then 0 else 1 end asc,[nid] )

(7 個資料列受到影響)
nid         sid         bank
----------- ----------- -----------
2           2           1
4           1           5
5           3           NULL

(3 個資料列受到影響)