create table z1
(
id varchar(10),
success int
)create table z2
(
id varchar(10)
)insert into z2 values('1')
insert into z2 values('2')
insert into z2 values('3')
insert into z2 values('4')
insert into z2 values('5')insert into z1 values('1',1)
insert into z1 values('2',1)
insert into z1 values('1',1)
insert into z1 values('5',1)
insert into z1 values('1',1)select * from z1
select * from z2select id,sum(success) as total from z1
group by id
这样查显示出来的结果是:
id total
1 3
2 1
5 1我要显示这样的结果该如何写:
id total
1 3
2 1
5 1
3 0
4 0
即把z1表中没有纪录,但是在z2表中存在的id号显示总纪录为0
(
id varchar(10),
success int
)create table z2
(
id varchar(10)
)insert into z2 values('1')
insert into z2 values('2')
insert into z2 values('3')
insert into z2 values('4')
insert into z2 values('5')insert into z1 values('1',1)
insert into z1 values('2',1)
insert into z1 values('1',1)
insert into z1 values('5',1)
insert into z1 values('1',1)select * from z1
select * from z2select id,sum(success) as total from z1
group by id
这样查显示出来的结果是:
id total
1 3
2 1
5 1我要显示这样的结果该如何写:
id total
1 3
2 1
5 1
3 0
4 0
即把z1表中没有纪录,但是在z2表中存在的id号显示总纪录为0
解决方案 »
- 请问这条SQL语句怎么写
- 一个查询问题.(考虑性能)
- 我这个查询为什么不对呢,该怎么写
- 复杂问题,一个有Instead of 触发器的View Inner Join上其他Table或者View 进行Update 或者Delete操作时,SQL禁止操作,如何解决,有高人指路吗?
- 用SELECT datepart(dw,getdate())得到的星期天数总比中国的的差一?怎么解决?
- 多表查询
- 两表合并问题
- 高手帮忙看看,我出啥问题了?!
- 在MSSQL中有什么办法实现逐条记录修改
- 【求助】有没有方法根据一个表的字段,统一更新其他表的字段?
- 不小心在注册表中把sql server驱动删了,怎么办?
- 求一sql语句,100分
select z2.id ,isnull(count(z1.success),0) from z1 right join z2 on z1.id=z2.id
group by z2.id
order by isnull(count(z1.success),0) desc
---------------------------
1 3
2 1
5 1
3 0
4 0
(
id varchar(10),
success int
)create table z2
(
id varchar(10)
)insert into z2 values('1')
insert into z2 values('2')
insert into z2 values('3')
insert into z2 values('4')
insert into z2 values('5')insert into z1 values('1',1)
insert into z1 values('2',1)
insert into z1 values('1',1)
insert into z1 values('5',1)
insert into z1 values('1',1)select * from z1
select * from z2
go
select z2.id ,isnull(count(z1.success),0) from z1 right join z2 on z1.id=z2.id
group by z2.id
order by isnull(count(z1.success),0) desc
drop table z1,z2
go
/*id
---------- -----------
1 3
2 1
5 1
3 0
4 0*/
(
id varchar(10),
success int
)create table z2
(
id varchar(10)
)insert into z2 values('1')
insert into z2 values('2')
insert into z2 values('3')
insert into z2 values('4')
insert into z2 values('5')insert into z1 values('1',1)
insert into z1 values('2',1)
insert into z1 values('1',1)
insert into z1 values('5',1)
insert into z1 values('1',1)
select z2.id,isnull(sum(z1.success),0) as total from z2 left join z1 on z1.id = z2.id
group by z2.id
id total
---------- -----------
1 3
2 1
3 0
4 0
5 1