ID userID name
1 1 aaa
2 1 bbb
3 2 ccc
4 1 ddd
5 3 eee想要的显示结果
ID userID name
2 1 bbb
4 1 ddd我想到用Order By 排序,但之后怎么写就不会了
1 1 aaa
2 1 bbb
3 2 ccc
4 1 ddd
5 3 eee想要的显示结果
ID userID name
2 1 bbb
4 1 ddd我想到用Order By 排序,但之后怎么写就不会了
select * from table where name not exists (select min(name) from table group by userid )
from tb1
where UserID=1 and ID not in (select min(ID) from tb1 where UserID=1)
order by ID
from t
where userID =1 and not exists (select min(ID) from t where userID =1)
谢谢,你少了一个ID,应该是
ID not in (select min(ID) from t where userID =1)
我用exsits 为什么不好用呢?提示附近有语法错误,求解之后马上结贴,非常感谢
(
ID INT IDENTITY,
userID INT,
[name] VARCHAR(10)
)
INSERT #temp
select '1', 'aaa' union all
select '1', 'bbb' union all
select '2', 'ccc' union all
select '1', 'ddd' union all
select '3', 'eee'
GO
--SQL:
SELECT * FROM #temp T
WHERE EXISTS(SELECT 1 FROM #temp WHERE userID = T.userID AND ID < T.ID)
OR (SELECT COUNT(*) FROM #temp WHERE userID = T.userID) = 1 --考虑一下,只有一条记录的是否显示
AND userID = 1
/*
2 1 bbb
4 1 ddd
*/