請問各位 我如果有一個 Table fDate fTime --------- ----- 094/06/15 07:55 094/06/15 17:10 094/06/16 07:50 094/06/16 12:10 094/06/16 18:00 ...... ...... 想要取出來的資料為以下的資料，要如何下 SQL 才能做到 094/06/15 07:55 17:10 094/06/16 07:50 12:10 18:00 ...... ...... 有一個問題 同一天的資料可能會有數筆(不一定會有多少筆) 是否可以定一個最多取出的數量 以上這樣請各位幫幫忙了 Eric Lin

------
Eric Lin

您好﹗    試試如下﹐取出每筆fTime最小值的資料 Select fDate, Min(fTime) From 資料表 Group By fDate    ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

------
忻晟

/*MSSQL2000 T-SQL*/
--測試資料
DECLARE @T TABLE(fDate VARCHAR(9),fTime VARCHAR(5))
INSERT INTO @T (fDate,fTime)
SELECT '094/06/15', '07:55' UNION
SELECT '094/06/15', '17:10' UNION
SELECT '094/06/16', '07:50' UNION
SELECT '094/06/16', '12:10' UNION
SELECT '094/06/16', '18:00'
--取得最大欄位數
DECLARE @FieldCount INT
SELECT TOP 1 @FieldCount=COUNT(fDate) FROM @T GROUP BY fDate ORDER BY COUNT(fDate) DESC
PRINT @FieldCount
--暫存資料表
CREATE TABLE #tmp (fDate varchar(9),pointer int )
--增加欄位
DECLARE @c NVARCHAR(100)
DECLARE @I INT
SET @I=1
WHILE @I<=@FieldCount BEGIN
   SET @c='ALTER TABLE #tmp ADD fd' CAST(@I AS NVARCHAR) ' VARCHAR(5) NULL'
   EXEC sp_executesql @c
   SET @I=@I 1
END
--加入日期資料及指標
INSERT INTO #tmp (fDate,pointer)
SELECT fDate,0 FROM @T GROUP BY fDate
--填入資料
DECLARE @DD VARCHAR(9)
DECLARE @TT VARCHAR(5)
DECLARE curs CURSOR FAST_FORWARD FOR SELECT fDate,fTime FROM @T
OPEN curs
FETCH NEXT FROM curs INTO @DD,@TT
WHILE @@FETCH_STATUS=0 BEGIN
   SELECT @I=pointer 1 FROM #tmp WHERE fDate=@DD
   SET @c='UPDATE #tmp SET fd@I=@TT,pointer=@I WHERE fDate=@DD'
   SET @c=REPLACE(@c,'@I',CAST(@I AS NVARCHAR))
   EXEC sp_executesql @c,N'@TT VARCHAR(5),@I INT,@DD VARCHAR(9)',@TT,@I,@DD
        FETCH NEXT FROM curs INTO @DD,@TT
END
CLOSE curs
DEALLOCATE curs
SELECT * FROM #tmp
DROP TABLE #tmp    

shieh2700 大大您的回答確實能夠實現我要的 想必您對 SQL 一定下了很大的功夫 我想要得到的時間欄位，最多只要6個 請問要修改哪一個地方 非常的感謝您 Eric Lin Eric Lin

------
Eric Lin

/*
六時間欄位版本
MSSQL2000 T-SQL
*/
--測試資料
DECLARE @T TABLE(fDate VARCHAR(9),fTime VARCHAR(5))
INSERT INTO @T (fDate,fTime)
SELECT '094/06/15', '07:55' UNION SELECT '094/06/15', '17:10' UNION
SELECT '094/06/16', '07:50' UNION SELECT '094/06/16', '12:10' UNION
SELECT '094/06/16', '12:11' UNION SELECT '094/06/16', '12:12' UNION
SELECT '094/06/16', '12:13' UNION SELECT '094/06/16', '12:14' UNION
SELECT '094/06/16', '12:15' UNION SELECT '094/06/16', '12:16' UNION
SELECT '094/06/16', '12:17' UNION SELECT '094/06/16', '18:00'
--暫存資料表
CREATE TABLE #tmp (
   fDate varchar(9),pointer int,
   f1 varchar(5),f2 varchar(5),f3 varchar(5),f4 varchar(5),f5 varchar(5),f6 varchar(5))
DECLARE @c NVARCHAR(100)
DECLARE @I INT
--加入日期資料及指標
INSERT INTO #tmp (fDate,pointer)
SELECT fDate,0 FROM @T GROUP BY fDate
--填入資料
DECLARE @DD VARCHAR(9)
DECLARE @TT VARCHAR(5)
DECLARE curs CURSOR FAST_FORWARD FOR
   SELECT fDate,fTime FROM @T ORDER BY fTime --[時間欄排列順序] 
OPEN curs
FETCH NEXT FROM curs INTO @DD,@TT
WHILE @@FETCH_STATUS=0 BEGIN
   SELECT @I=pointer 1 FROM #tmp WHERE fDate=@DD
   IF @I<=6 BEGIN
      SET @c='UPDATE #tmp SET f@I=@TT,pointer=@I WHERE fDate=@DD'
      SET @c=REPLACE(@c,'@I',CAST(@I AS NVARCHAR))
      EXEC sp_executesql @c,N'@TT VARCHAR(5),@I INT,@DD VARCHAR(9)',@TT,@I,@DD
   END
        FETCH NEXT FROM curs INTO @DD,@TT
END
CLOSE curs
DEALLOCATE curs
SELECT * FROM #tmp
DROP TABLE #tmp