数据: id cardid datetime flag
21054 887765 04 20 2006 5:11PM 1
23552 887765 04 21 2006 2:09PM 0
21054 887765 04 23 2006 2:09PM 1
23552 887765 04 25 2006 2:09PM 0
合并成:
cardid time1 time2
887765 04 20 2006 5:11PM 04 21 2006 2:09PM
887765 04 23 2006 2:09PM 04 25 2006 2:09PM谢谢各位了!!!
21054 887765 04 20 2006 5:11PM 1
23552 887765 04 21 2006 2:09PM 0
21054 887765 04 23 2006 2:09PM 1
23552 887765 04 25 2006 2:09PM 0
合并成:
cardid time1 time2
887765 04 20 2006 5:11PM 04 21 2006 2:09PM
887765 04 23 2006 2:09PM 04 25 2006 2:09PM谢谢各位了!!!
FROM TABLENAME A
LEFT JOIN TABLENAME B
ON A.cardid=B.cardid
AND A.FLAG=1
AND B.FLAG=0
AND B.[datetime]=(SELECT MIN([datetime]) FROM TABLENAME WHERE cardid=A.cardid AND FLAG=0)
group by cardid ,flag
time2=max(case when flag=0 then [datetime] end)
from 表
group by cardid
不行啊,你的结果只有一条数据。
如果flag的值为1,那么它的datetime为time1
如果flag的值为0,那么它的datetime为time2。
flag的值,正常情况应该是1,0,1,0,1,0,顺序应该是这个。
============
bloosh2005说的对啊
就是第一行的FLAG=1的和第二行的FLAG=0的记录并起来,同样道理,第三行和第四行并起来
------------------------------------------------------
用行號來合併數據,不能算是規律,因為行號不能拿來直接用的。
借用一下臨時表吧。Create Table TEST
(id Varchar(5),
cardid Varchar(6),
[datetime] DateTime,
flag Bit)
Insert TEST Select '21054', '887765', '04 20 2006 5:11PM', 1
Union All Select '23552', '887765', '04 21 2006 2:09PM', 0
Union All Select '21054', '887765', '04 23 2006 2:09PM', 1
Union All Select '23552', '887765', '04 25 2006 2:09PM', 0
GO
Select IID=Identity(Int,1,1),* Into #T From TESTSelect
cardid,
Max(Case When IID%2 =1 And flag=1 Then [datetime] End) As time1,
Max(Case When IID%2 =0 And flag=0 Then [datetime] End) As time2
From #T
Group By cardid, (IID-1)/2
GO
Drop Table TEST,#T
GO
--Result
/*
cardid time1 time2
887765 2006-04-20 17:11:00.000 2006-04-21 14:09:00.000
887765 2006-04-23 14:09:00.000 2006-04-25 14:09:00.000
*/
呵呵
谢谢各位了