表 xxx

id    nid    zi    dan    yu
1    1    a    1.2    2.0
2    1    b    1.3    2.1
3    1    c    2.0    2.5
4    2    a    1.2    2.2
5    2    d    1.3    2.0
6    3    a    1.2    3.0
7    3    b    1.3    2.5


需要的结果：

nid    zi    dan       yu
1    a    1.2    2.0  2.2  3.0
1    b    1.3    2.1  2.5
1    c    2.0    2.5

各位大侠帮帮忙啊，小弟在这先谢了

大家快来帮忙啊 ~~~~

后成NID为2,3的都不要出来吗?

有一点复杂


/*表 xxx

id        nid        zi        dan        yu
1        1        a        1.2        2.0
2        1        b        1.3        2.1
3        1        c        2.0        2.5
4        2        a        1.2        2.2
5        2        d        1.3        2.0
6        3        a        1.2        3.0
7        3        b        1.3        2.5


需要的结果：

nid        zi        dan           yu
1        a        1.2        2.0  2.2  3.0
1        b        1.3        2.1  2.5
1        c        2.0        2.5*/
--创建临时表
CREATE TABLE #TTT (ZI VARCHAR(10),DAN VARCHAR(10) ,YU VARCHAR(100))
--
---
DECLARE @XXX TABLE(ID INT,NID INT,ZI VARCHAR(10),DAN VARCHAR(10) ,YU VARCHAR(10))

INSERT INTO @XXX SELECT  1,1 ,'a',1.2, 2.0
UNION SELECT 2,1, 'b' ,1.3,2.1
UNION SELECT 3,1,'c', 2.0 ,2.5
UNION SELECT 4,2,'a',1.2,2.2
UNION SELECT 5,2,'d',1.3,2.0
UNION SELECT 6,3,'a',1.2,3.0
UNION SELECT 7,3,'b',1.3,2.5
----
--删除临时表的资料
DELETE  FROM #TTT
--

--声明变量
DECLARE @ZI VARCHAR(10),@ZI1 VARCHAR(10),@DAN VARCHAR(50),@YU VARCHAR(50),@YU1 VARCHAR(50)
SET @YU1=''
---

--用游标

DECLARE CURSOR2 CURSOR  FOR SELECT DISTINCT ZI FROM @XXX
     OPEN CURSOR2
        FETCH NEXT FROM CURSOR2 INTO  @ZI
             WHILE @@FETCH_STATUS=0
                     BEGIN
                     DECLARE CURSOR3 CURSOR  FOR SELECT ZI,DAN,YU FROM @XXX WHERE ZI=@ZI
                         OPEN CURSOR3
                         FETCH NEXT FROM CURSOR3 INTO @ZI1,@DAN,@YU
                                   WHILE @@FETCH_STATUS=0
                                        BEGIN
                                        SET @YU1=@YU1 +' '+ @YU
                                        FETCH NEXT FROM CURSOR3 INTO @ZI1,@DAN,@YU
                              END
                                      INSERT INTO #TTT SELECT @ZI,@DAN,@YU1
                         SET @YU1=''
                         CLOSE CURSOR3
                         DEALLOCATE CURSOR3
                           FETCH NEXT FROM CURSOR2 INTO  @ZI   
               END

        CLOSE CURSOR2
        DEALLOCATE CURSOR2


--最后的结果
SELECT B.NID, A.*  FROM (SELECT * FROM  #TTT) A, @XXX B WHERE A.ZI=B.ZI  AND  B.NID=1

[[italic] 本帖最后由 XieLi 于 2007-12-4 09:41 编辑 [/italic]]

使用到游标?.太复杂了.写个函数就可以实现了.

CREATE TABLE XXX(
ID INT,
NID INT,
ZI VARCHAR(10),
DAN VARCHAR(10) ,
YU VARCHAR(10))

GO
INSERT INTO XXX SELECT  1,1 ,'a',1.2, 2.0
UNION SELECT 2,1, 'b' ,1.3,2.1
UNION SELECT 3,1,'c', 2.0 ,2.5
UNION SELECT 4,2,'a',1.2,2.2
UNION SELECT 5,2,'d',1.3,2.0
UNION SELECT 6,3,'a',1.2,3.0
UNION SELECT 7,3,'b',1.3,2.5

GO
CREATE FUNCTION dbo.GetData(@ZI VARCHAR(10))
    RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @STR VARCHAR(1000)
    SET @STR=''
    SELECT @STR=@STR+YU+' ' FROM XXX WHERE ZI=@ZI
    RETURN @STR
END
GO

SELECT A.NID,A.ZI,B.DAN,YU=dbo.GetData(A.ZI)
FROM
(
    SELECT * FROM XXX WHERE NID=1
) A

INNER JOIN
(
    SELECT ZI,MIN(DAN) DAN FROM XXX GROUP BY ZI
) B
ON A.ZI=B.ZI AND A.DAN=B.DAN

DROP FUNCTION GetData
DROP TABLE XXX


/*
NID         ZI         DAN       YU                                             
----------- ---------- ---------- ------
1           a          1.2        2.0 2.2 3.0
1           b          1.3        2.1 2.5
1           c          2.0        2.5
（所影响的行数为 3 行）
*/

厉害! 学习了!

恩，谢谢purana 仁兄了，问题搞定
也谢谢 XieLi ！！
谢谢各位

是哦!我怎么就想得那么复杂呢!

比我强多了
不过以前没有学会用Sql 函数，没有想到函数有这么多的好处

没看懂，不理解函数的作用，还有不知道A、B怎么出来的，搜索资料继续学习。