帮忙看一下，这样的查询语句（SQL）怎么写？
帮忙看一下，这样的查询语句（SQL）怎么写？
表里有记录如下：
nIndex State LanguageID
1 2 10
1 3 11
1 4 12
2 3 10
2 3 11
3 1 10
3 2 11
4 2 10
4 2 11
4 2 12
我想查出nIndex相同，State也相同的记录。查询的结果应该是：
2 3 10
2 3 11
4 2 10
4 2 11
4 2 12
这个查询语句怎么写啊？
[解决办法]
select *
from t1 a ,(select n_index,state,count(1) from t1 group by n_index,state having count(1)> 1 ,) b
where a.nIndex=b.n_index and a.state = b.state
[解决办法]
select distinct a.* from tb a inner join tb b on a.nIndex=b.nIndex and a.State =b.State and a.LanguageID <> b.LanguageID
[解决办法]
又来晚....
create table test(
nIndex char(1),
state char(1),
languageId char(2)
)
insert into test
select '1 ', '2 ', '10 ' union all
select '1 ', '3 ', '11 ' union all
select '2 ', '3 ', '10 ' union all
select '2 ', '3 ', '11 ' union all
select '3 ', '4 ', '10 ' union all
select '3 ', '4 ', '11 ' union all
select '4 ', '5 ', '10 '
select b.*,a.languageId from
test as a inner join (
select nIndex,state from test
group by nIndex,state
having count(*)> 1
) as b
on a.nIndex=b.nIndex and a.state=b.state
drop table test
[解决办法]
create table t1(nIndex int, State int, LanguageID int)
insert t1
select 1 , 2 , 10 union all
select 1 , 3 , 11 union all
select 1 , 4 , 12 union all
select 2 , 3 , 10 union all
select 2 , 3 , 11 union all
select 3 , 1 , 10 union all
select 3 , 2 , 11 union all
select 4 , 2 , 10 union all
select 4 , 2 , 11 union all
select 4 , 2 , 12
select a.*
from t1 a ,(select nIndex,state from t1 group by nIndex,state having count(1)> 1 ) b
where a.nIndex=b.nIndex and a.state = b.state
[解决办法]
--借用楼上记录
declare @test table (
nIndex char(1),
state char(1),
languageId char(2)
)
insert into @test
select '1 ', '2 ', '10 ' union all
select '1 ', '3 ', '11 ' union all
select '2 ', '3 ', '10 ' union all
select '2 ', '3 ', '11 ' union all
select '3 ', '4 ', '10 ' union all
select '3 ', '4 ', '11 ' union all
select '4 ', '5 ', '10 '
select * from @test
where binary_checksum(nIndex,state) in
(select binary_checksum(nIndex,state)from @test
group by binary_checksum(nIndex,state) having count(1)> 1)
(7 行受影响)
nIndex state languageId
------ ----- ----------
2 3 10
2 3 11
3 4 10
3 4 11
(4 行受影响)
[解决办法]
declare @test table (
nIndex char(1),
state char(1),
languageId char(2)
)
insert into @test
select '1 ', '2 ', '10 ' union all
select '1 ', '3 ', '11 ' union all
select '2 ', '3 ', '10 ' union all
select '2 ', '3 ', '11 ' union all
select '3 ', '4 ', '10 ' union all
select '3 ', '4 ', '11 ' union all
select '4 ', '5 ', '10 '
select * from @test t
where (select count(1) from @test where nIndex=t.nIndex and state=t.state )> 1
(7 行受影响)
nIndex state languageId
------ ----- ----------
2 3 10
2 3 11
3 4 10
3 4 11
(4 行受影响)
