sql问题

Question

REATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
SELECT * FROM A-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) 
INTO # FROM syscolumns a, syscolumns b    
SELECT 
    A.ID, 
    COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID) 
FROM A, # B
WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';'   
ORDER BY 1,2
GODROP TABLE A,#id          country         
----------- ----------------
1           中国;日本;韩国
2           美国;意大利;法国
3           德国（所影响的行数为 3 行）ID          COUNTRY  
----------- ---------
1           韩国
1           日本
1           中国
2           法国
2           美国
2           意大利
3           德国（所影响的行数为 7 行）

Answer 1

不是这样的，那个要求计算total，而且数据量比较大，可能有几百甚至上千条，所以效率一定要高

Answer 2

create table tb(id int,field varchar(30))
insert into tb values(1,  '中国 英国 美国')
insert into tb values(2,  '中国 北京 上海')
insert into tb values(3,  '美国,纽约,华盛顿，上海')
insert into tb values(4,  '英国；伦敦；爱丁堡；北京')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) 
INTO # FROM syscolumns a, syscolumns b   select field name, count(*) total from
(
  SELECT 
    A.ID, 
    field = SUBSTRING(A.field, B.ID, CHARINDEX(',', A.field + ',', B.ID) - B.ID) 
  FROM (select id , replace(field,' ', ',') field from tb where charindex(' ' , field) > 0
      union all
      select id , replace(field,'，', ',') field from tb where charindex('，' , field) > 0
      union all
      select id , replace(field,'；', ',') field from tb where charindex('；' , field) > 0
  ) A, # B
  WHERE SUBSTRING(',' + a.field, B.id, 1) = ','   
) t
group by fielddrop table tb,#/*
name        total       
----------- ----------- 
爱丁堡      1
北京        2
华盛顿      1
伦敦        1
美国        2
纽约        1
上海        2
英国        2
中国        2
（所影响的行数为 9 行）
*/

Answer 3

create table tb(id int,field varchar(30))
insert into tb values(1,  '中国 英国 美国')
insert into tb values(2,  '中国 北京 上海')
insert into tb values(3,  '美国,纽约,华盛顿，上海')
insert into tb values(4,  '英国；伦敦；爱丁堡；北京')
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1) 
INTO # FROM syscolumns a, syscolumns b   SELECT 
    name = SUBSTRING(A.field, B.ID, CHARINDEX(',', A.field + ',', B.ID) - B.ID),
    total = count(*) 
FROM 
  (select id , replace(field,' ', ',') field from tb where charindex(' ' , field) > 0
      union all
      select id , replace(field,'，', ',') field from tb where charindex('，' , field) > 0
      union all
      select id , replace(field,'；', ',') field from tb where charindex('；' , field) > 0
  ) A, # B
WHERE SUBSTRING(',' + a.field, B.id, 1) = ','   
group by SUBSTRING(A.field, B.ID, CHARINDEX(',', A.field + ',', B.ID) - B.ID)drop table tb,#/*
name        total       
----------- ----------- 
爱丁堡      1
北京        2
华盛顿      1
伦敦        1
美国        2
纽约        1
上海        2
英国        2
中国        2
（所影响的行数为 9 行）
*/