简单的SQL帅选...

Question

/**************************现在的语句如下******************************/
select Portcode,PortFuName,PortWholeName,servicesid,week
from v_bk_BookingEtdSiCy 
group by Portcode,PortFuName,PortWholeName,servicesid,week
order by Portcode asc/******************************结果**********************************/Portcode PortFuName PortWholeName servicesid week
36         HKG         HongKong 101         11
36         HKG         HongKong 101         12
37         KHH         Kaohsiung 1393         18
37         KHH         Kaohsiung 1393         19
37         KHH         Kaohsiung 1393         20
38         SHA         Shanghai 1186         5
38         SHA         Shanghai 1186         6
38         SHA         Shanghai 1186         7/************************我想要的结果如下*****************************/Portcode PortFuName PortWholeName servicesid week
36         HKG         HongKong 101         11
37         KHH         Kaohsiung 1393         18
38         SHA         Shanghai 1186         5只要PortFuName,PortWholeName有相同的只取一条,其他的字段可以不管,但要select出来

Answer 1

select * from v_bk_BookingEtdSiCy a where week=(select min(week) from week 
v_bk_BookingEtdSiCy where portcode=a.portcode)

Answer 2

select * from v_bk_BookingEtdSiCy a where not exists(select 1 from v_bk_BookingEtdSiCy where PortFuName=a.PortFuName and PortWholeName=a.PortWholeName and servicesid>a.servicesid)

Answer 3

select min(Portcode),PortFuName,PortWholeName,min(servicesid),min(week) 
from v_bk_BookingEtdSiCy 
group by PortFuName,PortWholeName

Answer 4

select Portcode,min(PortFuName),min(PortWholeName),servicesid,week 
from v_bk_BookingEtdSiCy 
group by Portcode,PortFuName,PortWholeName,servicesid,week 
order by Portcode asc 

Answer 5

  select min(portcode),portfuname,portwholename,min(servicesid),min(week) from A group by portfuName,portwholeName

Answer 6

create table #a(code int,name varchar(10),wholename varchar(20),sid int,week int)insert into #a 
select 36,'HKG','HongKong',101,11 union all
select 36,'HKG','HongKong',101,12 union all
select 37,'KHH','Kaohsiung',1393,18 union all
select 37,'KHH','Kaohsiung',1393,19 union all
select 37,'KHH','Kaohsiung',1393,20 union all
select 38,'SHA','Shanghai',1186,5 union all
select 38,'SHA','Shanghai',1186,6 union all
select 38,'SHA','Shanghai',1186,7select max(code),name,wholename,max(sid),max(week) 
from #a 
group by wholename,name
既然不管其他的字段，那么只用
wholename,name分组就可以了

Answer 7

create table Table1
(
Portcode int , PortFuName varchar(40),PortWholeName varchar(40),servicesid int ,week int 
)
insert into table1
select 36         ,N'HKG',N'HongKong', N'101',11 union all 
select 36         ,N'HKG',N'HongKong', N'101',12 union all 
select 37         ,N'KHH',N'Kaohsiung', N'1393',18 union all 
select 37         ,N'KHH',N'Kaohsiung', N'1393',19 union all 
select 37         ,N'KHH',N'Kaohsiung', N'1393',20 union all 
select 38         ,N'SHA',N'Shanghai', N'1186',5 
select * from table1  T1 where T1.week=(select min(week) from table1 T2 where T2.servicesid=T1.servicesid)/*
36 HKG HongKong 101 11
38 SHA Shanghai 1186 5
37 KHH Kaohsiung 1393 18
*/

Answer 8

codeselect min(portcode)as portcode,portfuname,portwholename,min(servicesid)as servicesid,min(week)as week from A group by portfuName,portwholeName会更加完美 ^_^