如何解决这个查询问题？
在一个表:t_bbb
id uid name userid lnumber status
1 011 hhhhhh aaa 1 1
2 011 hhhhhh bbb 2 1
3 011 hhhhhh ccc 2 null
4 011 hhhhhh ddd 3 null
5 021 mmmmmm bbb 1 1
6 021 mmmmmm aaa 2 1
7 021 mmmmmm eee 3 1
8 021 mmmmmm ddd 3 1
9 021 mmmmmm ccc 4 null
10 011 vvvvvv ccc 1 null
11 011 vvvvvv bbb 2 null
12 011 vvvvvv aaa 2 null
13 011 vvvvvv ddd 3 null
如何列出如userid=ccc,status is null and lnumber-1的记录里uid相同 and tatus=1的全部记录.则结果应该如下：
3 011 hhhhhh ccc 2 null
9 021 mmmmmm ccc 4 null
10 011 vvvvvv ccc 1 null
[解决办法]
CREATE table t
(id int,uid varchar(10), name varchar(10), userid varchar(10), lnumber int, status int)
insert into t
select 1 , '011 ', 'hhhhhh ', 'aaa ', 1 , 1 union all
select 2 , '011 ', 'hhhhhh ', 'bbb ', 2 , 1 union all
select 3 , '011 ', 'hhhhhh ', 'ccc ', 2 , null union all
select 4 , '011 ', 'hhhhhh ', 'ddd ', 3 , null union all
select 5 , '021 ', 'mmmmmm ', 'bbb ', 1 , 1 union all
select 6 , '021 ', 'mmmmmm ', 'aaa ', 2 , 1 union all
select 7 , '021 ', 'mmmmmm ', 'eee ', 3 , 1 union all
select 8 , '021 ', 'mmmmmm ', 'ddd ', 3 , 1 union all
select 9 , '021 ', 'mmmmmm ', 'ccc ', 4 , null union all
select 10, '031 ', 'vvvvvv ', 'ccc ', 1 , null union all
select 11, '031 ', 'vvvvvv ', 'bbb ', 2 , null union all
select 12, '031 ', 'vvvvvv ', 'aaa ', 2 , null union all
select 13, '031 ', 'vvvvvv ', 'ddd ' , 3 , null
select*
from t
where id
in(
select min(id)as id
from t
where status is null
group by uid,name
)
id uid name userid lnumber status
----------- ---------- ---------- ---------- ----------- -----------
3 011 hhhhhh ccc 2 NULL
9 021 mmmmmm ccc 4 NULL
10 031 vvvvvv ccc 1 NULL
(3 row(s) affected)
[解决办法]
declare @t_bbb table(id int,uid varchar(3),name varchar(100),userid varchar(100),lnumber int,status int)
insert @t_bbb
select '1 ', '011 ', 'hhhhhh ', 'aaa ', '1 ', '1 ' union all
select '2 ', '011 ', 'hhhhhh ', 'bbb ', '2 ', '1 ' union all
select '3 ', '011 ', 'hhhhhh ', 'ccc ', '2 ',null union all
select '4 ', '011 ', 'hhhhhh ', 'ddd ', '3 ',null union all
select '5 ', '021 ', 'mmmmmm ', 'bbb ', '1 ', '1 ' union all
select '6 ', '021 ', 'mmmmmm ', 'aaa ', '2 ', '1 ' union all
select '7 ', '021 ', 'mmmmmm ', 'eee ', '3 ', '1 ' union all
select '8 ', '021 ', 'mmmmmm ', 'ddd ', '3 ', '1 ' union all
select '9 ', '021 ', 'mmmmmm ', 'ccc ', '4 ',null union all
select '10 ', '031 ', 'vvvvvv ', 'ccc ', '1 ',null union all
select '11 ', '031 ', 'vvvvvv ', 'bbb ', '2 ',null union all
select '12 ', '031 ', 'vvvvvv ', 'aaa ', '2 ',null union all
select '13 ', '031 ', 'vvvvvv ', 'ddd ', '3 ',null
select * from @t_bbb where userid= 'ccc ' and status is null-- and lnumber=1
/*
3011hhhhhhccc2NULL
9021mmmmmmccc4NULL
10031vvvvvvccc1NULL
*/
-- or
select * from @t_bbb where id in (select distinct id from @t_bbb where userid= 'ccc ' and status is null)
/*
3011hhhhhhccc2NULL
9021mmmmmmccc4NULL
10031vvvvvvccc1NULL
*/
