超麻烦sql

Question

create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select' 10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'我最后要把数量累计。得到结果
names,b,p
-----------------
f     ,0, 3
w     ,13,1
t     ,1 ,0
c     ,2 ,1
ak    ,3 ,0
b     ,0 ,1

Answer 1

create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select' 10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'SELECT ISNULL(A.names,B.names) AS names,
     SUM(ISNULL(A.total,0)) AS b, SUM(ISNULL(B.total,0)) AS p
FROM (SELECT total = CAST(LEFT(LTRIM(RTRIM(b)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(b)))-1) AS int),
           names=SUBSTRING(LTRIM(RTRIM(b)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(b))),8000)
      FROM tableaa WHERE b IS NOT NULL) AS A
     FULL JOIN (SELECT total = CAST(LEFT(LTRIM(RTRIM(p)),
                                      PATINDEX('%[^0-9]%',LTRIM(RTRIM(p)))-1) AS int),
           names=SUBSTRING(LTRIM(RTRIM(p)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(p))),8000)
      FROM tableaa WHERE p IS NOT NULL) AS B 
ON A.names = B.names
GROUP BY ISNULL(A.names,B.names)
ORDER BY names;
     
     
DROP TABLE tableaa;

Answer 2

set nocount on
--> Title  : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date   : 2009-11-30 
if object_id('[tableaa]') is not null drop table [tableaa]
go
create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select '10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'
select isnull(a.names,b.names)names,
       sum(isnull(a.b,0))b,
       sum(isnull(b.p,0))p
from(
select substring(b,patindex('%[a-z]%',b),len(b)-patindex('%[a-z]%',b)+1)names,
       sum(cast(left(b,patindex('%[a-z]%',b)-1)as int))b 
from tableaa
group by substring(b,patindex('%[a-z]%',b),len(b)-patindex('%[a-z]%',b)+1))a
full join 
(
select substring(p,patindex('%[a-z]%',p),len(p)-patindex('%[a-z]%',p)+1)names,
       sum(cast(left(p,patindex('%[a-z]%',p)-1)as int))p 
from tableaa
group by substring(p,patindex('%[a-z]%',p),len(p)-patindex('%[a-z]%',p)+1))b
on a.names=b.names
group by isnull(a.names,b.names)
/*
names                b           p
-------------------- ----------- -----------
NULL                 0           0
ak                   3           0
b                    0           1
c                    2           1
f                    0           3
t                    1           0
w                    13          1
警告: 彙總或其他 SET 作業已刪除 Null 值。*/

Answer 3

set nocount on
--> Title  : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date   : 2009-11-30 
if object_id('[tableaa]') is not null drop table [tableaa]
go
create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select '10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'
select isnull(a.names,b.names)names,
       sum(isnull(a.b,0))b,
       sum(isnull(b.p,0))p
from(
select substring(b,patindex('%[a-z]%',b),len(b)-patindex('%[a-z]%',b)+1)names,
       sum(cast(left(b,patindex('%[a-z]%',b)-1)as int))b 
from tableaa
group by substring(b,patindex('%[a-z]%',b),len(b)-patindex('%[a-z]%',b)+1))a
full join 
(
select substring(p,patindex('%[a-z]%',p),len(p)-patindex('%[a-z]%',p)+1)names,
       sum(cast(left(p,patindex('%[a-z]%',p)-1)as int))p 
from tableaa
group by substring(p,patindex('%[a-z]%',p),len(p)-patindex('%[a-z]%',p)+1))b
on a.names=b.names where isnull(a.names,b.names) is not null
group by isnull(a.names,b.names)
/*
names                b           p
ak                   3           0
b                    0           1
c                    2           1
f                    0           3
t                    1           0
w                    13          1
警告: 彙總或其他 SET 作業已刪除 Null 值。*/

Answer 4

create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select' 10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'SELECT 
 ISNULL(A.names,B.names) AS names,
     SUM(ISNULL(A.total,0)) AS b, SUM(ISNULL(B.total,0)) AS p
FROM (SELECT total = SUM(CAST(LEFT(LTRIM(RTRIM(b)),
                           PATINDEX('%[^0-9]%',LTRIM(RTRIM(b)))-1) AS int)),
           names=SUBSTRING(LTRIM(RTRIM(b)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(b))),8000)
      FROM tableaa WHERE b IS NOT NULL
      GROUP BY SUBSTRING(LTRIM(RTRIM(b)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(b))),8000)) AS A
     FULL JOIN (
          SELECT total = SUM(CAST(LEFT(LTRIM(RTRIM(p)),
                                      PATINDEX('%[^0-9]%',LTRIM(RTRIM(p)))-1) AS int)),
              names=SUBSTRING(LTRIM(RTRIM(p)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(p))),8000)
          FROM tableaa WHERE p IS NOT NULL
          GROUP BY SUBSTRING(LTRIM(RTRIM(p)),PATINDEX('%[^0-9]%',LTRIM(RTRIM(p))),8000)
      ) AS B 
ON A.names = B.names
GROUP BY ISNULL(A.names,B.names)
ORDER BY names;
     
     
DROP TABLE tableaa;

Answer 5

有问题，我的这个例子只是样本， 我其实有5列（b，p，sh，v，other）
这样我不能修改你们的sql。
因为isnull（）只能有两个参数，请再帮忙一次，感谢感谢

Answer 6

ISNULL改成COALESCE就能有多个参数了

Answer 7

create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select '10w','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'
select names,
b=sum(case when flag=0 then cast(col as int) else 0 end),
p=sum(case when flag=1 then cast(col as int) else 0 end)
from 
(select names=right(b,len(b)-(patindex('%[a-z]%',b)-1)),col=left(b,(patindex('%[a-z]%',b)-1)),flag=0 
from tableaa 
union all
select right(p,len(p)-(patindex('%[a-z]%',p)-1)), left(p,(patindex('%[a-z]%',p)-1)),1 
from tableaa)t
where names is not null
group by names/*names                b           p           
-------------------- ----------- ----------- 
ak                   3           0
b                    0           1
c                    2           1
f                    0           3
t                    1           0
w                    13          1（所影响的行数为 6 行）*/drop table tableaa

Answer 8

if object_id('tableaa')is not null
    drop table tableaa
go
create  table  tableaa(b varchar(20),p varchar(20))
insert into tableaa  select null,'2f'
insert into tableaa  select null,'1w'
insert into tableaa  select '3w','1f'
insert into tableaa  select '1t',null
insert into tableaa  select' 10W','1c'
insert into tableaa  select '2c',null
insert into tableaa  select '3ak','1b'
go
select * from (
select name=isnull(a.name,b.name),isnull(sum(cast(b as int)),0) b ,isnull(sum(cast(p as int)),0)p from 
(select b=isnull(left(b,patindex('%[a-z]%',b)-1),0),
name=isnull(substring(b,patindex('%[a-z]%',b),len(b)),'')
from tableaa) a full join
(select  p=isnull(left(p,patindex('%[a-z]%',p)-1),0),
name=isnull(substring(p,patindex('%[a-z]%',p),len(p)),0) from tableaa) b
on a.name=b.name 
group by a.name,b.name) tb where b<>0 or p<>0/*
name                 b           p
-------------------- ----------- -----------
ak                   3           0
t                    1           0
b                    0           1
c                    2           1
f                    0           3
W                    13          2
*/

Answer 9

有问题， 我的这个例子只是样本， 我其实有5列（b，p，sh，v，other） 
这样我不能修改你们的sql。 
因为isnull（）只能有两个参数，请再帮忙一次，感谢感谢 

Answer 10

看19樓的吧
select names,
    b=sum(case when flag=0 then cast(col as int) else 0 end),
    p=sum(case when flag=1 then cast(col as int) else 0 end),
    q=sum(case when flag=2 then cast(col as int) else 0 end),
    ... 
from 
(select names=right(b,len(b)-(patindex('%[a-z]%',b)-1)),col=left(b,(patindex('%[a-z]%',b)-1)),flag=0 
from tableaa 
union all
select right(p,len(p)-(patindex('%[a-z]%',p)-1)), left(p,(patindex('%[a-z]%',p)-1)),1 
from tableaa
union all
select right(q,len(q)-(patindex('%[a-z]%',q)-1)), left(q,(patindex('%[a-z]%',q)-1)),2 
from tableaa
...)t
where names is not null
group by names

Answer 11

select names,
    b=sum(case when flag=0 then cast(col as int) else 0 end),
    p=sum(case when flag=1 then cast(col as int) else 0 end),
    sh=sum(case when flag=2 then cast(col as int) else 0 end)
  
from 
(select names=right(b,len(b)-(patindex('%[a-z]%',b)-1)),col=left(b,(patindex('%[a-z]%',b)-1)),flag=0 
from tableaa 
union all
select right(p,len(p)-(patindex('%[a-z]%',p)-1)), left(p,(patindex('%[a-z]%',p)-1)),1 
from tableaa
union all
select right(sh,len(sh)-(patindex('%[a-z]%',sh)-1)), left(sh,(patindex('%[a-z]%',sh)-1)),2 
from tableaa
)t
where names is not null
group by names报 列名‘sh’无效

Answer 12

create  table  tableaa(b varchar(100),p varchar(100),sh varchar(100))
insert into tableaa  select null,'2f','1f'
insert into tableaa  select null,'1w',null
insert into tableaa  select '3w','1f','2a'
insert into tableaa  select '1t',null,'4d'
insert into tableaa  select' 10w','1c','5a'
insert into tableaa  select '2c',null,'6'
insert into tableaa  select '3ak','1b',null感谢我知道问题了，只有数字的也会报错，自能更新数据了