例如有一张表 a 字段有
id score
1 80
2 90
3 100
我要给予一个查询条件 select * from a where id in (1,2,3,4,5)
能否显示的结果为
1 80
2 90
3 100
4 0
5 0
表中不存在的也显示出来,分数默认为0
谢谢
要实现这种情况有哪些方法?程序或sql都行.
------解决方案--------------------
- SQL code
;with cte as( select 1 as row union select 2 union select 3 union select 4 union select 5)select a.row,isnull(b.score,0) scorefrom cte a left join tb b on a.row = b.id
------解决方案--------------------
select b.id,isnull(a.score,0) as score
from
(select 1 as id union select 2 union select 3 union select 4 union select 5) b
left join a on a.id=b.id
------解决方案--------------------
- SQL code
create table tb(id int,score int)insert into tb select 1,80insert into tb select 2,90insert into tb select 3,100goselect a.number,isnull(b.score ,0)scorefrom master..spt_values a left join tb b on a.number=b.idwhere type='p' and a.number in(1,2,3,4,5)/*number score----------- -----------1 802 903 1004 05 0(5 行受影响)*/godrop table tb
------解决方案--------------------
- SQL code
IF NOT OBJECT_ID('tb') IS NULL DROP TABLE tbGoCREATE TABLE tb ( [id] INT, [score] INT )INSERT tb SELECT 1 , 80 UNION ALL SELECT 2 , 90 UNION ALL SELECT 3 , 100GoSELECT a.number , ISNULL(b.[score], 0) [score]FROM master.dbo.spt_values a LEFT JOIN dbo.TB b ON a.number = b.IDWHERE type = 'P' AND number IN ( 1, 2, 3, 4, 5 ) /* number score----------- -----------1 802 903 1004 05 0 */
------解决方案--------------------
- SQL code
select * from a right join (select 1 as id union select 2 union select 3 union select 4 union select 5) bon a.id =b.id
------解决方案--------------------
------解决方案--------------------
- SQL code
if object_id('tb','U') is not null drop table tbgocreate table tb( id int, score int)goinsert into tbselect 1,80 union allselect 2,90 union allselect 3,100goselect number,isnull(score,0) from master..spt_values a left join tb b on a.number=b.id where type='p' and number between 1 and 5go/*number ----------- -----------1 802 903 1004 05 0(5 行受影响)*/