select PublisheriD ,MediumID,count(*) from
(select distinct PublisheriD ,MediumID from resTable) b
group by PublisheriD ,MediumID
(select distinct PublisheriD ,MediumID from resTable) b
group by PublisheriD ,MediumID
解决方案 »
- 链接服务器 "(null)" 的 OLE DB 访问接口
- 高分請求大師:關於簡體,繁體存入SQL內是處理為Unicode(UTF-8)類形 50分急急!!!!
- sql 2005 数据库没备份 如何恢复 update 之前的数据
- Sql Server 2008 配置远程连接问题
- 两表连接更新问题
- 如何禁止灾难性删除?
- 如何一次插入多条新记录?
- "select * from db where dm='000001' and scbz<>1 order by fsrq desc"为何会出错?
- ◆◆◆◆我想显示出pubs库里所有用户表名称◆◆◆◆
- ORDER BY 非得和 TOP 一起用吗?哪位大哥给我指点迷津???
- 如果你们是大虾---->就进来!急!!
- 求SQL语句!
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