22,210
社区成员
发帖
与我相关
我的任务
分享
insert into B(第一,第二,第三,第四,總數)
select
sum(case when id=1 then 個數 end)'第一',
sum(case when id=2 then 個數 end)'第二',
sum(case when id=3 then 個數 end)'第三',
max(case when id=4 then 個數 end)'第四',
sum(個數) '總數' from(
select top 4 ROW_NUMBER() over(order by id desc)id,* from A) a
with table1(falarm) as
(
SELECT '-abc-ef-cda' UNION ALL
SELECT '-gh-ty-ccc' UNION ALL
SELECT '-sc-ch' UNION ALL
SELECT '-cd-dd'
)
select '-' + falarm falarm from
(
SELECT B.falarm FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(falarm , '-' , '</v><v>') + '</v>') from table1
) A
OUTER APPLY
(
SELECT falarm = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )
) B
) c where falarm <>''
如果你的记录存在一个表中,例如表名叫tb1,表中的那列名falarm,那语句就是如下:
select '-' + falarm falarm from
(
SELECT B.falarm FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(falarm , '-' , '</v><v>') + '</v>') from tb1
) A
OUTER APPLY
(
SELECT falarm = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )
) B
) c where falarm <>''
[/quote]
高手啊,再帮忙看一个吧:
现有表A
falarm 个数
-ab 7
-ac 5
-bc 4
-dc 3
-be 1
把表A中个数的前四名放到表B 得到的结果为(姓名 电话可以从另一张表C中得到):
姓名 电话 第一 第二 第三 第四 总数
... ..... 7 5 4 3 19
我是刚接触sql 谢谢了 insert into B(第一,第二,第三,第四,總數)
select
sum(case when id=1 then 個數 end)'第一',
sum(case when id=2 then 個數 end)'第二',
sum(case when id=3 then 個數 end)'第三',
max(case when id=4 then 個數 end)'第四',
sum(個數) '總數' from(
select top 4 ROW_NUMBER() over(order by id desc)id,* from A) a
[/quote]
最后再问一下 如果想得到的结果再改一下呢:
姓名 电话 第一 第二 第三 第四 总数
... ..... -ab -ac -bc -dc 19
with table1(falarm) as
(
SELECT '-a-b' UNION ALL
SELECT '-a-b-c' UNION ALL
SELECT '-a' UNION ALL
SELECT '-c-b'
)
select '-' + falarm falarm from
(
SELECT B.falarm FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(falarm , '-' , '</v><v>') + '</v>') from table1
) A
OUTER APPLY
(
SELECT falarm = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )
) B
) c where falarm <>''
with table1(falarm) as
(
SELECT '-abc-ef-cda' UNION ALL
SELECT '-gh-ty-ccc' UNION ALL
SELECT '-sc-ch' UNION ALL
SELECT '-cd-dd'
)
select '-' + falarm falarm from
(
SELECT B.falarm FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(falarm , '-' , '</v><v>') + '</v>') from table1
) A
OUTER APPLY
(
SELECT falarm = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )
) B
) c where falarm <>''
如果你的记录存在一个表中,例如表名叫tb1,表中的那列名falarm,那语句就是如下:
select '-' + falarm falarm from
(
SELECT B.falarm FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(falarm , '-' , '</v><v>') + '</v>') from tb1
) A
OUTER APPLY
(
SELECT falarm = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )
) B
) c where falarm <>''
CREATE TABLE #T(
falarm varchar(40)
)
CREATE TABLE #Fala(
falarm varchar(40)
)
INSERT INTO #t
SELECT '-ab-bbc' UNION ALL
SELECT '-ab-bc-ce' UNION ALL
SELECT '-ac' UNION ALL
SELECT '-cdd-ba' union all
select '-a-b-c-d' union all
select '-dee-gg-acd-bb' union all
select '-arr-bc-caa-de' union all
select '-at-bpp-cn-dd' union all
select '-agg-bbb-ca-db'
select IDENTITY(int,1,1)id,* into #t2 from #t
DECLARE @i INT ,@MaxLen INT, @pos int,@count int, @sql nvarchar(max), @len int, @tlen int
set @count=(select COUNT(*) from #T)
set @sql=''
while @count>0
begin
SET @i=1
set @MaxLen=(select LEN(falarm) from #t2 where id=@count)
set @pos=1
WHILE @i<@maxLen
BEGIN
set @tlen=@pos
set @pos=(select CHARINDEX('-',falarm,@pos+1) from #t2 where id=@count)
if @pos=0
set @pos=@MaxLen+1
set @len=@pos-@tlen
set @sql=@sql+'
INSERT INTO #Fala
select * from (SELECT SUBSTRING(falarm,'+convert(nvarchar(10),@tlen)+','+convert(nvarchar(10),@len)+')AS fala FROM #T2 where id='+convert(nvarchar(10),@count)+')a WHERE a.fala!=''''
'
SET @i=@i+@len
END
print 'count='+convert(nvarchar(10),@count)
set @count=@count-1
end
set @sql=@sql+'select * from #Fala'
print @sql
exec(@sql)
drop table #t2