SQL 查询添加备用空白记录 [英] SQL Query Add an Alternate Blank Records

问题描述

我正在使用以下查询来显示来自 SQL SERVER 的记录

I am using following Query to Display Records from SQL SERVER

select * from orders

目前此查询显示数据库中的所有 100 条记录，我需要的是显示 200 条记录而不是 100 条记录(因此备用空白记录很好)这可能吗?

Currently this query shows all 100 records in database, what i need is instead of 100 it should show 200 records ( so alternate blank records is fine ) It is possible can it be done ?

推荐答案

-- sample table
declare @Order table
( 
  orderid int,
  qty int
)

-- add some data
insert into @Order
select 1, 10 union all
select 2, 20 union all
select 3, 30

-- cross join the query against two rows       
select case D.N when 1 then O.orderid end as orderid,
       case D.N when 1 then O.qty end as qty
from @Order as O
  cross join (select 1 union all select 2) as D(N)
order by O.orderid, D.N  

结果:

orderid     qty
----------- -----------
1           10
NULL        NULL
2           20
NULL        NULL
3           30
NULL        NULL

这篇关于SQL 查询添加备用空白记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！