34,575
社区成员
发帖
与我相关
我的任务
分享
DECLARE @x XML= CONVERT(XML,REPLICATE('<n>0</n>',20))
;with t(id, mishu ) as
(
select 1, 5 union all
select 2, 5.6 union all
select 3, 35 union all
select 4, 1.5
)
select isnull(b.id,c.id) AS id,isnull(b.mishu,0) AS mishu from (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS rn, f.value('.','int') id FROM @x.nodes('n') a(f))c
LEFT JOIN (SELECT ROW_NUMBER()OVER(ORDER BY id) AS rn,t.* FROM t) b ON c.rn=b.rn
id mishu
1 5.0
2 5.6
3 35.0
4 1.5
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
0 0.0
select mishu=isnull(b.mishu,0)
from (select number
from master.dbo.spt_values
where type='P' and number between 1 and 20) a
left join (select mishu,rn=row_number() over(order by getdate()) from t_table) b on a.number=b.rn
with t_table(id, mishu ) as
(
select 1, 5 union all
select 2, 5.6 union all
select 3, 35 union all
select 4, 1.5
)
select a.number, ISNULL(b.mishu, 0) from
(select number+1 number from master..spt_values where type='P' and number<20) a
left join t_table b on a.number=b.id
SELECT
*
INTO
#T_table
FROM
(
SELECT 1 AS ID,'5' AS mishu UNION ALL
SELECT 2 AS ID,'5.6' AS mishu UNION ALL
SELECT 3 AS ID,'35' AS mishu UNION ALL
SELECT 4 AS ID,'1.5' AS mishu
) AS A
CREATE TABLE #T (ID INT)
DECLARE @i INT = 1
WHILE @i <= 20
BEGIN
INSERT INTO #T VALUES (@i)
SET @i = @i + 1
END
SELECT #T.ID,#T_table.mishu,ISNULL(#T_table.mishu, '0') FROM #T LEFT JOIN #T_table ON #T.ID = #T_table.ID
DROP TABLE #T
select mishu=isnull(b.mishu,0)
from (select number
from master.dbo.spt_values
where type='P' and number between 1 and 20) a
left join (select mishu,rn=row_number() over(order by Id) from T_FinishJincang where RunNO='160710001') b on a.number=b.rn
order by a.number
use Tempdb
go
--> -->
declare @T table([mishu] FLOAT)
Insert @T
select 5 union all
select 5.6 union all
select 35 union all
select 1.5
Select [mishu] from @T
UNION ALL
SELECT TOP (20-(SELECT COUNT(*) FROM @T))0 FROM sys.columns
/*
mishu
5
5.6
35
1.5
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
*/
select mishu=isnull(b.mishu,0)
from (select number
from master.dbo.spt_values
where type='P' and number between 1 and 20) a
left join (select mishu,rn=row_number() over(order by Id) from T_FinishJincang where RunNO='160710001') b on a.number=b.rn
mishu
0.8
1.7
0
0
0
0
0.8
0.8
0
0
0
0
3.4
3.4
3.4
0
0
0
0
0
多次按执行案件,数字位置不一样
--------------------------------------------------------