select DISTINCT message from 表
解决方案 »
- SQL查询分析器制定作业时,经常报“无法对请求的用户名....执行setuser”
- 如何读出临时表的列名?(建临时表时,临时表的列名没有固定)
- 如何写一个SQL语句 当不存在某表时,先新建该表,再添加数据,否则直接添加数据
- 求查一个月有多少天的SQL语句
- 求一个语句可以该一表的商品编码~~~~100分
- SqlServer 导入外部Excel数据丢失问题(在线)
- 请教一个按时间查询问题!
- 请问一个SQL Server查询方式问题
- 跪求子字符串查找语句
- 关于sqlserver时间格式问题
- 如何将asa的数据库备份.db的数据库文件还原在sqlserver2000上.
- 我现在有数据库的Monitor_Data,Monitor_Log两个文件,我怎么能在SQL里显示出来呢?
where aa.phone=bb.phone and aa.message=bb.message
select * from 表 aa
where message=(select top 1 message from 表 bb where phone=aa.phone)
--随机抽取分组后的数据,每组取N笔--示例数据
declare @t table(id int,catgoryid int,name varchar(10))
insert @t select 1 ,2,'a'
union all select 2 ,2,'n'
union all select 3 ,2,'h'
union all select 4 ,2,'e'
union all select 5 ,2,'d'
union all select 6 ,3,'o'
union all select 7 ,3,'t'
union all select 8 ,3,'u'
union all select 9 ,3,'c'
union all select 10,3,'m'
union all select 11,5,'k'
union all select 12,5,'l'
union all select 13,5,'q'
union all select 14,5,'w'--查询
select mid=identity(int),id=cast(id as int),catgoryid,name
into #t from @t
order by newid()select * from #tselect id,catgoryid,name
from #t a
where mid in(select top 3 mid from #t where catgoryid=a.catgoryid)
order by catgoryid,iddrop table #t/*--其中一次测试结果id catgoryid name
----------- ----------- ----------
1 2 a
2 2 n
3 2 h
7 3 t
9 3 c
10 3 m
11 5 k
12 5 l
14 5 w(所影响的行数为 9 行)
distinct 避免了相同号码的相同信息的重复显示
where cast(a.message as varchar(200)+cast(a.phone as varchar(20)
=(select top 1 cast(message as varchar(200)+cast(phone as varchar(20)
from yourtable b where a.phone=b.phone order by message)
相同信息,distinct是对选出的字段同时起作用
group by phone
from phone as B
group by phone或者select distinct phone, (select top 1 message from table as A where A.phone=B.phone) as message from phone as B
from talbe as B
group by phone或者select distinct phone, (select top 1 message from table as A where A.phone=B.phone) as message from table as B
或
select message=min(message),phone from tablename group by phone
----------------------A.有關鍵字段(s)-----------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table1]
create table table1(id int not null identity primary key,phone varchar(15) not null,message varchar(20) null,address varchar(50) null)goinsert into table1
select '13512345678','aaa','a' union all
select '13512345678','bbb','b' union all
select '13512345678','ccc','c' union all
select '13512345678','aaa','s' union all
select '13512345671','bbb','j' union all
select '13512345671','bbb','j' union all
select '13512345671','ccf','f' union all
select '13512345671','ccd','e' union all
select '13112345671','aaa','d'
goselect * from table1
/*
id phone message address
1 13512345678 aaa a
2 13512345678 bbb b
3 13512345678 ccc c
4 13512345678 aaa s
5 13512345671 bbb j
6 13512345671 bbb j
7 13512345671 ccf f
8 13512345671 ccd e
9 13112345671 aaa d
*/--方法1
select a.* from table1 a join (select min(id) as id from table1 group by phone) b on a.id=b.id
/*
id phone message address
9 13112345671 aaa d
5 13512345671 bbb j
1 13512345678 aaa a
*/--方法2
select a.* from table1 a where id in(select min(id) as id from table1 where a.phone=phone)
/*
id phone message address
9 13112345671 aaa d
5 13512345671 bbb j
1 13512345678 aaa a
*/----------------------A.有關鍵字段(s)---------------------------------------------------------B.沒有關鍵字段(s)-----------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[table2]
create table table2(phone varchar(15) not null,message varchar(20) null,address varchar(50) null)goinsert into table2
select '13512345678','aaa','a' union all
select '13512345678','bbb','b' union all
select '13512345678','ccc','c' union all
select '13512345678','aaa','s' union all
select '13512345671','bbb','j' union all
select '13512345671','bbb','j' union all
select '13512345671','ccf','f' union all
select '13512345671','ccd','e' union all
select '13112345671','aaa','d'
goselect * from table2
/*
phone message address
13512345678 aaa a
13512345678 bbb b
13512345678 ccc c
13512345678 aaa s
13512345671 bbb j
13512345671 bbb j
13512345671 ccf f
13512345671 ccd e
13112345671 aaa d
*/alter table table2 add id int not null identity
go--方法1
select a.phone,a.message,a.address from table1 a join (select min(id) as id from table1 group by phone) b on a.id=b.id
/*
phone message address
13112345671 aaa d
13512345671 bbb j
13512345678 aaa a
*/--方法2
select a.phone,a.message,a.address from table1 a where id in(select min(id) as id from table1 where a.phone=phone)
/*
phone message address
13112345671 aaa d
13512345671 bbb j
13512345678 aaa a
*/alter table table2 drop column id
go
----------------------B.沒有關鍵字段(s)-----------------------------------