--三、按name分组取第一次出现的行所在的数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
SELECT *,IDENTITY(INT,1,1) IDD INTO #T FROM TBSELECT * FROM #T T WHERE IDD=(SELECT MIN(IDD) FROM #T WHERE ID=T.ID) AND flag=1
SELECT *,IDENTITY(INT,1,1) IDD INTO #T FROM TB会和原表的顺序一样不一样?
2000里面貌似加ORDER BY时不保证顺序滴,
select * from [table] t where flag=(select top 1 flag from [table] where id=t.id)
--方法一 select * from [table] t where flag=(select top 1 flag from [table] where id=t.id)--方法二 with cte as (select TID=row_number()over(PARTITION BY ID ORDER BY getdate()),* from [table]) select ID,flag from cte where TID=1
--更正,上面为取第一个flag的数据 --下面为取第一个flag=1with cte as (select TID=row_number()over(order by getdate()),* from [table]) select ID,flag from cte t where TID=(select MIN(TID) from cte where ID=T.ID and flag=1 )
SELECT * FROM (SELECT * FROM tb GROUP BY ID) AS a #按照ID分组查询,显示的就是分组的第一条数据 WHERE flag=1 #把查询出来的数据作为一个表根据flag=1查询
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
2000里面貌似加ORDER BY时不保证顺序滴,
select * from [table] t
where flag=(select top 1 flag from [table] where id=t.id)
--方法一
select * from [table] t
where flag=(select top 1 flag from [table] where id=t.id)--方法二
with cte as
(select TID=row_number()over(PARTITION BY ID ORDER BY getdate()),*
from [table])
select ID,flag from cte where TID=1
--更正,上面为取第一个flag的数据
--下面为取第一个flag=1with cte as
(select TID=row_number()over(order by getdate()),* from [table])
select ID,flag from cte t
where TID=(select MIN(TID) from cte where ID=T.ID and flag=1 )
(SELECT * FROM tb GROUP BY ID) AS a #按照ID分组查询,显示的就是分组的第一条数据
WHERE flag=1 #把查询出来的数据作为一个表根据flag=1查询