Code
use AspNet
GO
create table w (word nvarchar(100));
insert into w(word)
select('go') union all
select('The third') union all
select('，') union all
select('Doctor') union all
select('girls') union all
select(' <:');
select* from w;
declare @t nvarchar(1000)
set @t='today we will go to see doctor Li, and we will go by buss<.'
select WORD, (LEN(@t)-LEN(REPLACE(@t,WORD,'')))/Len(Word) as number
from w WHERE LEN(@t)-LEN(REPLACE(@t,WORD,'')) >0
Result:
go 2
, 1
Doctor 1
Code
use AspNet
Go
create table GroupAsset(id integer primary key identity(1,1), GroupID int, AssetID int)
create table GroupTbl(GroupID int primary key, GroupName char(10))
create table AssetTbl(AssetID int primary key, AssetName char(10))
create table AssetStatus(id integer primary key identity(1,1), AssetID int, AssetStatus char(10))
select groupname, assetname, d.assetID, e.AssetStatus
from AssetStatus as e,
(select groupname, assetname, b.assetid as assetID
from GroupAsset as a, assetTbl as b, groupTbl as c
where a.assetID = b.assetID and a.GroupID=c.GroupID) as d
where e.AssetID = d.AssetID
use AspNet
go
create table ReverseData(id integer primary key identity(1,1),aa int, bb int, cc int)
insert into ReverseData(aa,bb,cc)
select 0591, 10, 2 union
select 0591 , 20 , 3 union
select 0591 ,40, 6 union
select 0594 ,11 , 4 union
select 0594 ,30 , 6 union
select 0594 ,30 , 4
select * from Reversedata
select m.aa,
substring(m.bb,1,charindex(',',m.bb)-1) as bb,
substring(substring(m.bb,charindex(',',m.bb)+1,len(m.bb)),
1,charindex(',',substring(m.bb,charindex(',',m.bb)+1,len(m.bb)))-1) as cc,
substring(substring(m.bb,charindex(',',m.bb)+1,len(m.bb)),
charindex(',',substring(m.bb,charindex(',',m.bb)+1,len(m.bb)))+1,len(m.bb)) as dd,
h.ee from
(
select * from
(select distinct aa from Reversedata)a
outer apply(select bb= stuff(replace(replace(
(
select bb from Reversedata n
where aa = a.aa
for xml auto
), '<N bb="', ','), '"/>', ''),1,1,''))n
)m left join
(select aa ,sum(cc) as ee from Reversedata group by aa) h on m.aa=h.aa
select 0591, 10, 2 union
select 0591 , 20 , 3 union
select 0591 ,40, 6 union
select 0594 ,11 , 4 union
select 0594 ,30 , 6 union
select 0594 ,30 , 4
--------------------->
591 10 20 40 11
594 11 30 30 14
