多个字段共同作为主键的查询问题

[hsrzyn]

表TableA 字段有courseNO，Name,Majorno ,
courseno和majorno做主键
如 courseno name majorno
1 a 1
1 b 1
1 c 2
1 a 2
1 b 3
2 c 1
2 d 1
我想查出的结果是
courseno majornno name
1 1 a，b
1 2 c，a
1 3 b
2 1 c，d

我想着用临时表处理…… 但弄了很长时间没查出来，不知道到底该对哪列上建游标
代码就不贴了吧……
我的思路是:在courseno上建立游标，循环查询每一行，在查询前先记录本行的courseno和majorname，之后与上一行做比较，
如果courseno相同而majorno不同，就update 临时表，
如果courseno相同且majorno相同，无操作
如果couseno不同，insert临时表

[hsrzyn]

[Quote=引用 6 楼 sdhylj 的回复:]
你这个表,既然courseno和majorno做主键,那么你的数据就是不正确的,有重复数据.
[/Quote]
不好意思啊 说错了 是哪三个字段共同当主键

[hsrzyn]

解决了
feixianxxx O(∩_∩)O~ 回复一个就够了 结贴了……

[让你望见影子的墙]

字符串合并，见精华帖。自己写一个函数就行

[青锋-SS]

用函数

[feixianxxx]

create table tb (CardNo int,CardName varchar(10))
insert tb
select 001,        'AA' union all
select 001,        'BB' union all
select 001,        'CC' union all
select 002,        'DD' union all
select 002,        'EE' union all
select 003,        'FF'
go
CREATE FUNCTION dbo.f_tb(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ' ' + CardName FROM tb WHERE CardNo=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 

SELECT CardNo, CardName = dbo.f_tb(CardNo) FROM tb GROUP BY CardNo 
drop table tb 
drop function dbo.f_tb 
go


--2005 XML
if object_id('tb') is not null
drop table tb
create table tb(aa varchar(20),bb varchar(20))
insert into tb select '奥米茄',  '地板' union all
select '瓷砖' ,'地板' union all
select '蒙娜丽莎','厨房设备' union all 
select '实木复合地板' ,'涂料' 

select  [values]=stuff((select '|'+aa from tb t where bb=tb.bb for xml path('')), 1, 1, ''),bb
from tb 
group by bb 
/*bb                   values
-------------------- --------------------
厨房设备                 蒙娜丽莎
地板                   奥米茄|瓷砖
涂料                   实木复合地板

(3 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-21 11:29:20
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([courseno] int,[name] varchar(1),[majorno] int)
insert [tb]
select 1,'a',1 union all
select 1,'b',1 union all
select 1,'c',2 union all
select 1,'a',2 union all
select 1,'b',3 union all
select 2,'c',1 union all
select 2,'d',1
--------------开始查询--------------------------
select courseno, majorno,[name]=stuff((select ','+[name] from tb t where majorno=tb.majorno for xml path('')), 1, 1, '') 
from tb 
group by courseno, majorno

----------------结果----------------------------
/*courseno    majorno     name
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1           a,b,c,d
1           2           c,a
1           3           b
2           1           a,b,c,d

(4 行受影响)

*/

[feixianxxx]

CREATE FUNCTION dbo.f_tb(@id1 int,@id2 int ) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + name FROM tb WHERE courseno=@id1 and majorno=@id2 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 

SELECT courseno, majorno,name  = dbo.f_tb(courseno, majorno) 
FROM tb 
GROUP BY courseno, majorno

就这么做

[青锋-SS]

你这个表,既然courseno和majorno做主键,那么你的数据就是不正确的,有重复数据.

[SQL77]

[Quote=引用 6 楼 sdhylj 的回复:]
你这个表,既然courseno和majorno做主键,那么你的数据就是不正确的,有重复数据.
[/Quote]
呵呵是

[feixianxxx]

if object_id('[tb]') is not null 
drop table [tb]
go
create table [tb]([courseno] int,[name] varchar(1),[majorno] int)
insert [tb]
select 1,'a',1 union all
select 1,'b',1 union all
select 1,'c',2 union all
select 1,'a',2 union all
select 1,'b',3 union all
select 2,'c',1 union all
select 2,'d',1
go
CREATE FUNCTION dbo.f_tb(@id1 int,@id2 int ) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + name FROM tb WHERE [courseno]=@id1 and [majorno]=@id2 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 

SELECT courseno, majorno,name  = dbo.f_tb([courseno], majorno) 
FROM tb 
GROUP BY courseno, majorno
/*
courseno    majorno     name
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1           a,b
1           2           c,a
1           3           b
2           1           c,d

*/