1按类别合并字符串把相同类别的内容按类别合并到一起,见后面的例:
CREATE function f_str(@id int)
returns varchar(8000)
as
begin
declare @ varchar(8000)
set @=''
select @=@+','+rtrim(PHONE)
from A
where id=@id
return(stuff(@,1,1,''))
end
![](/Images/OutliningIndicators/None.gif)
表A
ID PHONE
1 111111
2 222222
1 333333
3 444444
4 555555
4 666666
想查询出:
ID PHONE
1 111111,333333
2 222222
3 444444
4 555555,666666
就用上面的函数
查询时用
SELECT ID,PHONE=f_str(id) from A
2分组后取前几名
解决这个问题的方法也是多种多样,现在在下面通过一个例子提出3种方法,仁者见仁,智者见智
选择哪种方法,或则哪种方法效率高,自己看吧
选出每门学科成绩在前2位的记录,并按照 CourseID 和 Achievement 从高到低排序
-- 建表及插入数据
create table StuTable
(
StudentID varchar(8),
CourseID varchar(10),
Achievement int
)
![](/Images/OutliningIndicators/None.gif)
insert StuTable select 71101, 1, 80
union all select 71102, 4, 86
union all select 71103, 2, 45
union all select 71104, 2, 88
union all select 71102, 1, 90
union all select 71103, 1, 78
union all select 71103, 3, 34
union all select 71104, 3, 64
union all select 71104, 1, 56
union all select 71101, 4, 75
union all select 71101, 2, 76
union all select 71102, 3, 56
union all select 71103, 4, 57
union all select 71102, 2, 85
union all select 71101, 3, 97
union all select 71104, 4, 96
(1) select * from StuTable t
where Achievement
in
(select top 2 Achievement from StuTable where CourseID=t.CourseID order by Achievement desc)order by CourseID,Achievement desc
--select * from 表 a
--where column2 in(
--select top 3 column2 from 表 where column1=a.column1 order by column2 desc)
![](/Images/OutliningIndicators/None.gif)
(2)select * from StuTable t where not exists
(select 1 from StuTable where CourseID=t.CourseID and Achievement>t.Achievement having count(*)>1)
order by CourseID,Achievement desc
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
(3)select * from StuTable t where
(select count(1) from StuTable where CourseID=t.CourseID and Achievement>t.Achievement)<2
order by CourseID,Achievement desc
![](/Images/OutliningIndicators/None.gif)
3排序普通的排序排名次比较简单
举个例子:
我有一个成绩表,新增一列为名次。要求总成绩相同的名次相同。
xuehao xingming zongfen
0010 lixue 500
0015 zhansi 456
0014 heyao 512
0016 woshi 423
此表xuehao值唯一,希望生成新表如下:
mingci xuehao xingming zongfen
1 0014 heyao 512
2 0010 lixue 500
3 0015 zhansi 456
4 0016 woshi 423
方法1:
select 1+isnull((select count(1) from 成绩表 where zongfen>a.zongfen),0) as mingci,xuehao,xingming,zongfen from 成绩表 a order by zongfen desc 方法2:
select mingci=identity(int,1,1),* into #t from @tb order by zongfen desc
select * from #t
![](/Images/OutliningIndicators/None.gif)
但是如果有的不是简单的按照zongfen排呢,或者zongfen需要先求出来呢
如
有一组号码dhhm
138111111
138111111
138222222
138111111
138111111
138222222
138333333
138222222
138333333
按次数排序后
select dhhm,count(*) from table group by dhhm order by count(*) desc
![](/Images/OutliningIndicators/None.gif)
得出
138111111 4
138222222 3
138333333 2
有什么方法能够取出各号码次数的排名,比如138111111排在第一位?
方法1:
select dhhm,
[count]=count(*)
into #
from table
group by dhhm
order by count(*) desc--要想求得排名,必须要排序的数目,或者象上面的那个总分,没有就根据临时表先求出来
然后
select dhhm,
[order]=(select count(1)+1 from # where [count]>t.[count])
from # t
方法2:
其实也可以不用临时表啊,
把要插入临时表求大小的语句赋予它别名
select dhhm,[count]=count(1) from @tb group by dhhm
select dhhm,
[order]=(select count(1)+1 from
(select dhhm,[count]=count(1) from @tb group by dhhm )b
where b.[count]>t.[count])
from
(select dhhm,[count]=count(1) from @tb group by dhhm )t
4字符串合并
下面这个例子已经很好的说明了如何用函数进行字符串合并,看例子就行,不用再说什么了
--测试数据
Create table t (id varchar(5),公司 varchar(6),职员 varchar(4),金额 money)
insert into t select 'F0001','A公司','王三',100
union all select 'F0001','A公司','李杰',200
union all select 'F0001','B公司','张静',300
union all select 'F0002','A公司','李五',150
union all select 'F0002','C公司','赵为',350
--函数
Create function F_TGetStr
(@Item as varchar(10),
--@User_Name varchar(10), --这个对应分组的列,如果还有其他的列用于分组,则也要加入函数中,现在是没有
--@User_Date int, --比如,分组列还包含 User_Date ,现在是没有
@bit bit --用于判断合并哪个字符串
)
returns varchar(200)
as
begin
declare @s as varchar(200)
set @s=''
if @bit=0
begin
select @s=@s+','+ltrim(rtrim(公司)) from
(select distinct 公司 from T where id=@item
and [User_Name]=@User_Name --对应的,在处理语句中加上条件,现在是没有
and [User_Date]=@User_Date --对应的,在处理语句中加上条件,现在是没有
)
A
end
if @bit=1
begin
select @s=@s+','+ltrim(rtrim(职员)) from
(select distinct 职员 from T where id=@item
and [User_Name]=@User_Name --对应的,在处理语句中加上条件,现在是没有
and [User_Date]=@User_Date --对应的,在处理语句中加上条件,现在是没有
)
A
end
return stuff(@s,1,1,'')
end
![](/Images/OutliningIndicators/None.gif)
--查询
select id,公司=dbo.F_TGetStr(id,0),职员=dbo.F_TGetStr(ID,1),金额=sum(金额)
from t group by ID
--结果
id 公司 职员 金额
----- -------------------- -------------------- ---------------------
F0001 A公司,B公司 李杰,王三,张静 600.0000
F0002 A公司,C公司 李五,赵为 500.0000
![](/Images/OutliningIndicators/None.gif)
(所影响的行数为 2 行)
![](/Images/OutliningIndicators/None.gif)
--删除环境和函数
drop table T
drop function F_TGetStr
![](/Images/OutliningIndicators/None.gif)
5去掉左或右的重复项
id value
---------- ------------------------------
00 80
01 80
02 80
03 17
04 17
05 56
06 56
如何得到:
id value
---------- ------------------------------
00
01
02 80
03
04 17
05
06 56
declare @tb table(id varchar(10), value int)
insert @tb
select '00', 80 union all
select '01', 80 union all
select '02', 80 union all
select '03', 17 union all
select '04', 17 union all
select '05', 56 union all
select '06', 56
其实方法一和 二是差不多
(1)select id,value=case id when (select max(id) from @tb where value=t.value)
then cast(value as varchar) else ''end
from @tb t
(2)select id,value=case when id in (select max(id) from @tb group by value)
then cast(value as varchar) else ''end
from @tb t
(3)select id,value=case when exists (select 1 from @tb where value=t.value and id>t.id)
then '' else cast(value as varchar)end
from @tb t
(4)select id,value=(case when not exists(select 1 from @tb where value=t.value and id>t.id) then cast(value as varchar(20)) else '' end)
from @tb t
(5)select a.[id], value=isnull(cast(b.value as varchar),'') from @tb a --效率最高
left join
(select [id]=max(id),value from @tb group by value)b
on a.id=b.id and a.value=b.value
--结果
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
id value
---------- ------------------------------
00
01
02 80
03
04 17
05
06 56
![](/Images/OutliningIndicators/InBlock.gif)
(所影响的行数为 7 行)
*/
![](/Images/OutliningIndicators/None.gif)