如果可以的话这样岂不更好? select publisherid,mediumid,count(*) from restable group by publisherid,mediumid order by publisherid,mediumid 但是不行呀!可能只返回部分组合,并没有达到我的要求呀!: P01 M01 N1 P01 M03 N3 P02 M03 N6 P03 M02 N8 P03 M03 N9 还有他法吗?
select c.PublisheriD,c.MediumID,count(*) from (select * from (select distinct PublisheriD from resTable) a, (select distinct MediumID from resTable) b ) c left join resTable d on c.PublisheriD=d.PublisheriD and c.MediumID=d.MediumID group by c.PublisheriD,c.MediumID
select a.PublisheriD, a.MediumID,count(a.ResID) resTable a full jion resTable.b on a.ResID=b.ResID and a.NodeID=b.NodeID group by a.PublisheriD,b.MediumID应差不多吧,你调试调试。 可用isnull(a.PublisheriD,'指定一当值为NULL自己想要的缺省值')
select c.PublisheriD, c.MediumID, sum(case when d.MediumID is null then 0 else 1 end) from (select * from (select distinct PublisheriD from resTable) a, (select distinct MediumID from resTable) b ) c left join resTable d on c.PublisheriD=d.PublisheriD and c.MediumID=d.MediumID group by c.PublisheriD,c.MediumID
to j9988 这是什么意思? sum(case when d.MediumID is null then 0 else 1 end) 我要的是不去掉重复的ResID的数目?
select publisherid,mediumid,count(*)
from restable
group by publisherid,mediumid
order by publisherid,mediumid
但是不行呀!可能只返回部分组合,并没有达到我的要求呀!:
P01 M01 N1
P01 M03 N3
P02 M03 N6
P03 M02 N8
P03 M03 N9
还有他法吗?
(select * from
(select distinct PublisheriD from resTable) a,
(select distinct MediumID from resTable) b
) c left join resTable d
on c.PublisheriD=d.PublisheriD and c.MediumID=d.MediumID
group by c.PublisheriD,c.MediumID
resTable.b on a.ResID=b.ResID and a.NodeID=b.NodeID
group by a.PublisheriD,b.MediumID应差不多吧,你调试调试。
可用isnull(a.PublisheriD,'指定一当值为NULL自己想要的缺省值')
c.PublisheriD,
c.MediumID,
sum(case when d.MediumID is null then 0 else 1 end)
from
(select * from
(select distinct PublisheriD from resTable) a,
(select distinct MediumID from resTable) b
) c left join resTable d
on c.PublisheriD=d.PublisheriD and c.MediumID=d.MediumID
group by c.PublisheriD,c.MediumID
sum(case when d.MediumID is null then 0 else 1 end)
我要的是不去掉重复的ResID的数目?
不是去掉重复的ResID的数目.
left join 的结果,如果原表没有的项目,它会是NULL值,如:
c.PublisheriD c.MediumID d.PublisheriD d.MediumID
P01 M01 P01 M01
P01 M02 NULL NULL
P01 M03 P01 M03
P01 M03 P01 M03
让NULL值为0
结果:
c.PublisheriD c.MediumID
P01 M01 1
P01 M02 0
P01 M03 2