select workdate,place,出现的次数
from 表A ,( select workdate,place,count(*) as 出现次数
from 表A
group by workdate,place ) B
where a.workdate=b.workdate and a.place=b.place
order by workdate,place
from 表A ,( select workdate,place,count(*) as 出现次数
from 表A
group by workdate,place ) B
where a.workdate=b.workdate and a.place=b.place
order by workdate,place
from (
select distinct PLACE from a
) as p cross join
(
select distinct WORKDATE from a
) as d
left join (
select WORKDATE,PLACE,sum(QTY) as QTY
from a
group by WORKDATE,PLACE
) as x
on d.WORKDATE=x.WORKDATE
and p.PLACE=x.PLACE
select aa.WORKDATE,aa.PLACE,出现次数=count(a.CID)
from(
select *
from(select WORKDATE from A group by WORKDATE)aa,
(select PLACE from A group by PLACE)bb
)aa left join A on aa.WORKDATE=a.WORKDATE and aa.PLACE=a.PLACE
group by aa.WORKDATE,aa.PLACE
order by aa.WORKDATE,aa.PLACE
create table A(CID int,WORKDATE varchar(10),PLACE varchar(10),QTY int)
insert A select 1,'2004-08-01','A',5
union all select 1,'2004-08-01','B',3
union all select 1,'2004-08-02','A',8
union all select 2,'2004-08-03','C',15
union all select 3,'2004-08-01','B',7
union all select 4,'2004-08-03','C',9
go--查询
select aa.WORKDATE,aa.PLACE,出现次数=count(a.CID)
from(
select *
from(select WORKDATE from A group by WORKDATE)aa,
(select PLACE from A group by PLACE)bb
)aa left join A on aa.WORKDATE=a.WORKDATE and aa.PLACE=a.PLACE
group by aa.WORKDATE,aa.PLACE
order by aa.WORKDATE,aa.PLACE
go--删除测试
drop table A/*--测试结果WORKDATE PLACE 出现次数
---------- ---------- -----------
2004-08-01 A 1
2004-08-01 B 2
2004-08-01 C 0
2004-08-02 A 1
2004-08-02 B 0
2004-08-02 C 0
2004-08-03 A 0
2004-08-03 B 0
2004-08-03 C 2(所影响的行数为 9 行)
--*/
create table zx (CID int ,WORKDATE char (10),PLACE char (1),QTY int)insert into zx
select 1,'2004-08-01','A',5
union
select 1,'2004-08-01','B',3
union
select 1,'2004-08-02','A',8
union
select 2,'2004-08-03','C',15
union
select 3,'2004-08-01','B',7
union
select 4,'2004-08-03','C',9
select
isnull (a.workdate,b.workdate) as workdate,
isnull (a.place,b.place) as place,
isnull (a.aa,0) as aa
from
(select workdate,place,count(*) as aa from zx group by workdate,place) a full join
(select a.*,b.* from
(select workdate from zx group by workdate ) a,
(select place from zx group by place) b) b
on a.workdate=b.workdate and a.place=b.place
order by isnull (a.workdate,b.workdate),isnull (a.place,b.place)