基础数据查询

Question

表名：xiaojiexid      mingcheng      jibingid
1 一般小结       aaa,bbb,ccc
2 特殊小结       bbb,ccc
3 高级小结       ccc,ddd
4 危险小结       aaa,bbb,ccc,ddd
请问如何得到这种结果xid      mingcheng      jibingid
1 一般小结       aaa
1 一般小结       bbb
1 一般小结       ccc
2 特殊小结       bbb
2 特殊小结       ccc
3 高级小结       ccc
3 高级小结       ccc
4 危险小结       aaa
4 危险小结       bbb
4 危险小结       ccc
4 危险小结       ddd

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([xid] int,[mingcheng] varchar(8),[jibingid] varchar(15))
insert [tb]
select 1,'一般小结','aaa,bbb,ccc' union all
select 2,'特殊小结','bbb,ccc' union all
select 3,'高级小结','ccc,ddd' union all
select 4,'危险小结','aaa,bbb,ccc,ddd'
 
---查询---
select 
  a.xid,
  mingcheng,
  substring(a.jibingid,b.number,charindex(',',a.jibingid+',',b.number)-b.number) 
from 
  tb a,master..spt_values b
where
  charindex(',',','+a.jibingid,b.number)=b.number
and
  b.type='P' 
---结果---
xid         mingcheng 
----------- --------- ---------------
1           一般小结      aaa
1           一般小结      bbb
1           一般小结      ccc
2           特殊小结      bbb
2           特殊小结      ccc
3           高级小结      ccc
3           高级小结      ddd
4           危险小结      aaa
4           危险小结      bbb
4           危险小结      ccc
4           危险小结      ddd(11 行受影响)

Answer 2

到Oracle版问一下吧,Oracle没有master..spt_values 这个表.好像有更简单的实现函数.

Answer 3

http://blog.csdn.net/jinjazz/archive/2009/09/01/4507720.aspxIF OBJECT_ID('tb') IS NOT NULL    
    DROP TABLE tb   
GO   
CREATE TABLE tb (id INT,col VARCHAR(30))    
INSERT INTO tb VALUES(1,'aa,bb')    
INSERT INTO tb VALUES(2,'aaa,bbb,ccc')    
GO    
--1.2000/2005通用方法   
SELECT    
    a.id,   
    col=SUBSTRING(a.col,number,CHARINDEX(',',a.col+',',number)-b.number)   
FROM tb a   
    JOIN master..spt_values b   
        ON b.type='p'    
            --AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可   
            AND CHARINDEX(',',','+a.col,number)=number   
--结果:   
/*  
id          col  
----------- --------------------------------------------------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc  
 
(5 行受影响)  
*/  
               
--2.2005以上新方法:   
SELECT a.id,b.col   
FROM (   
    SELECT id,col=CAST('<v>'+REPLACE(col,',','</v><v>')+'</v>' AS XML)   
    FROM tb    
) AS a    
    OUTER APPLY (   
        SELECT C.value('.','varchar(50)') AS col --此处value必须为小写   
        FROM a.col.nodes('/v') AS T(C)   
    ) AS b   
--结果:   
/*  
id          col  
----------- --------------------------------------------------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc  
 
(5 行受影响)  
*/     
  
--3.游标循环法:   
DECLARE @t TABLE (id INT,col NVARCHAR(50))   
DECLARE @id INT,@col nvarchar(200)   
DECLARE c CURSOR FOR SELECT * FROM tb   
OPEN c   
FETCH NEXT FROM c INTO @id,@col   
WHILE @@FETCH_STATUS = 0   
BEGIN    
    WHILE CHARINDEX(',',@col)>0   
    BEGIN   
        INSERT @t SELECT @id,LEFT(@col,CHARINDEX(',',@col+',')-1)    
        SET  @col=STUFF(@col,1,CHARINDEX(',',@col),'')    
    END   
    INSERT @t SELECT @id,LEFT(@col,CHARINDEX(',',@col+',')-1) --退出循环后插入最后获取的值   
    FETCH NEXT FROM c INTO @id,@col   
END   
CLOSE c   
DEALLOCATE c   
--查询   
SELECT * FROM @t   
--结果:   
/*  
id          col  
----------- --------------------------------------------------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc  
 
(5 行受影响)  
*/  
  
--4.SQL2005 函数法:   
/*---------------------------------  
--  Author : 分拆列值(函数法) htl258(Tony)  
--  Date   : 2009-09-10 01:38:02  
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   
    Mar 29 2009 10:27:29   
    Copyright (c) 1988-2008 Microsoft Corporation  
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)  
---------------------------------*/  
IF OBJECT_ID('tb') IS NOT NULL    
    DROP TABLE tb   
GO   
CREATE TABLE tb (id INT,col VARCHAR(30))    
INSERT INTO tb VALUES(1,'aa,bb')    
INSERT INTO tb VALUES(2,'aaa,bbb,ccc')    
GO    
  
IF OBJECT_ID('f_str') IS NOT NULL    
    DROP FUNCTION f_str   
GO   
CREATE FUNCTION f_str(@str VARCHAR(20))   
RETURNS @t TABLE(col VARCHAR(20))   
AS   
BEGIN   
    SET @str=@str+','  
    WHILE len(@str)>0   
        BEGIN   
            INSERT @t SELECT LEFT(@str,CHARINDEX(',',@str)-1)   
            SET @str=STUFF(@str,1,CHARINDEX(',',@str),'')   
        END   
    RETURN   
END   
GO   
  
--调用查询   
SELECT a.id,b.col    
FROM tb a   
    CROSS APPLY f_str(a.col) b   
/*  
id          col  
----------- --------------------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc  
 
(5 行受影响)  
*/  
  
--5.SQL2005函数法二:   
/*---------------------------------  
--  Author : 分拆列值(函数法) htl258(Tony)  
--  Date   : 2009-09-10 01:46:02  
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   
    Mar 29 2009 10:27:29   
    Copyright (c) 1988-2008 Microsoft Corporation  
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)  
---------------------------------*/  
IF OBJECT_ID('tb') IS NOT NULL    
    DROP TABLE tb   
GO   
CREATE TABLE tb (id INT,col VARCHAR(30))    
INSERT INTO tb VALUES(1,'aa,bb')    
INSERT INTO tb VALUES(2,'aaa,bbb,ccc')    
GO    
  
IF OBJECT_ID('f_str') IS NOT NULL    
    DROP FUNCTION f_str   
GO   
CREATE FUNCTION f_str(@str VARCHAR(50))   
RETURNS @t TABLE(col VARCHAR(50))   
AS   
BEGIN   
    DECLARE @xml XML   
    SET @xml='<v>'+REPLACE(@str,',','</v><v>')+'</v>'    
    INSERT @t SELECT C.value('.','varchar(50)') FROM @xml.nodes('/v') AS T(C)   
    RETURN   
END   
GO   
  
--调用查询   
SELECT a.id,b.col    
FROM tb a   
    CROSS APPLY f_str(a.col) b   
/*  
id          col  
----------- ------------------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc  
 
(5 行受影响)  
*/  本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/09/09/4533846.aspx