--http://expert.csdn.net/Expert/topic/3020/3020661.xml?temp=.4630701
--建表
create table a(id int, name varchar(30))
go
--加数据
insert a
select
1, '水泥,石灰,黄沙'
union all
select
2, '砖头'
union all
select
3, '水泥,石子,油漆'
union all
select
4, '木材,玻璃,地板,窗'
go
--测试语句
declare @s varchar(2000)
set @s=''
select @s=@s+Name+',' from a
set @s=left(@s,len(@s)-1)
declare @sql varchar(2000)
set @sql=replace(@s,',',''' union select ''')
set @sql='set nocount on create table #t(id int IDENTITY(1,1),name varchar(20)) insert #t(name) select '''+@sql+''' select * from #t drop table #t'
select
b.A列,
sum(a.B列)
from
表 a,
(select distinct A列 from 表 where charindex('、',A列) < 1) b
where
a.A列 = b.A列
or
a.A列 like b.A列+'、%'
or
a.A列 like '%、'+b.A列+'、%'
or
a.A列 like '%、'+b.A列
group by
b.A列
---------------把下面的拷貝到sql中直接執行就可----
Create Table test
(
A varchar(30) null,
B int
)
insert into test
select 'A',2 union
select 'B',2 union
select 'A,B',1 union
Select 'C',5
-----------------------------------------下面是sql語句 把表和字段改成自己的就可以----
select K.A,B=sum(isnull(k.B,0)+isnull(P.B,0)) from -----這裡補上isnull 處理
(select * from Test where charindex(',',A)<1) K
left join
(select * from Test where charindex(',',A)>0) P
On (charindex(K.A,P.A)>0)
Group by K.A
-----------------------輸出結果--
A 3
B 3
---------------把下面的拷貝到sql中直接執行就可----
Create Table test
(
A varchar(30) null,
B int
)
insert into test
select 'A',2 union
select 'B',2 union
select 'A,B',1
-----------------------------------------下面是sql語句 把表和字段改成自己的就可以----
select K.A,B=sum(k.B+P.B) from
(select * from Test where charindex(',',A)<1) K -------把這個結果作為一個表
left join
(select * from Test where charindex(',',A)>0) P -------把這個結果作為一個表
On (charindex(K.A,P.A)>0) ------- 再左連----------
Group by K.A ------按主表分組---------
-----------------------輸出結果--
A 3
B 3
---------------把下面的拷貝到sql中直接執行就可----
Create Table test
(
A varchar(30) null,
B int
)
insert into test
select 'A',2 union
select 'B',2 union
select 'A,B',1
-----------------------------------------下面是sql語句 把表和字段改成自己的就可以----
select K.A,B=sum(k.B+P.B) from
(select * from Test where charindex(',',A)<1) K
left join
(select * from Test where charindex(',',A)>0) P
On (charindex(K.A,P.A)>0)
Group by K.A
-----------------------輸出結果--
A 3
B 3
已用时间: 00: 00: 00.16
16:45:12 SQL> create or replace function tes(tp_a in varchar) return number as
16:45:18 2 res number;
16:45:18 3 cursor cur1 is select b from t1 where a like '%'||tp_a||'%';
16:45:18 4 begin
16:45:18 5 res:=0;
16:45:18 6 for cur2 in cur1 loop
16:45:18 7 res:=res+cur2.b;
16:45:18 8 end loop;
16:45:18 9 return res;
16:45:18 10 end;
16:45:18 11 /
函数已创建。
已用时间: 00: 00: 00.32
16:45:18 SQL> select a,tes(a) from t1 where length(a)=1;
A 3
B 3
NAME TIME MONEY
---------- -------------------- ----------
A 2
B 2
A,B 1
選取了 3 列
目前歷時: 00:00:00.31
----------------------------------------------
SQL> select * from (
2 select name,sum(money) from (
3 select decode(instr(name,'A'),0,'','A') name,money from bao_temp)
4 group by name
5 UNION select name,sum(money) from (
6 select decode(instr(name,'B'),0,'','B') name,money from bao_temp)
7 group by name)
8 where name is not null
9 /