On the select () returns the result in question-CodePudding

I ask if the results are displayed in, does not exist, it returns 0.
For example:
Select * from table1 where id in (1, 2, 3, 4)
In fact database only 2 data for id=1, id=2
How will the results to display
Id value
1 value1
2 value2
3 0
4 0

CodePudding user response:

 
Select id=Anderson d, 
value=https://bbs.csdn.net/topics/isnull (b.v alue, 0) 
The from (select id=1 union all 
Union all select id=2 
Select id=3 union all 
Select id=4) a 
Left the join (select * 
The from table1 
Where id in (1, 2, 3, 4)) b on Anderson, d=b.i d

CodePudding user response:

The

reference 1/f, started the first reply:

 
Select id=Anderson d, 
value=https://bbs.csdn.net/topics/isnull (b.v alue, 0) 
The from (select id=1 union all 
Union all select id=2 
Select id=3 union all 
Select id=4) a 
Left the join (select * 
The from table1 
Where id in (1, 2, 3, 4)) b on Anderson, d=b.i d

Tried not to add the time after the group or is it just 1 the other does not display the data

CodePudding user response:

 
The SELECT ISNULL (Anderson, d, b.n umber), ISNULL (Dr. Alue, 0) FROM table1 a RIGHT JOIN (

SELECT the number FROM the master. The dbo. Spt_values WHERE [type]='P' AND number (1, 2, 3, 4)) in b 
ON Anderson, d=b.n umber

This id data word table you can build yourself, own the only support up to 2047