34,590
社区成员
发帖
与我相关
我的任务
分享
/*
SQL Select语句完整的执行顺序:
1、from子句组装来自不同数据源的数据;
2、where子句基于指定的条件对记录行进行筛选;
3、group by子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用having子句筛选分组;
6、计算所有的表达式;
7、使用order by对结果集进行排序
举例
(7)select (9) top 20 a.name,sum(b.score) as score
(1) from student s
(3) join result b
(2) on s.id=b.id
(4)where s.sex='male'
(5)group by a.name
(6)having sum(b.score)>50
(8)order by a.name
1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. ORDER BY
*/
use pubs
go
/*
========================================================================================================================================================
单表之行列转换
========================================================================================================================================================
*/
if object_id('#table') is not null
drop table #table
go
create table #table
(
id int identity(1,1) not null,
name nvarchar(20) null, --学生姓名
course nvarchar(20) null, --课程名
mark int null --分数(成绩)
)
go
insert into #table(name,course,mark)
select N'张三',N'语文','80' union all
select N'李四',N'数学','70' union all
select N'张三',N'数学','60' union all
select N'张三',N'英语','50'
go
select * from #table
go
--静态SQL
select name,N'语文'=max(case course when N'语文' then mark else 0 end),
N'数学'=max(case course when N'数学' then mark else 0 end),
N'英语'=max(case course when N'英语' then mark else 0 end)
from #table group by name
/*
name N数学 N英语 N语文
-------------------- ----------- ----------- -----------
李四 70 0 0
张三 60 50 80
*/
--动态SQL
declare @sql nvarchar(4000)
set @sql = 'select name '
select @sql = @sql + ',N'+ltrim(course)+'=max(case course when N'''+ltrim(course)+''' then mark else 0 end)'
from (select distinct course from #table) t
set @sql = @sql + ' from #table group by name'
exec(@sql)
/*
name N数学 N英语 N语文
-------------------- ----------- ----------- -----------
李四 70 0 0
张三 60 50 80
*/
--动态SQL
go
declare @sql nvarchar(4000)
set @sql = ''
select @sql = isnull(@sql+'','') + ',N'+ltrim(course)+'=max(case course when N'''+ltrim(course)+''' then mark else 0 end)'
from (select distinct course from #table) t
set @sql = 'select name ' + @sql + ' from #table group by name'
exec(@sql)
/*
name N数学 N英语 N语文
-------------------- ----------- ----------- -----------
李四 70 0 0
张三 60 50 80
*/
declare @sql nvarchar(4000)
set @sql=''
select @sql = isnull(@sql+',','') + 'max(case name when N'''+ltrim(name)+''' then mark else 0 end) [' + ltrim(name) + ']'
from (select distinct name from #table) t
set @sql = 'select course' + @sql + ' from #table group by course'
exec(@sql)
/*
course 李四 张三
-------------------- ----------- -----------
数学 70 60
英语 0 50
语文 0 80
*/
--SQL2005版本的语法
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from #table group by[Course]
print('select * from #table pivot (max([mark]) for [Course] in('+@s+'))b')
--静态SQL2005
select * from #table pivot (max([mark]) for [Course] in([数学],[英语],[语文]))b
--加上总成绩列
go
declare @sql nvarchar(4000)
set @sql = 'select name '
select @sql = @sql + ',N'+ltrim(course)+'=max(case course when N'''+ltrim(course)+''' then mark else 0 end)'
from (select distinct course from #table) t
set @sql = @sql + ' , 总成绩 = sum(mark) from #table group by name'
exec(@sql)
/*
name N数学 N英语 N语文 总成绩
-------------------- ----------- ----------- ----------- -----------
李四 70 0 0 70
张三 60 50 80 190
*/
if object_id('table1') is not null
drop table table1
go
create table table1(id int identity(1,1) not null,name1 nvarchar(20) null, course1 int null, course2 int null)
go
insert into table1 (name1,course1,course2)
select N'李四','90','80' union all
select N'张三','100','70'
go
select * from table1
go
--静态SQL
select * from (select [Name1],[Course]='course1',[Score]=[course1] from table1 union all
select [Name1],[Course]='course2',[Score]=[course2] from table1)t order by [Name1],[Course]
--动态SQL(注意在做这题时用到 系统表 syscolumns 所以在这里不能用 " #表 "
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Name1],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from table1'
from syscolumns where ID=object_id('table1') and Name not in('Name1','Id') --排除不转换的列
--print(@s)
order by Colid
print('select * from ('+@s+')t order by [Name1],[Course]')--增加一个排序
/*
Name1 Course Score
-------------------- ------- -----------
张三 course1 100
张三 course2 70
李四 course1 90
李四 course2 80
*/
go
select * from syscolumns
--原中国风实例-------------------------------------------------------------------------------------
if not object_id('Class') is null
drop table Class
Go
Create table Class(id int identity(1,1) not null, [Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
go
Insert Class(Student,数学,物理,英语,语文)
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
SELECT * from class
--动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student','id')--排除不转换的列
--print(@s)
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
--exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
/*
Student Course Score
------- ------ -----------
张三 ?? 87
张三 ?? 90
张三 ?? 82
张三 ?? 78
李四 ?? 65
李四 ?? 65
李四 ?? 85
李四 ?? 77
*/
--------------------------------------------------------------------------------------------------------
drop table table1
drop table class
四、 一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:
select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名
第二种方法:
先使用联结服务器:
EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'
exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
GO
然后你就可以如下:
select * from 别名.库名.dbo.表名
insert 库名.dbo.表名 select * from 别名.库名.dbo.表名
select * into 库名.dbo.新表名 from 别名.库名.dbo.表名
go
五、 怎样获取一个表中所有的字段信息
蛙蛙推荐:怎样获取一个表中所有字段的信息
先创建一个视图
Create view fielddesc
as
select o.name as table_name,c.name as field_name,t.name as type,c.length as
length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp
from syscolumns c
join systypes t on c.xtype = t.xusertype
join sysobjects o on o.id=c.id
left join sysproperties p on p.smallid=c.colid and p.id=o.id
where o.xtype='U'
查询时:
Select * from fielddesc where table_name = '你的表名'
/*
========================================================================================================================================================
一道基础题
问题描述:
本题用到下面三个关系表:
CARD 借书卡。 CNO 卡号,NAME 姓名,CLASS 班级
BOOKS 图书。 BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理:
1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
3. 查询借阅了"水浒"一书的读者,输出姓名及班级。
4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。
6. 查询现有图书中价格最高的图书,输出书名及作者。
7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。
========================================================================================================================================================
*/
go
if object_id('CARD') IS NOT NULL
drop table card
go
create table card(cno int identity(1000,1) not null,name nvarchar(20) null,class nvarchar(10) null)
go
insert into card(name,class)
select N'a','H001' union all
select N'b','H002' union all
select N'c','H003' union all
select N'aa','H001' union all
select N'cc','H003'
go
if object_id('books') is not null
drop table books
go
create table books(bno int identity(100000,1) not null,bname nvarchar(50) null,author nvarchar(20),price money null,quantity int null)
go
insert into books(bname,author,price,quantity)
select N'自然科学与技术',N'刘小丽',54.8,3000 union all
select N'科学与技术',N'刘丽',74.0,1000 union all
select N'技术',N'小丽',85.3,3000 union all
select N'自然',N'王丽',94.3,4000 union all
select N'自然科学',N'丽',34.0,3500
go
if object_id('borrow') is not null
drop table borrow
go
create table borrow(cno int not null,bno int null,rdate smalldatetime null)
go
insert into borrow(cno,bno,rdate)
select '1001', '100001','2008-07-07' union all
select '1001', '100000',null union all
select '1001', '100002','2008-07-07' union all
select '1001', '100003',null union all
select '1001', '100004','2008-07-07' union all
select '1003', '100001','2008-07-07' union all
select '1002', '100001',null union all
select '1004', '100002','2008-07-07'
go
select * from card
select * from books
select * from borrow
go
/*
2. 找出借书超过3本的读者,输出借书卡号及所借图书册数。
3. 查询借阅了"技术"一书的读者,输出姓名及班级。
4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
*/
go
select cno,count(*) from borrow group by cno having count(bno)>3
go
select card.name,card.class from card
where card.cno in (select cno from borrow where borrow.bno = (select bno from books where bname=N'技术'))
go
select card.name,card.class from card,books,borrow
where card.cno = borrow.cno and books.bno = borrow.bno and books.bname=N'技术'
go
select * from borrow where rdate < getdate()
go
/*
5. 查询书名包括"科学"关键词的图书,输出书号、书名、作者。
6. 查询现有图书中价格最高的图书,输出书名及作者。
7. 查询当前借了"技术"但没有借"自然"的读者,输出其借书卡号,并按卡号降序排序输出。
*/
go
select * from books where bname like N'%科学%'
go
select * from books
go
select bname,author from books where price = (select max(price) from books)
go
select a.cno from borrow a,books where a.bno = books.bno and books.bname=N'技术' and
not exists (select * from borrow aa,books where aa.bno = books.bno and books.bname=N'自然'and a.cno = aa.cno )
go
/*
========================================================================================================================================================
区分两个函数
如果两个表达式不相等,NULLIF 返回第一个 expression 的值。如果相等,NULLIF 返回第一个 expression 类型的空值。
语法
NULLIF ( expression , expression )
ISNULL
如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回 replacement_value。
语法
ISNULL (check_expression , replacement_value )
========================================================================================================================================================
*/
go
select isnull('a','a')
select isnull('a',null)
select isnull(null,null) --当为null时返回null
go
select nullif('a','a') --相等时返回null
select nullif('a',null)
select nullif(null,'b')
========================================================================================================================================================
多表之行列转换
========================================================================================================================================================
*/
go
if object_id('#t1') is not null
drop table t1
go
create table t1(id int identity(1,1) not null,name nvarchar(20) null)
go
if object_id('#t2') is not null
drop table t2
go
create table t2(id int identity(1,1) not null,nameid nvarchar(20) null)
go
insert into t1(name) select N'a' union all select N'b' union all select N'c'
go
insert into t2(nameid) select '1,2' union all select '1,3'
go
go
drop table #temp
select top 100 id = identity(int,1,1) into #temp from syscolumns a,syscolumns b
go
select a.id,b.name from t2 a,t1 b where
charindex(','+convert(nvarchar(20),b.id)+',',','+a.nameid+',')>0
go
drop function F_INDEX
go
CREATE FUNCTION F_INDEX
(@FIELD VARCHAR(20))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @sql VARCHAR(50)
select @sql = isnull(@sql+',','') + name from t1 where charindex(cast(id as varchar),@FIELD)>0
--set @sql = stuff(@sql,1,1,'')
return @sql
end
go
select * from t1
go
select * from t2
go
select id,dbo.F_INDEX(nameid) as name from t2
go
/*
========================================================================================================================================================
行中数字相加
========================================================================================================================================================
*/
go
if object_id("#t") is not null
drop table #t
go
create table #t(id int identity(1,1) not null,col varchar(100) null)
go
insert into #t(col) select '1,2,3,4,5,6,7,8,9' union all select '2,3,4,5,6,7,8,9'
go
select * from #t
go
if object_id(#temp) is not null
drop table #temp
go
select top 100 id=identity(int,1,1) into #temp from syscolumns a,syscolumns b
go
select a.id,sum(convert(int,substring(col,b.id,charindex(',',col+',',b.id)-b.id))) as 总数 from #t a,#temp b
where len(a.col) >= b.id group by a.id
/*
id 总数
----------- -----------
1 45
2 44
*/
go
/*
========================================================================================================================================================
月统计报表数据
========================================================================================================================================================
*/
--有表如下:
--id workdate ondutyName
--1 2006-1-1 a;b;
--2 2006-1-2 b;c;
--3 2006-1-3 a;c;
--....
--workdate表示值班日期,ondutyName表示值班人员,以';'隔开不同的人员,
--现在要得到这样的统计结果:
--比如统计2006年1月每天的值班情况,得到以下的数据:
--Name 1 2 3 ....31
--a 1 0 1 .......
--b 1 1 0 .......
--c 0 1 1 .......
--如果a这天值班了,就用1表示,不值班,就用0表示
--如果统计的是2006年2月数据,因为2月只有28天,那么,生成的统计表的格式就如下:
--Name 1 2 3 ......28
--a .............
--b .............
--c .............
--请问该怎样写这样的存储过程?
go
if object_id('t') is not null
drop table t
go
create table t(id int identity(1,1) not null,workdate smalldatetime null,ondutyName nvarchar(30) null)
go
insert into t(workdate,ondutyName) select '2006-01-01','a;b'
union all select '2006-01-02','b;c'
union all select '2006-01-03','a;d'
union all select '2006-01-04','c;d'
go
select * from t
go
go
select top 3000 identity(int,1,1)[id] into # from sysobjects a, sysobjects b
go
drop table #t
go
select substring(ondutyname+';',b.id,CHARINDEX(';',ondutyname+';',b.id)-b.id)Name,
day(workdate)workdate
into #t
from (select * from t where workdate BETWEEN '2006-1-1' and '2006-1-31') a,# b
where substring(';'+ondutyname,b.id,1)=';'
go
declare @table varchar(1000)
set @table='select Name,'
select @table=@table+'(case when((select workdate from #t b where b.name=a.name and b.workdate='+
QUOTENAME(workdate,'''')+'))is null then 0 else 1 end) as '+QUOTENAME(workdate)+','
from #t
group by workdate
set @table = left(@table,len(@table)-1)+' from #t a group by name'
exec (@table)
go
========================================================================================================================================================
--处理表重复记录(查询和删除)
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
========================================================================================================================================================
*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
select * from #T
go
select * from #T t where id = (select max(ID) from #T where name =t.name)
go
select * from #T t where not exists(select 1 from #T where name = t.name and id > t.id)
go
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID
go
select max(id) id,name,max(memo) memo from #T group by name having count(name)>1
go
/*
id name memo
----------- ---- ----
3 A A3
2 B B2
*/
/*-------------------中国风做的几种方法-------------------------------------------------------------------------------------------------
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID
方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1
方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0
方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)
方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)
方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)
--------------------------------------------------------------------------------------------------------------------------------------------
*/
/*
========================================================================================================================================================
评级问题
========================================================================================================================================================
*/
if object_id('student') is not null
drop table student
go
create table student(id int identity(1,1) not null,name nvarchar(10) null,score nvarchar(10) null)
go
insert into student(name,score)
select N'李四','98' union all
select N'张三','79' union all
select N'陈五','68' union all
select N'麻六','39' union all
select N'麻六2','75'
go
select * from student
--静态SQL
select id,name,score,(case when score>=90 then 'A'
when score>=80 then 'B'
when score >= 70 then 'C'
when score >= 60 then 'D' else 'E' end) as 评级 from student
go
/*
id name score 评级
----------- ---------- ---------- ----
1 李四 98 A
2 张三 79 C
3 陈五 68 D
4 麻六 39 E
5 麻六2 75 C
*/
go
--静态SQL
select sum(case when score between 90 and 100 then 1 else 0 end) as '100~90',
sum(case when score between 80 and 89 then 1 else 0 end) as '89~80',
sum(case when score between 70 and 79 then 1 else 0 end) as '79~70',
sum(case when score between 60 and 69 then 1 else 0 end) as '69~60',
sum(case when score between 0 and 59 then 1 else 0 end) as '59~0' from student
/*
100~90 89~80 79~70 69~60 59~0
----------- ----------- ----------- ----------- -----------
1 0 2 1 1
*/
go
select ltrim(rtrim(min(score/10)*10))+'~'+ ltrim(rtrim(min(score/10)*10+9)) as 分数段,count(*) as 人数 from student group by score/10
go
/*
分数段 人数
------------------------- -----------
30~39 1
60~69 1
70~79 2
90~99 1
*/
go
--======================================================================================================================================================
--在一用户消费帐目表中想查询出不同消费金额段
--(金额段为100元,如0-100,101-200,201-300 ......4901-5000)的用户总数.
--=====================================================================================================================================================
if object_id('t') is not null
drop table t
go
create table t(Salary int,UID varchar(50))
insert t select 100,'U001'
union all select 201,'U002'
union all select 150,'U007'
union all select 300,'U011'
union all select 560,'U021'
union all select 800,'U045'
go
select * from t
go
select rtrim(ltrim(min(salary/100)*100))+'~'+rtrim(ltrim(min(salary/100)*100+99)) as 金额段,count(*) as 用户数 from t group by salary/100
/*
金额段 用户数
------------------------- -----------
100~199 2
200~299 1
300~399 1
500~599 1
800~899 1
*/
/*
========================================================================================================================================================
排名问题(这里有几种情况出现,一种是相同排同名次(间隔一个名次),二种是相同的排同名次)
========================================================================================================================================================
*/
go
if object_id('student') is not null
drop table student
go
create table student(id int identity(1,1) not null,name nvarchar(10) null,score nvarchar(10) null)
go
insert into student(name,score)
select N'李四','98' union all
select N'张三','79' union all
select N'陈五','79' union all
select N'麻六','39' union all
select N'麻六2','75'
go
select * from student
go
select name,score,'排名' = (select count(*)+1 from student where score > t.score) from student t order by score DESC --相同排同名次,有间隔
go
/*
name score 排名
---------- ---------- -----------
李四 98 1
张三 79 2
陈五 79 2
麻六2 75 4
麻六 39 5
*/
go
select name,score,'排名' =
(select count(*)+1 from
(select distinct(score) from student) a where a.score > t.score) from
student t order by score DESC --相同排同名次,无间隔
go
/*
name score 排名
---------- ---------- -----------
李四 98 1
张三 79 2
陈五 79 2
麻六2 75 3
麻六 39 4
*/
/*
/*
========================================================================================================================================================
合并行成一列
========================================================================================================================================================
*/
go
if object_id('cl') is not null
drop table cl
go
create table cl(col1 nvarchar(20) null, col2 nvarchar(20) null)
go
insert into cl(col1,col2)
select '1','a' union all
select '2','b' union all
select '3','c' union all
select '2','b' union all
select '3','c'
go
select * from cl
go
--静态SQL
/*
这里注意,函数不能访问临时表
*/
if object_id('Un_Char') is not null
drop function Un_Char
go
create function Un_Char(@col1 int) --创建合并字符函数(注意学习创建函数语法,用户定义函数不能用于执行修改数据库状态的操作, --自义函数可从查询中调用。标量函数和存储过程一样,可使用 EXECUTE 语句执行)
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s = isnull(@s+',','')+col2 from cl where col1 = @col1
return @s
end
go
select col1,dbo.Un_Char(col1) col2 from cl group by col1
go
/*
col1 col2
-------------------- ----------------------------------------------------------------------------------------------------
1 a
2 b,b
3 c,c
*/
go
drop table cl
go
/*
========================================================================================================================================================
拆分一行成多列
这就需要模拟一个循环来遍历字符串。笔者将这种操作叫做“走过字符串”或者“穿越字符串”,并且一开始就解释了这种技术。当使用SQL时,这是在字符串分析中的基础操作
使用笛卡儿积来生成行号,用来在该行中返回字符串中的每个字符。然后,使用DBMS中的内置的字符串分析函数来摘出所要显示的字符
根据所要实现的目标,可以决定是否需要对在字符串中每个字符都生成单独的一行。下面的例子是要遍历E.ENAME,并且显示字符串中的各个部分(超过1个字符):
select substr(e.ename,iter.pos) a,
substr(e.ename,length(e.ename)-iter.pos+1) b
from (select ename from emp where ename = 'KING') e,
(select id pos from t10) iter
where iter.pos <= length(e.ename)
========================================================================================================================================================
*/
go
if object_id('cl') is not null
drop table cl
go
create table cl(col1 nvarchar(10),col2 nvarchar(100))
go
insert into cl(col1,col2)
select '1','a,b,cc,d' union all
select '2','1,2,3'
go
select * from cl
go
drop table #temp
select top 100 id = identity(int,1,1) into #temp from syscolumns a,syscolumns b
go
select col1, col2 = substring(col2,cl2.id,charindex(',',cl.col2+',',cl2.id)-cl2.id) from cl,#temp cl2
-- where len(replace(col2,',',''))>=cl2.id
where charindex(',',','+cl.col2,cl2.id) = cl2.id
/*
col1 col2
---------- ----------------------------------------------------------------------------------------------------
1 a
1 b
1 c
1 d
2 1
2 2
2 3
*/
go
/*
========================================================================================================================================================
统计字符个数
========================================================================================================================================================
*/
go
if object_id('cl') is not null
drop table cl
go
create table cl(col1 nvarchar(10),col2 nvarchar(30))
go
insert into cl(col1,col2)
select '1','a,bb,c,d,bb,ggg' union all
select '2','1,2,3' union all
select '3','a,b,2'
go
select * from cl
go
/*
在第一行中调用了LENGTH函数来返回字符串的原始长度,然后在第二行中首次调用LENGTH函数是为了求得去掉逗号后的字符串长度,去逗号的操作用了REPLACE函数。
通过计算这两个长度的差可以得到这两个字符串中字符数的差,也就是在这个字符串中有多少个逗号。最后的一步操作就是将计算出的差值除以要查找的字符串的长度。
如果所要查找的字符串的长度大于1时,此时这一步操作是必须的
*/
select col2,'bb' as '要统计的字符',(len(col2) - len(replace(col2,'bb','')))/len('bb') as '个数' from cl --统计字符在本行中的个数
/*
col2 要统计的字符 个数
------------------------------ ------ -----------
a,bb,c,d,bb,ggg bb 2
1,2,3 bb 0
a,b,2 bb 0
*/
go
drop table #temp
select top 100 id = identity(int,1,1) into #temp from syscolumns a,syscolumns b
go
select * from cl
go
select t.col2,count(*) as '个数' from
(select col1, col2 = substring(col2,cl2.id,charindex(',',cl.col2+',',cl2.id)-cl2.id) from cl,#temp cl2
where charindex(',',','+cl.col2,cl2.id) = cl2.id) t group by col2
go
/*
col2 个数
------------------------------ -----------
1 1
2 2
3 1
a 2
b 1
bb 2
c 1
d 1
ggg 1
*/
/*