字段唯一查询
messid(递增/int) name(姓名/nvarchar) mess(留言内容/nvarchar)1 AA **
2 BB **
3 AA **
4 AA **
5 BB **
6 EE **
7 FF **
我想查询出的结果是
1 AA **
2 BB **
6 CC **
7 DD **请高手指教一下应该怎么写这个SQL语句呢(兄弟们希望你的SQL语句测试后在发上来好么 谢谢)
(
select distinct (name) form mess
select distinct(*) from mess
请不要发以上2条类似的sql语句,我测试过,以上两条语句全部不行,
)
messid(递增/int) name(姓名/nvarchar) mess(留言内容/nvarchar)1 AA **
2 BB **
3 AA **
4 AA **
5 BB **
6 EE **
7 FF **
我想查询出的结果是
1 AA **
2 BB **
6 CC **
7 DD **请高手指教一下应该怎么写这个SQL语句呢(兄弟们希望你的SQL语句测试后在发上来好么 谢谢)
(
select distinct (name) form mess
select distinct(*) from mess
请不要发以上2条类似的sql语句,我测试过,以上两条语句全部不行,
)
(select 1 from tb where name=t.name and id<t.id)
(select 1 from mess where name=t.name and id<t.id)
-----------
1 AA **
2 BB **
6 EE **
7 FF **吧?
结果应该是:
-----------
1 AA **
2 BB **
6 EE **
7 FF **吧?
=================================================================
恩呵呵 写错了
select * from tb where id in (select id from(select distinct (name),id form mess))
select distinct name from tt 我这样就行
AA
BB
挺奇怪的
select distinct name from tt 我这样就行
AA
BB
Top
chshjking(金少) ( ) 信誉:100 2007-08-21 10:38:04 得分: 0
select distinct (name),** from mess
Top =============================
这样的形式只能显示一列字段 我要整表的字段
where id<=(select min(id) from [mess]
group by name )可能不对,你试试
Where (Select Count(A) From mess Where name= T.name And id < T.id) =0
Order By id, name
(select 1 from mess where name=m1.name and id<m1.id)
------------
select * from mess t where not exists
(select 1 from mess where name=t.name and id<t.id)
没有问题
where id in (select min(id) from mess group by name )
Select * From mess A Where Not Exists(Select id From mess Where name = A.name And id < A.id)--方法二
Select * From mess A Where id In (Select Min(id) From mess Group By name)--方法三
Select * From mess A Where id = (Select Min(id) From mess Where name = A.name)--方法四
Select A.* From mess A
Inner Join
(Select name, Min(id) As id From mess Group By name) B
On A.name = B.name And A.id = B.id
不用这样麻烦吧select distinct ([name]),[id],[mess] from mess
没有问题
----------說"沒有問題"前,最好自己先測試下。
WHERE NOT EXISTS (SELECT 1 FROM mess b WHERE a.name = b.name AND a.id > b.id)
Select * From mess T
Where (Select Count(name) From mess Where name= T.name And id < T.id) =0
Order By id, name--方法二:
select * from mess t where not exists
(select 1 from mess where name=t.name and id<t.id)--方法三:
Select * From mess T
Where id In (Select TOP 1 id From A Where name = T.name Order By id asc)
Order By name, id asc
on a.id=b.id and a.name=b.name
on a.id=b.id and a.name=b.name order by b.name asc
select * from mess inner join (select id,distinct(name) from mess group by name) b
on a.id=b.id and a.name=b.name order by b.name asc
----------語法錯誤
(select 1 from mess where name=t.name and id<t.id)也就是你4种方法中的方法一
如果name=aa对应的mess都是相同的,可以
select name,mess from table group by name,mess;
如果想要查询是否有name,mess重复的数据,可以
select name,mess,count(*) from table group by name,mess
having count(*)=1;--等于1不重复,>1说明有重复
如果name=aa对应的mess是不相同的,可以
select name,mess from table where id=(select min(id) from table where name='aa')
and name='aa';
如果想快速删除重复数据,可以
DELETE FROM table E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM table X WHERE X.name= E.name);
Select * From mess A Where id In (Select Min(id) From mess Group By name)
select * from mess inner join (select id,distinct(name) from mess group by name) b
on a.id=b.id and a.name=b.name order by b.name asc
这条可以
select * from mess inner join (select id,distinct(name) from mess group by name) b
on a.id=b.id and a.name=b.name order by b.name asc
-----------
你測試了沒?都語法錯誤了,還可以。
感觉你太热心了,别人的回复都要去测试一下的!
to paoluo(一天到晚游泳的鱼(學習.NET中)) :
感觉你太热心了,别人的回复都要去测试一下的!
----------其實你只需要放入查詢分析器中執行下即可。而那個語句,很明顯的可以看到語法錯誤,即使不執行也會知道是錯誤的。
楼上的,有的人回复的用的数据库是不一样的,可能有些地方的语法稍有出入,况且这里回复的基本都是凭记忆写出来的,难免会有落关键字等错误,你用你的数据库测试有什么意义呢!
--------基本語法錯誤,用什麼數據庫測試都是一樣的。注意,是基本語法錯誤。就算是憑記憶寫的,有錯誤很正常,但是還是要盡量寫正確的。而且,我指出語法錯誤,也沒有問題吧。再說了,寫個錯誤的在那,沒人會拿去用的。
不行啊,谁能帮忙解决下