數據庫轉換問題﹐思歸﹐大力﹐幫幫我﹗﹗﹗﹗﹗﹗﹗﹗﹗

[dahroswell]

求各路高手幫忙﹐我有這樣一個表A

empid empno empdate emptime

1756 AA000042 2002-05-19 60328
1756 AA000042 2002-05-19 60332
1756 AA000042 2002-05-19 60336
1756 AA000042 2002-05-19 60340
1756 AA000042 2002-05-20 29755
1756 AA000042 2002-06-11 41864
1756 AA000042 2002-06-11 50900
1757 AA000021 2002-05-20 30200
1759 AA000010 2002-12-14 59400
1759 AA000010 2002-12-28 79740
1759 AA000010 2003-04-03 63360
1760 AA000011 2002-05-22 45005
1760 AA000011 2002-05-22 48408
1760 AA000011 2002-05-22 63005
1760 AA000011 2002-05-22 66441
.
.
.
.
.
.

要將上表A轉換為下表B
是按日期不同來轉換成行的
empid empno t1 t2 t3 t4

1756 AA000042 2002-05-19 60328 2002-05-19 60332 2002-05-19 60336 2002-05-19 60340
1756 AA000042 2002-05-20 29755
1756 AA000042 2002-06-11 41864 2002-06-11 50900
1757 AA000021 2002-05-20 30200
1759 AA000010 2002-12-14 59400
1759 AA000010 2002-12-28 79740
1759 AA000010 2003-04-03 63360
1760 AA000011 2002-05-22 45005 2002-05-22 48408 2002-05-22 63005 2002-05-22 66441
高分相贈﹐謝謝各位大俠了﹗﹗﹗
思歸﹐大力﹐幫我﹗﹗﹗
我用了下面的轉換﹕
select empid,empno,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=1 then empdate+' '+emptime end) as t1,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=2 then empdate+' '+emptime end) as t2,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=3 then empdate+' '+emptime end) as t3,
min(case when (select count(*) from a where empid=x.empid and empno=x.empno and empdate=empdate and emptime<=x.emptime)=4 then empdate+' '+emptime end) as t4
from a x
group by empid,empno,empdate
order by empid,empno,empdate
可是出現下列錯誤﹕
伺服器: 訊息 130，層級 15，狀態 1，行 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
伺服器: 訊息 170，層級 15，狀態 1，行 3
Line 3: Incorrect syntax near '='.
伺服器: 訊息 170，層級 15，狀態 1，行 4
Line 4: Incorrect syntax near '='.
伺服器: 訊息 170，層級 15，狀態 1，行 5
Line 5: Incorrect syntax near '='.
思歸﹐大力快求求我﹐我這東西好急用呀﹐
謝謝啦﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗﹗

[batisituta]

mark

[dahroswell]

思歸﹐大力在哪呀﹗﹗﹗

[srz007]

大力可是sql板的顶级高手,你跑这里来问,他看的到才怪

[zhiri]

thanks

[zhiri]

select empid,empno,
min(t1) as t1,
min(t2) as t2,
min(t3) as t3,
min(t4) as t4
from (
select empid,empno,kqdate,
case when (select count(*) from c where empid=x.empid and empno=x.empno and kqdate=x.kqdate and kqtime<=x.kqtime)=1 then kqdate+' '+kqtime end as t1,
case when (select count(*) from c where empid=x.empid and empno=x.empno and kqdate=x.kqdate and kqtime<=x.kqtime)=2 then kqdate+' '+kqtime end as t2,
case when (select count(*) from c where empid=x.empid and empno=x.empno and kqdate=x.kqdate and kqtime<=x.kqtime)=3 then kqdate+' '+kqtime end as t3,
case when (select count(*) from c where empid=x.empid and empno=x.empno and kqdate=x.kqdate and kqtime<=x.kqtime)=4 then kqdate+' '+kqtime end as t4
from c x
) as y
group by empid,empno,kqdate
order by empid,empno,kqdate
go

[dahroswell]

求求哥們了﹐我好急的。

[dahroswell]

思歸﹐大力在哪呀﹗﹗﹗