求助一条很棘手的sql查询语句，高手请进

Question

表中两个重要字段：炉号，成分，没有加工的炉号就是数字形式，如123，加工一次后该炉号添加123*1,再加工一次炉号添加123*2，不一定所有的炉号都加工，现在要选出加工了两次的炉号和对应的成分，即某个炉号存在，该炉号*1与*2同时存在的炉号及对应的成分。例如：
炉号   成分  
1      c   
2      p
3      ca
2*1    s
2*2    si 选出的如下：
炉号  未加工成分  一次成分    二次成分 
 2      p         s          si  

Answer 1

create table tb(炉号 varchar(10), 成分 varchar(10))
insert into tb values('1'  ,    'c')  
insert into tb values('2'  ,    'p') 
insert into tb values('3'  ,    'ca') 
insert into tb values('2*1',    's') 
insert into tb values('2*2',    'si') 
goselect t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from (select * from tb where charindex('*1',炉号) > 0) t2 where left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from (select * from tb where charindex('*2',炉号) > 0) t3 where left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from
(select * from tb where charindex('*',炉号) <= 0) t1drop table tb/*
炉号         未加工成分      一次成分       二次成分       
---------- ---------- ---------- ---------- 
1          c          NULL       NULL
2          p          s          si
3          ca         NULL       NULL（所影响的行数为 3 行）
*/

Answer 2

create table tb(炉号 varchar(10), 成分 varchar(10))
insert into tb values('1'  ,    'c')  
insert into tb values('2'  ,    'p') 
insert into tb values('3'  ,    'ca') 
insert into tb values('2*1',    's') 
insert into tb values('2*2',    'si') 
goselect * from 
(
select t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from (select * from tb where charindex('*1',炉号) > 0) t2 where left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from (select * from tb where charindex('*2',炉号) > 0) t3 where left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from
(select * from tb where charindex('*',炉号) <= 0) t1
) t
where [一次成分] is not null and  [二次成分] is not nulldrop table tb/*
炉号         未加工成分      一次成分       二次成分       
---------- ---------- ---------- ---------- 
2          p          s          si（所影响的行数为 1 行）
*/

Answer 3

create table [tb]([炉号] varchar(3),[成分] varchar(2))
insert [tb]
select '1','c' union all
select '2','p' union all
select '3','ca' union all
select '2*1','s' union all
select '2*2','si'
 select 
  a.炉号,
  a.[成分] as 未加工成分,
  b.[成分] as 一次成分,
  c.[成分] as 二次成分 
from tb a, tb b,tb c
where a.[炉号]=left(b.炉号,1) and a.[炉号]=left(c.炉号,1) 
and charindex('*1',b.炉号)>0 
and charindex('*2',c.炉号)>0 /**炉号   未加工成分 一次成分 二次成分 
---- ----- ---- ---- 
2    p     s    si（所影响的行数为 1 行）
**/

Answer 4

create table tb(炉号 varchar(50),成分 varchar(50))
insert into tb select '1','c'
insert into tb select '2','p'
insert into tb select '3','ca'
insert into tb select '2*1','s'
insert into tb select '2*2','si';with cte as
(
select 
case when len(replace(炉号,'*',''))=len(炉号) then 炉号+'*0' else 炉号 end as 炉号,
成分
from tb
)
select 
left(炉号,charindex('*',炉号)-1) as 炉号,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=0 then 成分 end) as 未加工成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=1 then 成分 end) as 一次成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=2 then 成分 end) as 二次成分
from cte t
where exists(
select 1 from cte where left(炉号,charindex('*',炉号)-1)=left(t.炉号,charindex('*',t.炉号)-1)
group by left(炉号,charindex('*',炉号)-1) 
having count(*) = 3
)
group by left(炉号,charindex('*',炉号)-1)炉号 未加工成分 一次成分 二次成分
2 p s si

Answer 5

create table tb(no varchar(10), element varchar(10))
insert into tb values('1'  ,    'c')  
insert into tb values('2'  ,    'p') 
insert into tb values('3'  ,    'ca') 
insert into tb values('2*1',    's') 
insert into tb values('2*2',    'si') select tb1.no,tb1.element element,tb2.element element2,tb3.element element3
from tb tb1,
     (select left(no,charindex('*',no)-1) as no,element 
       from tb
      where charindex('*',no)>1 and right(no,charindex('*',no)-1)=1) tb2,
     (select left(no,charindex('*',no)-1) as no,element 
       from tb
      where charindex('*',no)>1 and right(no,charindex('*',no)-1)=2) tb3
where tb1.no=tb2.no and tb1.no=tb3.nono   element element2 element3
2 p s si

Answer 6

charindex影响效率 建议加工的次数单独存放一个字段 

Answer 7

用存储过程的话 是这样的：use master 
goif exists(select * from sysobjects where name='proc_temp')
drop procedure proc_temp
go
create proc proc_temp
as
begin
if exists(select * from sysobjects where name='tb')
drop table tbcreate table tb
(id varchar(30),cont varchar(20))
insert into tb values('1','c')
insert into tb values('2','p')
insert into tb values('3','ca')
insert into tb values('2*1','s')
insert into tb values('2*2','si')
select * from tb
select * from (
select id as 炉号,
未加工成分=a.cont,
一次成分=(select cont from (select * from tb where charindex('*1',id)>0) b where left(b.id,charindex('*1',b.id)-1)=a.id),
二次成分=(select cont from (select * from tb where charindex('*2',id)>0) c where left(c.id,charindex('*2',c.id)-1)=a.id)
from (select * from tb where charindex('*',id)<=0) a)t where 一次成分 is not null and 二次成分 is not null
end 
go
exec proc_temp
go

Answer 8

--1
select * from 
(
select t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from (select * from tb where charindex('*1',炉号) > 0) t2 where left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from (select * from tb where charindex('*2',炉号) > 0) t3 where left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from
(select * from tb where charindex('*',炉号) <= 0) t1
) t
where [一次成分] is not null and  [二次成分] is not null
--2
select 
  a.炉号,
  a.[成分] as 未加工成分,
  b.[成分] as 一次成分,
  c.[成分] as 二次成分 
from tb a, tb b,tb c
where a.[炉号]=left(b.炉号,1) and a.[炉号]=left(c.炉号,1) 
and charindex('*1',b.炉号)>0 
and charindex('*2',c.炉号)>0 
--3
with cte as
(
select 
case when len(replace(炉号,'*',''))=len(炉号) then 炉号+'*0' else 炉号 end as 炉号,
成分
from tb
)
select 
left(炉号,charindex('*',炉号)-1) as 炉号,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=0 then 成分 end) as 未加工成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=1 then 成分 end) as 一次成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=2 then 成分 end) as 二次成分
from cte t
where exists(
select 1 from cte where left(炉号,charindex('*',炉号)-1)=left(t.炉号,charindex('*',t.炉号)-1)
group by left(炉号,charindex('*',炉号)-1) 
having count(*) = 3
)
group by left(炉号,charindex('*',炉号)-1)貌似方案2性能最好嘛！

Answer 9

我在2楼的语句可以改为下面这样,要简单一些.create table tb(炉号 varchar(10), 成分 varchar(10))
insert into tb values('1'  ,    'c')  
insert into tb values('2'  ,    'p') 
insert into tb values('3'  ,    'ca') 
insert into tb values('2*1',    's') 
insert into tb values('2*2',    'si') 
goselect t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from tb t2 where charindex('*1',t2.炉号) > 0 and left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from tb t3 where charindex('*2',t3.炉号) > 0 and left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from tb t1
where charindex('*',t1.炉号) <= 0drop table tb/*
炉号         未加工成分      一次成分       二次成分       
---------- ---------- ---------- ---------- 
1          c          NULL       NULL
2          p          s          si
3          ca         NULL       NULL（所影响的行数为 3 行）
*/

Answer 10

select tb.炉号, tb.成分 as 未加工成分 ,tb1.成分 as 一次成分,tb2.成分 as 二次成分 from 
  tb,
 (select left(炉号,LOCATE('*1',炉号)-1) as 炉号 ,成分 from tb where LOCATE('*1',炉号)>0 ) tb1,
 (select left(炉号,LOCATE('*2',炉号)-1) as 炉号 ,成分 from tb where LOCATE('*2',炉号)>0 ) tb2 
where tb.炉号 =tb2.炉号
炉号  未加工成分  一次成分    二次成分 
2      p        s          si  

Answer 11

select a.炉号, a.成分 as 未加工成分, b.成分 as 一次成分, c.成分 as 二次成分 
  from tb a
  Left join tb b on a.炉号 + '*1' = b.炉号
  Left join tb c on a.炉号 + '*2' = c.炉号
 where right(c.炉号, 2) = '*2'

Answer 12

select a.prd_no,a.name,b.name,c.name from a inner join 
(select left(prd_no,1)as prd_no,name from a where prd_no=left(prd_no,1)+'*1') b
on a.prd_no=b.prd_no inner join 
 (select left(prd_no,1) as prd_no,name from a where prd_no=left(prd_no,1)+'*2') c
on a.prd_no=c.prd_no-----字段说明-------
Prd_no 炉号
name  成分

Answer 13

恩，查询语句好写，可是这样的设计不利于以后的扩展~
建议表结构为
自增编号 炉号 成分 加工次数
1        1     c    0
2        2     p    0
3        3    ca    0
4        2     s    1
5        2    si    2

Answer 14

create table tb(炉号 varchar(10), 成分 varchar(10))
insert into tb values('1'  ,    'c')  
insert into tb values('2'  ,    'p') 
insert into tb values('3'  ,    'ca') 
insert into tb values('2*1',    's') 
insert into tb values('2*2',    'si') 
goselect * from 
(
select t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from (select * from tb where charindex('*1',炉号) > 0) t2 where left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from (select * from tb where charindex('*2',炉号) > 0) t3 where left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from
(select * from tb where charindex('*',炉号) <= 0) t1
) t
where [一次成分] is not null and  [二次成分] is not nulldrop table tb
这个套用的格式比较多
比较常用

Answer 15

Create Table #TAB(LH Varchar(10), CF Varchar(10))
Insert Into #Tab Select '1','c'
Insert Into #Tab Select '2','p'
Insert Into #Tab Select '3','ca'
Insert Into #Tab Select '2*1','s'
Insert Into #Tab Select '2*2','si'Select Left(A.LH, CharIndex('*',A.LH) - 1) AS 炉号,
(Select B.CF From #TAB B Where B.LH = Left(A.LH, CharIndex('*',A.LH) - 1)) As 未加工成分,
(Select C.CF From #TAB C Where Left(C.LH, CharIndex('*',C.LH) - 1) = Left(A.LH, CharIndex('*',A.LH) - 1) And Right(C.LH, 2) = '*1') As 一次成分,
(Select D.CF From #TAB D Where Left(D.LH, CharIndex('*',D.LH) - 1) = Left(A.LH, CharIndex('*',A.LH) - 1) And Right(D.LH, 2) = '*2') As 二次成分
From #TAB A
Where A.LH Like '%*2'炉号         未加工成分      一次成分       二次成分
---------- ---------- ---------- ----------
2          p          s          si(1 行受影响)

Answer 16

--1
select * from 
(
select t1.炉号 ,
       [未加工成分] = t1.成分 ,
       [一次成分] = (select 成分 from (select * from tb where charindex('*1',炉号) > 0) t2 where left(t2.炉号,charindex('*1',t2.炉号) - 1) = t1.炉号),
       [二次成分] = (select 成分 from (select * from tb where charindex('*2',炉号) > 0) t3 where left(t3.炉号,charindex('*2',t3.炉号) - 1) = t1.炉号)
from
(select * from tb where charindex('*',炉号) <= 0) t1
) t
where [一次成分] is not null and  [二次成分] is not null
--2
select 
  a.炉号,
  a.[成分] as 未加工成分,
  b.[成分] as 一次成分,
  c.[成分] as 二次成分 
from tb a, tb b,tb c
where a.[炉号]=left(b.炉号,1) and a.[炉号]=left(c.炉号,1) 
and charindex('*1',b.炉号)>0 
and charindex('*2',c.炉号)>0 
--3
with cte as
(
select 
case when len(replace(炉号,'*',''))=len(炉号) then 炉号+'*0' else 炉号 end as 炉号,
成分
from tb
)
select 
left(炉号,charindex('*',炉号)-1) as 炉号,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=0 then 成分 end) as 未加工成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=1 then 成分 end) as 一次成分,
max(case when cast(right(炉号,len(炉号)-charindex('*',炉号)) as int)=2 then 成分 end) as 二次成分
from cte t
where exists(
select 1 from cte where left(炉号,charindex('*',炉号)-1)=left(t.炉号,charindex('*',t.炉号)-1)
group by left(炉号,charindex('*',炉号)-1) 
having count(*) = 3
)
group by left(炉号,charindex('*',炉号)-1)