又来提个问题, 发现喜欢上提问题了, 能学到很多东西..

[icelovey]

DECLARE @TB TABLE
(
F1 NVARCHAR(10),
F2 DATETIME,
F3 NVARCHAR(10),
F4 INT
)
INSERT INTO @TB
SELECT 'P1', '2001-1-1', 'D1', 200
UNION ALL SELECT 'P1', '2001-1-1', 'D1', 300
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50

SELECT * FROM @TB


大概要求就是如果F1和F2都相同的时候, 得到下面的结果.

F1 F2
P1 D1
01-JAN-2001
Qty:200
Qty:300
P2 D2
01-FEB-2002
Qty:100
P2 D2
01-JAN-2003
Qty:50

[lweia]

召唤翻译...我没看懂...泪奔...

[icelovey]

乱了重新发下

DECLARE @TB TABLE 
( 
F1 NVARCHAR(10), 
F2 DATETIME, 
F3 NVARCHAR(10), 
F4 INT 
) 
INSERT INTO @TB 
SELECT 'P1', '2001-1-1', 'D1', 200 
UNION ALL SELECT  'P1', '2001-1-1', 'D1', 300 
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100 
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50 

SELECT * FROM @TB 


大概要求就是如果F1和F2都相同的时候, 得到下面的结果. 

F1    F2 
P1    D1 
      01-JAN-2001 
      Qty:200 
      Qty:300 
P2    D2 
      01-FEB-2002 
      Qty:100 
P2    D2 
      01-JAN-2003 
      Qty:50

[icelovey]

[Quote=引用 1 楼 lweia 的回复:]
召唤翻译...我没看懂...泪奔...
[/Quote]
其实我也没看懂...

[--小F--]

DECLARE @TB TABLE 
( 
F1 NVARCHAR(10), 
F2 DATETIME, 
F3 NVARCHAR(10), 
F4 INT 
) 
INSERT INTO @TB 
SELECT 'P1', '2001-1-1', 'D1', 200 
UNION ALL SELECT  'P1', '2001-1-1', 'D1', 300 
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100 
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50 

SELECT 
 (case id when 1 then f1 else '' end) as f1,
 (case id when 1 then f2 else '' end) as f2,
 (case id when 1 then f3 else '' end) as f3,
 f4
FROM 
(select id=row_number()over(partition by f1 order  by f1),* from @tb)t 
/*f1         f2                      f3         f4
---------- ----------------------- ---------- -----------
P1         2001-01-01 00:00:00.000 D1         200
           1900-01-01 00:00:00.000            300
P2         2002-02-01 00:00:00.000 D2         100
           1900-01-01 00:00:00.000            50

(4 行受影响)*/

[bancxc]

用程序做吧

[SQL77]

DECLARE @TB TABLE 
( 
F1 NVARCHAR(10), 
F2 DATETIME, 
F3 NVARCHAR(10), 
F4 INT 
) 
INSERT INTO @TB 
SELECT 'P1', '2001-1-1', 'D1', 200 
UNION ALL SELECT  'P1', '2001-1-1', 'D1', 300 
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100 
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50 

--SELECT * FROM @TB 

SELECT 
CASE WHEN NOT EXISTS(SELECT 1 FROM @TB WHERE F1=T.F1 AND (F2<T.F2 OR F2=T.F2 AND F4<T.F4)) THEN F1 ELSE '' END AS F1,
F2,F3,F4
FROM @TB T

（所影响的行数为 4 行）

F1         F2                                                     F3         F4          
---------- ------------------------------------------------------ ---------- ----------- 
P1         2001-01-01 00:00:00.000                                D1         200
           2001-01-01 00:00:00.000                                D1         300
P2         2002-02-01 00:00:00.000                                D2         100
           2003-01-01 00:00:00.000                                D2         50

（所影响的行数为 4 行）

是哪个意思啊

[SQL77]

DECLARE @TB TABLE 
( 
F1 NVARCHAR(10), 
F2 DATETIME, 
F3 NVARCHAR(10), 
F4 INT 
) 
INSERT INTO @TB 
SELECT 'P1', '2001-1-1', 'D1', 200 
UNION ALL SELECT  'P1', '2001-1-1', 'D1', 300 
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100 
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50 

--SELECT * FROM @TB 

SELECT 
CASE WHEN NOT EXISTS(SELECT 1 FROM @TB WHERE F1=T.F1 AND (F2<T.F2 OR F2=T.F2 AND F4<T.F4)) THEN F1 ELSE '' END AS F1,
F2,
CASE WHEN NOT EXISTS(SELECT 1 FROM @TB WHERE F1=T.F1 AND (F2<T.F2 OR F2=T.F2 AND F4<T.F4)) THEN F3 ELSE '' END AS F3,
F4
FROM @TB T

（所影响的行数为 4 行）

F1         F2                                                     F3         F4          
---------- ------------------------------------------------------ ---------- ----------- 
P1         2001-01-01 00:00:00.000                                D1         200
           2001-01-01 00:00:00.000                                           300
P2         2002-02-01 00:00:00.000                                D2         100
           2003-01-01 00:00:00.000                                           50

（所影响的行数为 4 行）

[icelovey]

可能没看明白..我想要的结果是这样的

F1  |  F2 
----------------
P1  |  D1 
    |  01-JAN-2001 
    |  Qty:200 
    | Qty:300 
P2  | D2 
    | 01-FEB-2002 
    | Qty:100 
P2  | D2 
    | 01-JAN-2003 
    | Qty:50

不知道清楚没

[icelovey]

F1 | F2
----------------
P1 | D1
| 01-JAN-2001
| Qty:200
| Qty:300
-----------------
P2 | D2
| 01-FEB-2002
| Qty:100
------------------
P2 | D2
| 01-JAN-2003
| Qty:50

[bancxc]

---顺序不对 写不出来了
DECLARE @TB TABLE 
( 
F1 NVARCHAR(10), 
F2 DATETIME, 
F3 NVARCHAR(10), 
F4 INT 
) 
INSERT INTO @TB 
SELECT 'P1', '2001-1-1', 'D1', 200 
UNION ALL SELECT  'P1', '2001-1-1', 'D1', 300 
UNION ALL SELECT 'P2', '2002-2-1', 'D2', 100 
UNION ALL SELECT 'P2', '2003-1-1', 'D2', 50
  
;with cte as 
(
	select id=row_number() over(partition by F1,F2 order by getdate()),F1,F2,F3
	from
	(
		Select  F1,F2,F3
		from @tb
		union all
		Select  F1,F2,Convert(nvarchar(10),F2,113)
		from @tb
		union all
		Select  F1,F2,rtrim(ltrim(str(F4)))
		from @tb
	)  v 
) 
select F1=(Case T2.ID When 1 then T2.F1 else '' end),T2.F3
from cte t1 
right join cte t2 on t1.F1=t2.f1 and t1.f2=T2.F2 and t1.id=1
where not exists(
select 1 from cte where t2.F1=F1 and T2.F2=F2 and T2.F3=F3 and T2.ID>ID
)
/*

(4 行受影响)
F1         F3
---------- ----------
P1         D1
           01 01 2001
           200
           300
P2         100
           01 02 2002
           D2
P2         D2
           01 01 2003
           50

(10 行受影响)

*/