求一SQL语句(关于不同情况下的查询语句)~~急~
有一个表
id,name,code,Note
1 2 A sfe
2 we B secc
2 we A awef
code列有两种情况,A和B
如果某个记录没有Code 为B的情况,那么就取它的Code为A的Note,如果某个记录有Code为B的情况,比如说id为2的记录,那么这时要取它Code为B时的Note
怎样用SQL语名实现啊?
[解决办法]
----借用ls的数据
create table tb(id int,name varchar(10),code varchar(10),Note varchar(10))
insert into tb values(1, '2 ' , 'A ', 'sfe ')
insert into tb values(2, 'we ', 'B ', 'secc ')
insert into tb values(2, 'we ', 'A ', 'awef ')
go
-------查询语句--------
select * from tb t where not exists(select 1 from tb where id=t.id and code> t.code)
----------------------
drop table tb
/* 结果
id name code Note
----------- ---------- ---------- ----------
1 2 A sfe
2 we B secc
(2 row(s) affected)
*/
[解决办法]
如果code 只有a,b 两种情况
select * from tb where id not in (select id from tb where code = 'B ')
union all
select * from tb where code = 'B '
就等于
select * from tb
[解决办法]
我想这个语句可能更好理解一些吧
select * from tb t where code in (select top 1 code from tb where id=t.id order by code desc)
[解决办法]
晕了.
[解决办法]
create table tb(id int,name varchar(10),priduct varchar(10),code varchar(10),Note varchar(10))
insert into tb values(1, 'w1 ' , 'abs ', 'A ', 'wawa ')
insert into tb values(2, 'w2 ', 'a ', 'A ', 'w2 ')
insert into tb values(2, 'w2 ', 'b ', 'B ', 'w2s2 ')
select * from tb where id not in (select id from tb where code= 'B ')
union
select * from tb where id in (select id from tb where code= 'B ') and code <> 'A '
[解决办法]
select aa.id,aa.name,aa.product,aa.code as code,isnull(bb.note,aa.note) as note from tb aa left join tb bb on aa.id=bb.id and bb.code <> aa.code where aa.code= 'A '
