Union ALL读取空行
Union ALL读取空行
提问于 2012-07-11 20:34:32
我有两个连接了union All的查询。
SELECT select 'Finished' AS Status,amount AS amount,units As Date
from table1 WHERE Pdate > cdate AND name =@name
UNION ALL
SELECT select 'Live' AS Live,amount,units
from table1 Where Pdate = cdate And name =@name结果
Status amount units
Finished 100 20
Live 200 10当两个查询中的任何一个读取空集时，我只得到一行，如果两个查询都读取空集，则我没有行
那么我怎么才能得到这样的结果呢？
Status amount Units
Finished 100 20
Live 0 0或
Status amount Units
Finished 0 0
Live 200 10或
Status amount Units
Finished 0 0
Live 0 0谢谢。
回答 3
Stack Overflow用户
发布于 2012-07-11 20:39:41
我认为你可以使用sum来做这件事？如果在没有行的情况下sum不返回0，则替换为Coalesce(sum(amount), 0) as amount
SELECT select 'Finished' AS Status,sum(amount) AS amount, sum(units) As Unit
from table1 WHERE Pdate > cdate AND name =@name
UNION ALL
SELECT select 'Live' AS Status, sum(amount) as amount, sum(units) as Unit
from table1 Where Pdate = cdate And name =@name如果你不想对结果求和，那么只需要合并就行了？coalesce(amount, 0) As amount等等。
Stack Overflow用户
发布于 2012-07-11 21:23:13
我只想指出，您的查询是不必要的复杂，具有嵌套的selects和union all。编写查询的更好方法是：
select (case when pdate > cdate then 'Finished' else 'Live' end) AS Status,
amount AS amount, units As Date
from table1
WHERE Pdate >= cdate AND name = @name该查询不会产生您想要的结果，因为它只产生有数据的行。
获得额外行的一种方法是增加原始数据，然后检查是否需要它。
select status, amount, units as Date
from (select Status, amount, units,
row_number() over (partition by status order by amount desc, units desc) as seqnum
from (select (case when pdate > cdate then 'Finished' else 'Live' end) AS Status,
amount, units, name
from table1
WHERE Pdate >= cdate AND name = @name
) union all
(select 'Finished', 0, 0, @name
) union all
(select 'Live', 0, 0, @name
)
) t
where (amount > 0 or units > 0) or
(seqnum = 1)这会添加您想要的额外行。然后它会枚举它们，所以它们将在任何序列中排在最后。它们将被忽略，除非它们是序列中的第一个。
Stack Overflow用户
发布于 2012-07-14 21:44:24
试试这样的东西
with stscte as
(
select 'Finished' as status
union all
select 'Live'
),
datacte
as(
select 'Finished' AS Status,amount AS amount,units As Date
from table1 WHERE Pdate > cdate AND name =@name
UNION ALL
select 'Live' ,amount,units
from table1 Where Pdate = cdate And name =@name
)
select sc.status,isnull(dc.amount,0) as amount,isnull(dc.unit,0) as unit
from stscte sc left join datacte dc
on sc.status = dc.status页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11432776
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号