[sybase][sql] One demo to know how to use left join / inner join / count

先介绍表结构 (table schema):

changeset_history:

Column name	Type	Null	Table	Num
changeset_id	id_TY	No	changeset_history	1
username	sysname	No	changeset_history	2
applname	sysname	No	changeset_history	3
comment	long_descr_TY	Yes	changeset_history	4
create_zdt	datetime	No	changeset_history	5
change_zdt	datetime	No	changeset_history	6
status	char(1   )	No	changeset_history	7

change_hist:

Column name	Type	Null	Table	Num
change_id	bigint	No	change_hist	1
changeset_id	id_TY	No	change_hist	2
table_id	smallint	No	change_hist	3
key_id	binary(20  )	No	change_hist	4
change_cd	char(1   )	No	change_hist	5
create_zdt	datetime	No	change_hist	6
change_zdt	datetime	No	change_hist	7
keys	varchar(1024)	No	change_hist	8

change_new_hist / change_old_hist:

Column name	Type	Null	Table	Num
change_id	ubigint	No	change_old_hist	1
column_id	smallint	No	change_old_hist	2
value	varchar(1934)	Yes	change_old_hist	3
t_value	text	Yes	change_old_hist	4
u_value	univarchar	Yes	change_old_hist	5
change_zdt	datetime	No	change_old_hist	6

一个changeset(_id) 对应多个change(_id), 一个change可以有多个change_new/old

想查询一个changeset_id的每个状态(open,close,export)的时间和这个changeset 有多少个change以及每个change有多少个change_new/old

create view cs_hist_each_status_my
as
select t1.changeset_id
, t1.username
, t1.applname
, t1.create_zdt as create_zdt
, t2.change_zdt as close_zdt
, t3.change_zdt as export_zdt
, t4.cg_amount
, case when t5.cg_new_amount is null then 0 else t5.cg_new_amount end as cg_new_amount
, case when t6.cg_old_amount is null then 0 else t6.cg_old_amount end as
cg_old_amount
from changeset_history t1
inner join changeset_history t2
    on t1.changeset_id = t2.changeset_id
inner join changeset_history t3
    on t1.changeset_id = t3.changeset_id
left join (select changeset_id, count(change_id) cg_amount from change_hist group by changeset_id) t4
    on t1.changeset_id = t4.changeset_id
left join (select changeset_id, count(n2.change_id) cg_new_amount from change_hist n1, change_new_hist n2 where n1.change_id = n2.change_id group by changeset_id) t5
    on t1.changeset_id = t5.changeset_id
left join (select changeset_id, count(o2.change_id) cg_old_amount from change_hist o1, change_old_hist o2 where o1.change_id = o2.change_id group by changeset_id) t6
    on t1.changeset_id = t6.changeset_id
where t1.status = 'O'
and t2.status = 'C'
and t3.status = 'X'

Show Results:

[246] HXHALODB1.halo_govcorp.1> top1 cs_hist_each_status_my
changeset_id: 0x00040003588610c9
username: myang
applname: execSQL
create_zdt: Mar 28 2014  6:17AM
close_zdt: Mar 28 2014  6:17AM
export_zdt: Mar 28 2014  6:22AM
cg_amount: 2
cg_new_amount: 2
cg_old_amount: 2

(1 row affected)