sqlserver 2005 中
CREATE TABLE [dbo].[mian](
[id] [int] NOT NULL,
[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[age] [int] NULL,
[state] [bit] NOT NULL
)
--执行2遍,则共8条记录
insert into mian values(1,'st',27,1)
insert into mian values(1,'tj',28,0)
insert into mian values(2,'2st',45,0)
insert into mian values(2,'2tj',88,0)
1 st 27 1
1 tj 28 0
2 2st 45 0
2 2tj 88 0
1 st 27 1
1 tj 28 0
2 2st 45 0
2 2tj 88 0
执行id有相同,执行有state有1的就取出1的,没有的就取出0中的任何一条
select * from (select * , row_number() over(partition by id order by state desc) as sequence
from mian ) a where sequence =1
--where 前要有个表名,^-^
oracle中要如下修改:
select * from (select t.* , row_number() over(partition by t.id order by t.state desc) as sequence
from mian t ) a where sequence =1
oracle 中要加个别名t,与sqlserver2005中不同,不知道为什么。