/*
Limpire:
*/--原始数据:@Test
declare @Test table(keyword int,sourcefield varchar(5),primaryid int)
insert @Test
select 123456,'690$a',1 union all
select 123451,'690$a',1 union all
select 123453,'690$a',2 union all
select 123454,'690$a',2select * from @Test a where keyword=(select top 1 keyword from @Test where primaryid=a.primaryid)/*
结果为
keyword sourcefield primaryid
123456 690$a 1
123453 690$a 2我的想法是按照 keyword 排序 然后按照 primaryid做distinct处理,请问这个语句怎么写
*/
Limpire:
*/--原始数据:@Test
declare @Test table(keyword int,sourcefield varchar(5),primaryid int)
insert @Test
select 123456,'690$a',1 union all
select 123451,'690$a',1 union all
select 123453,'690$a',2 union all
select 123454,'690$a',2select * from @Test a where keyword=(select top 1 keyword from @Test where primaryid=a.primaryid)/*
结果为
keyword sourcefield primaryid
123456 690$a 1
123453 690$a 2我的想法是按照 keyword 排序 然后按照 primaryid做distinct处理,请问这个语句怎么写
*/
insert into tb values('123456', '690$a', 1)
insert into tb values('123451', '690$a', 1)
insert into tb values('123453', '690$a', 2)
insert into tb values('123454', '690$a', 2)
go
select * from tb a where keyword in (select top 1 keyword from tb where primaryid = a.primaryid)
/*
keyword sourcefield primaryid
---------- ----------- -----------
123456 690$a 1
123453 690$a 2
(所影响的行数为 2 行)
*//*
我的想法是按照 keyword 排序 然后按照 primaryid做distinct处理,请问这个语句怎么写
照你的说法,其结果应该为:
keyword sourcefield primaryid
---------- ----------- -----------
123451 690$a 1
123453 690$a 2
或
keyword sourcefield primaryid
---------- ----------- -----------
123456 690$a 1
123454 690$a 2
*/
select * from tb a where keyword in (select min(keyword) from tb group by primaryid)
/*
keyword sourcefield primaryid
---------- ----------- -----------
123451 690$a 1
123453 690$a 2
(所影响的行数为 2 行)
*/
select * from tb a where keyword in (select max(keyword) from tb group by primaryid)
/*
keyword sourcefield primaryid
---------- ----------- -----------
123456 690$a 1
123454 690$a 2
(所影响的行数为 2 行)
*/
drop table tb
where
keyword in (select top 1 keyword from tb where primaryid = a.primaryid)