解决方案 »
- 求教怎么修改数据库sa的密码?
- 各位高手,我在用sql server导出access出现的问题
- 这蛋疼的问题,可以说是一种无中生有的技术=============纠结当疼中
- 解决马上结贴!!!!如何用SQL语句把生成的查询结果导出为Excel文件?
- 请教 我想实现 服务器和本地数据库记录的同步 服务器的table1 和本地的tabke1 结构相同表名相同 当服务器中的table1 有变化 用1条sql语句查出和本地表的不相同的 记录 怎么写?
- (高手,兄弟们)一条比较难的SQL语句
- 如何使用SQL SERVER2000导出HTML文件到另一台服务器的共享目录?
- 复制大问题。。。
- 我的数据库中某一字段存放的是*.bmp图片文件,发现他太大,如何将他转换成*.jpg图片文件
- vfp问题???
- SQL疑难问问题个人感觉跟域有关
- 无法重定向程序建立链接,请确保 SQL Browser 服务正在运行
select @st1= sum(*) from table where T1='a';
select @st2= sum(*) from table where T1='b';
........
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,T1 varchar(111) )
insert into #tb
select 1,'a,b,c' union all
select 2,'b,c,d,e' union all
select 3,'a,c,d,f' union all
select 4,'a,c' select distinct SUBSTRING(T1,number,CHARINDEX(',',t1+',',number)-number)
from #tb,master..spt_values
where type='p' and number>0
and SUBSTRING(','+T1,number,1)=','/*
(4 row(s) affected)---------------------------------------------------------------------------------------------------------------
a
b
c
d
e
f(6 row(s) affected)
*/select SUBSTRING(T1,number,CHARINDEX(',',t1+',',number)-number),COUNT(*)
from #tb,master..spt_values
where type='p' and number>0
and SUBSTRING(','+T1,number,1)=','
group by SUBSTRING(T1,number,CHARINDEX(',',t1+',',number)-number)/*
--------------------------------------------------------------------------------------------------------------- -----------
a 3
b 2
c 4
d 2
e 1
f 1(6 row(s) affected)
*/
insert #temp
select 'a,b,c' union all
select 'b,c,d,e' union all
select 'a,c,d,f' union all
select 'a,c'
--SQL
--#0.定义临时表#
create table #(id int identity, T1 varchar(10))
declare @sql varchar(max)
set @sql = 'insert into # select T1=''' + replace(stuff((select ',' + T1 from #temp for xml path('')),1,1,''), ',', ''' union all select ''') + ''''
exec(@sql)
--#1.
select T1=stuff((select distinct ','+T1 from # for xml path('')),1,1,'')
/*
T1
a,b,c,d,e,f
*/
--#2.
select result=LTRIM((select ' ' + (T1 + '=' + cast(COUNT(*) as varchar(10))) from # group by T1 for xml path('')))
/*
result
a=3 b=2 c=4 d=2 e=1 f=1
*/
create table #tab(id int, t1 varchar(20))insert into #tab
select 1,'a,b,c'
union all select 2,'b,c,d,e'
union all select 3,'a,c,d,f'
union all select 4,'a,c'declare @sql varchar(1000)
set @sql='select * into ##temp from (select '''
select @Sql=@sql+t1+',' from #tab
select @sql=replace(@sql,',',''' as a union all select'''),@sql=(substring(@sql,1,len(@sql)-22)+') as a')
exec (@sql)--1字符合并
declare @Merger varchar(20)
set @Merger=''
select @Merger=@Merger+','+ a from ##temp group by aselect substring(@Merger,2,len(@Merger)) --结果1-------------------
a,b,c,d,e,f(1 行受影响)select a,count(0) from ##temp group by a
--结果2
a
---- -----------
a 3
b 2
c 4
d 2
e 1
f 1(6 行受影响)