当前位置: 代码迷 >> Sql Server >> 简单SQL查询语句,该怎么解决
  详细解决方案

简单SQL查询语句,该怎么解决

热度:75   发布时间:2016-04-27 21:43:40.0
简单SQL查询语句
三个列A,B,C
要查
同一A,但B不同的数据
比如表
A         B           C
1         123     ad
1         123     cd
2         684     ab
2         234     ae
3         456     ge
4         678     aw
4         789     gw

结果出来就是
A         B         C
2         684     ab
2         234     ae
4         678     aw
4         789     gw


求SQL查询语句

------解决方案--------------------
--理解錯了, 改改

create table T(A int, B int, C varchar(10))
insert T select 1, 123, 'ad '
union all select 1, 123, 'cd '
union all select 2, 684, 'ab '
union all select 2, 234, 'ae '
union all select 3, 456, 'ge '
union all select 4, 678, 'aw '
union all select 4, 789, 'gw '

select * from T as tmp
where (select count(*) from T where A=tmp.A and B <> tmp.B)=1

--result
A B C
----------- ----------- ----------
2 684 ab
2 234 ae
4 678 aw
4 789 gw

(4 row(s) affected)
------解决方案--------------------
select * from T as tmp
where (select count(*) from T where A=tmp.A and b <> tmp.b)> 1
------解决方案--------------------
select a.* from tb a
inner join (select A, Num from tb group by A having count(distinct B)> 1)b
on a.A=b.A
------解决方案--------------------
declare @t table(A int, B int, C varchar(10))
insert @t select 1,123, 'ad '
union all select 1,123, 'cd '
union all select 2,684, 'ab '
union all select 2,234, 'ae '
union all select 3,456, 'ge '
union all select 4,678, 'aw '
union all select 4,789, 'gw '

select * from @t as t where A=(select A from @t where A=t.A and B <> t.B)
------解决方案--------------------
2 684 ab
2 234 ae
2 234 ac
这样的情况你们怎么算
------解决方案--------------------
marco08(天道酬勤) 兄:
分两种理解:
1:A为偶数时
select * from T where (cast(A as smallint) % 2=0)

2:B相等时,除去
select * from T as tmp
where (select count(*) from T where A=tmp.A and B <> tmp.B)=1
请问LZ你是要那种情况的!
  相关解决方案