有两个表tb ,这个表结构如下:
orderid bookid tm stcd
1 a 12:15 0
2 a 12:19 0
3 a 12:17 0
4 a 13:12 1
5 a 9:12 1
6 b 8:12 0
7 b 8:45 0
8 b 8:46 1
9 c 14:12 0
10 c 14:13 1
11 c 14:46 1
如何得到如下数据呢?
bookid tmstcd0 tmstcd1
a 12:15 13:12
a 12:19 9:12
a 12:17
b 8:12 8:46
b 8:45
c 14:12 14:13
c 14:46
谢谢!!!
orderid bookid tm stcd
1 a 12:15 0
2 a 12:19 0
3 a 12:17 0
4 a 13:12 1
5 a 9:12 1
6 b 8:12 0
7 b 8:45 0
8 b 8:46 1
9 c 14:12 0
10 c 14:13 1
11 c 14:46 1
如何得到如下数据呢?
bookid tmstcd0 tmstcd1
a 12:15 13:12
a 12:19 9:12
a 12:17
b 8:12 8:46
b 8:45
c 14:12 14:13
c 14:46
谢谢!!!
,sum(case stcd when 0 then tm else 0 end) as tmstcd0
,sum(case stcd when 1 then tm else 0 end) as tmstcd1
from tb
group by bookid
kkzjk() 我不是求和呀!
create table tb(orderid int,bookid varchar(2),tm varchar(20),stcd int)
insert into tb(orderid,bookid,tm,stcd)
select 1,'a','12:15',0 union all
select 2,'a','12:19',0 union all
select 3,'a','12:17',0 union all
select 4,'a','13:12',1 union all
select 5,'a','9:12', 1 union all
select 6,'b','8:12', 0 union all
select 7,'b','8:45', 0 union all
select 8,'b','8:46', 1 union all
select 9,'c','14:12',0 union all
select 10,'c','14:13',1 union all
select 11,'c','14:46',1 select * from tb
orderid bookid tm stcd
----------- ------ -------------------- -----------
1 a 12:15 0
2 a 12:19 0
3 a 12:17 0
4 a 13:12 1
5 a 9:12 1
6 b 8:12 0
7 b 8:45 0
8 b 8:46 1
9 c 14:12 0
10 c 14:13 1
11 c 14:46 1(所影响的行数为 11 行)sql:
select bookid=isnull(a.bookid,b.bookid)
,tmstcd0=isnull(a.tm,'')
,tmstcd1=isnull(b.tm,'')
from (
select bookid,tm,id=(select count(1) from tb where bookid=a.bookid and stcd=0 and orderid<=a.orderid)
from tb a
where stcd=0
)a
full join (
select bookid,tm,id=(select count(1) from tb where bookid=a.bookid and stcd=1 and orderid<=a.orderid)
from tb a
where stcd=1
)b
on a.bookid=b.bookid and a.id=b.idbookid tmstcd0 tmstcd1
------ -------------------- --------------------
a 12:15 13:12
a 12:19 9:12
a 12:17
b 8:12 8:46
b 8:45
c 14:12 14:13
c 14:46(所影响的行数为 7 行)
,(case stcd when 0 then tm else '' end) as tmstcd0
,(case stcd when 1 then tm else '' end) as tmstcd1
from t 这样的话,出来的结果是:
a 12:15
a 12:19
a 12:17
a 13:12
a 9:12
b 8:12
b 8:45
b 8:46
c 14:12
c 14:13
c 14:46 这样的话,还要对空的地方进行合并
不知如何实现?
谢谢!
可以了,谢谢!