SELECT distinct t.testservice FROM #test t WHERE CHARINDEX(',',testservice)=0testservice ------------- FTPDL FTPUL voice wap(4 row(s) affected)
IF(OBJECT_ID('test') IS NOT null) DROP TABLE test CREATE TABLE test ( testservice VARCHAR(600) ) INSERT INTO test SELECT 'FTPDL,FTPUL' UNION ALL SELECT 'FTPDL' UNION ALL SELECT 'voice' UNION ALL SELECT 'FTPUL' UNION ALL SELECT 'voice' UNION ALL SELECT 'wap' select distinct Result=substring(a.testservice,b.number,charindex(',',a.testservice+',',b.number)-b.number) from test a,master..spt_values b where b.number<=len(a.testservice) and type='p' and number>0 and substring(','+a.testservice,b.number,1)=','/*Result --------------------- FTPDL FTPUL voice wap(4 行受影响)*/drop table test
--测试数据 IF(OBJECT_ID('test') IS NOT null) DROP TABLE test CREATE TABLE test ( testservice nVARCHAR(600) ) INSERT INTO test SELECT 'FTPDL,FTPUL' UNION ALL SELECT 'FTPDL' UNION ALL SELECT 'voice' UNION ALL SELECT 'FTPUL' UNION ALL SELECT 'voice' UNION ALL SELECT 'wap' --查询 select distinct * from test where charindex(',',testservice)=0 --结果 /* testservice ------------ FTPDL FTPUL voice wap */
5L正确! 因为列值有些是以 “,”分隔的值,LZ的意思是统计所有列值,包括逗号分隔内的值。
IF(OBJECT_ID('#test') IS NOT null) DROP TABLE #test CREATE TABLE #test ( testservice VARCHAR(600) ) INSERT INTO #test SELECT 'adfdasf, adf24' UNION ALL SELECT 'FTPDL,FTPUL' UNION ALL SELECT 'FTPDL' UNION ALL SELECT 'voice' UNION ALL SELECT 'FTPUL' UNION ALL SELECT 'voice' UNION ALL SELECT 'wap' --这句 sql 只是排除了字段中有逗号的记录 SELECT distinct t.testservice FROM #test t where charindex(',', t.testservice) = 0;--这句 sql 正确的 select distinct Result=substring(a.testservice,b.number,charindex(',',a.testservice+',',b.number)-b.number) from #test a,master..spt_values b where b.number<=len(a.testservice) and type='p' and number>0 and substring(','+a.testservice,b.number,1)=','
WHERE CHARINDEX(',',testservice)=0testservice
-------------
FTPDL
FTPUL
voice
wap(4 row(s) affected)
IF(OBJECT_ID('test') IS NOT null)
DROP TABLE test
CREATE TABLE test
(
testservice VARCHAR(600)
)
INSERT INTO test
SELECT 'FTPDL,FTPUL' UNION ALL
SELECT 'FTPDL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'FTPUL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'wap'
select
distinct Result=substring(a.testservice,b.number,charindex(',',a.testservice+',',b.number)-b.number)
from test a,master..spt_values b
where b.number<=len(a.testservice)
and type='p'
and number>0
and substring(','+a.testservice,b.number,1)=','/*Result
---------------------
FTPDL
FTPUL
voice
wap(4 行受影响)*/drop table test
IF(OBJECT_ID('test') IS NOT null)
DROP TABLE test
CREATE TABLE test
(
testservice nVARCHAR(600)
)
INSERT INTO test
SELECT 'FTPDL,FTPUL' UNION ALL
SELECT 'FTPDL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'FTPUL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'wap'
--查询
select distinct * from test where charindex(',',testservice)=0
--结果
/*
testservice
------------
FTPDL
FTPUL
voice
wap
*/
因为列值有些是以 “,”分隔的值,LZ的意思是统计所有列值,包括逗号分隔内的值。
IF(OBJECT_ID('#test') IS NOT null) DROP TABLE #test
CREATE TABLE #test
(
testservice VARCHAR(600)
)
INSERT INTO #test
SELECT 'adfdasf, adf24' UNION ALL
SELECT 'FTPDL,FTPUL' UNION ALL
SELECT 'FTPDL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'FTPUL' UNION ALL
SELECT 'voice' UNION ALL
SELECT 'wap' --这句 sql 只是排除了字段中有逗号的记录
SELECT distinct t.testservice FROM #test t where charindex(',', t.testservice) = 0;--这句 sql 正确的
select
distinct Result=substring(a.testservice,b.number,charindex(',',a.testservice+',',b.number)-b.number)
from #test a,master..spt_values b
where b.number<=len(a.testservice)
and type='p'
and number>0
and substring(','+a.testservice,b.number,1)=','