select * from table1 A where a in (select top 1 a from table1 B where B.b=A.b order by a desc) order by a
select max(a), b, max(c) from table1 group by b
就是b值相同的时候取c=2的那条记录.c只可以选1或2
to CrazyFor(烟草) 当B相等,没有C等于2,就取那条记录.这个查询,就是为了去掉一些纪录. 就是b相同,并且c有等于1的也有等于2的,去掉c等于1的那一条.
select * from 表 where id not in (select b from 表 where b=c)
SELECT * FROM 表1 WHERE a+b+c NOT IN (SELECT a,b,c FROM 表1 WHERE C='1' group by b having count(*)>1)
SELECT * FROM 表1 WHERE b NOT IN (SELECT b FROM 表1 WHERE C='1' group by b having count(*)>1)
经过测试hjhing的答案可行。 Server: Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.这是(吴)执行后产生的错误 大力的执行结果是: 4 4 1 5 5 1 tj_dns的语句可以修改一下 select max(a) as a, b, max(c)as c from a group by b
按b相同,并且c有等于1的也有等于2的,去掉c等于1的那一条.的条件!declare @a table (a int,b int,c int) insert @a values(1,1,1) insert @a values(2,2,1) insert @a values(3,2,2) insert @a values(4,4,1) insert @a values(5,5,1) insert @a values(6,5,3)select * from @a where a not in (select a from @a where b in (select b from @a where b in(select b from @a group by b having count(b)>1) and c=1) and b in (select b from @a where b in(select b from @a group by b having count(b)>1) and c=2) and c=1)
主要看b和c,b值相等,当c=2的时候,取322,去掉221.
是不是就是b值不能相同
假如b 值相同就取最大一个c?
from table1 A where a in (select top 1 a from table1 B where B.b=A.b order by a desc)
order by a
当B相等,没有C等于2,就取那条记录.这个查询,就是为了去掉一些纪录.
就是b相同,并且c有等于1的也有等于2的,去掉c等于1的那一条.
(SELECT a,b,c FROM 表1 WHERE C='1' group by b having count(*)>1)
(SELECT b FROM 表1 WHERE C='1' group by b having count(*)>1)
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.这是(吴)执行后产生的错误
大力的执行结果是:
4 4 1
5 5 1
tj_dns的语句可以修改一下
select max(a) as a, b, max(c)as c from a group by b
insert @a values(1,1,1)
insert @a values(2,2,1)
insert @a values(3,2,2)
insert @a values(4,4,1)
insert @a values(5,5,1)
insert @a values(6,5,3)select * from @a where a not in (select a from @a where
b in (select b from @a where b in(select b from @a group by b having count(b)>1) and c=1) and
b in (select b from @a where b in(select b from @a group by b having count(b)>1) and c=2) and c=1)