SQL搞不定，求高手

可能要写存储过程，或用两个sql。
存储过程不会；
两个sql，select user_class from xxx where user_name=张三
select class from xxx where id in (xxx)

写个存储过程，对1,2,3,4进行截取，分别取出单值，再用case...when...或使用再次查询，返回你想要的数据。

不用存储过程这么复杂吧... sql子查询就能搞定啊你用什么数据库，我写给你

以前也碰到过这样的问题，一个sql没写出来，当时的解决方法是后台处理了一下然后在用sql查的。一般说出现这种情况的问题是数据库设计的不太合理或者存储数据的方式不合理。。
user表数据
user_name   user_class（外键）
张三                  1
张三                  2
张三                  3
张三                  4class表数据
class_id  class_name
1         1班
2          2班
3        3班
4        4班

行转列，列转行，先把user表列转为行，把结果集与class关联，再把关联的结果集行专列：
以下是基于oracle的sqlselect user_name, wmsys.wm_concat(class_name)
  from (SELECT REGEXP_SUBSTR(t.user_class, '[^,]+', 1, num) AS user_id,
               t.user_name
          FROM user t, (SELECT LEVEL num FROM DUAL CONNECT BY LEVEL <= 100)
         WHERE num <=
               LENGTH(t.user_class) - LENGTH(REPLACE(t.user_class, ',')) + 1
           and t.user_name = '张三') lt
  left join class lt1
    on lt.user_id = lt1.class_id

以前也碰到。。过。。想了好久，没想出来，后来都是通过查询结果。先全部查询出来存到list中，在遍历list，重组

一：行转列 SELECT user_name,
               REGEXP_SUBSTR(user_class, '[^,]+', 1, LEVEL, 'i') AS user_class
          FROM user1
        CONNECT BY LEVEL <= LENGTH(user_class) -
                   LENGTH(REGEXP_REPLACE(user_class, ',', '')) + 1
二：两表关联 select a.user_name, c.class_name
  from (SELECT user_name,
               REGEXP_SUBSTR(user_class, '[^,]+', 1, LEVEL, 'i') AS user_class
          FROM user1
        CONNECT BY LEVEL <= LENGTH(user_class) -
                   LENGTH(REGEXP_REPLACE(user_class, ',', '')) + 1) a
  left join class_name c
    on a.user_class = c.class_id
三：wm_concat可以把这个字段里面的值用逗号隔开合并
最终为  select a.user_name as user_name, wm_concat(c.class_name) as class
  from (SELECT user_name,
               REGEXP_SUBSTR(user_class, '[^,]+', 1, LEVEL, 'i') AS user_class
          FROM user1
        CONNECT BY LEVEL <= LENGTH(user_class) -
                   LENGTH(REGEXP_REPLACE(user_class, ',', '')) + 1) a
  left join class_name c
    on a.user_class = c.class_id
group by a.user_name

select user.user_class,class. class_name from class,user where user.user_name=张三 and class_id in ( user_class) ;

如果是ORACLE的话，可以用wm_concat搞定

user表数据
user_name user_class
张三 1,2,3,4你的user表怎么这样的啊？？

CREATE TABLE ST
(
NAME VARCHAR(100),
CLASSID VARCHAR(100)
)
INSERT INTO ST VALUES ('张三','1,2,3,4')CREATE TABLE CL
(
CLASSID VARCHAR(100),
CLASSNAME VARCHAR(100)
)
insert into CL values ('1','1班');
insert into CL values ('2','2班');
insert into CL values ('3','3班');
insert into CL values ('4','4班');
Create function funTestReturn(@name varchar(10))
returns varchar(max)
as
begin
declare @str varchar(max)= ''
declare @strreturn varchar(max)= ''
declare @tmp varchar(10) =''
select @str= classid from ST where name=@name
set @str=@str+',';
while(@str<>'')
begin
   set @tmp=left(@str,charindex(',',@str,1)-1);
   select @strreturn+=className + ',' from cl where classid=@tmp;
   set @str=stuff(@str,1,charindex(',',@str,1),'');
end
if(LEN(@strreturn)>0)
set @strreturn = SUBSTRING(@strreturn,1,LEN(@strreturn)-1);
return @strreturn
end
select ST.*,  dbo.funTestReturn(ST.Name) as name  from ST  ；NAME    CLASSID   name张三      1,2,3,4       1班,2班,3班,4班    (1 行受影响)

Select '张三' As [User_Name],'1,2,3,4' As User_Class Into #UserSelect * Into #Class
From
(
Select '1' As Class_Id, '1班' As Class_Name Union All
Select '2' As Class_Id, '2班' As Class_Name Union All
Select '3' As Class_Id, '3班' As Class_Name Union All
Select '4' As Class_Id, '4班' As Class_Name
) T
Select [User_Name],Class=STUFF((SELECT ','+[Class_Name] FROM (Select [User_Name],Class_Name
From #User Join #Class On CharIndex(Class_Id,User_Class)>0 ) A WHERE [User_Name]=T.[User_Name] FOR XML PATH('')), 1, 1, '') From
(
Select [User_Name],Class_Name
From #User Join #Class On CharIndex(Class_Id,User_Class)>0
) T
Group by [User_Name]Drop Table #User
Drop Table #Class

在SQL 2005 中测试可行，且不用另写存储过程和函数

上面的地址是在mysql中使用join的循环以及辅助表。
是我在做数据迁移的时候用的，你可以学习学习。

mssql里可以用for xml path+ stuff 解决

调试易

SQL搞不定，求高手

解决方案 »