在sql查询的行中隐藏0 [英] hide 0 in rows of sql query
本文介绍了在sql查询的行中隐藏0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在sql中有表
samiksha sharad sac
0 10 0
1 0 0
我只想要1行而不是2行,我想隐藏0。
谢谢提前查询。
解决方案
选择 *,row_number()< span class =code-keyword> over ( order by sac asc ) as rowid into #temp from sql table
select * 来自 #temp 其中 rowid 不 中的class =code-keyword>( 1 )
现在试试吧
如果你只想要1.行
SELECT samiksha,sharad ,sac
FROM TableName
WHERE sharad<> 0 AND sac<> 0
或
如果你只想要2.行
SELECT samiksha,sharad,sac
FROM TableName
WHERE samiksha<> 0
DECLARE @ tmp TABLE (samiksha INT ,sharad INT ,sac INT )
INSERT INTO @ tmp (samiksha,sharad,sac)
VALUES ( 0 , 10 , 0 ),( 1 , 0 , 0 )
SELECT samiksha,sharad,sac
FROM @ tmp
WHERE sharad<> 0 AND sac<> 0
SELECT samiksha,sharad,sac
FROM @ tmp
WHERE samiksha< > 0
SELECT SUM(samiksha) AS samiksha,SUM(sharad) AS sharad,SUM(sac) AS sac
FROM @ tmp
返回值:
1.查询
(无)
2.查询
1 0 0
3.查询
1 10 0
[/ EDIT]
将所有零更新为null并试试这个
选择coalesce(samiksha,sharad,sac)作为samiksha,coalesce(samiksha,sharad,sac)作为sharad,coalesce(samiksha,sharad,sac)作为桌子上的囊
如果适合你的话,接受它。
I have table in sql
samiksha sharad sac 0 10 0 1 0 0
I want only 1 row instead of 2 rows, i want to hide 0 .
thanks in advance for query.解决方案select * ,row_number() over(order by sac asc) as rowid into #temp from sql table select * from #temp where rowid not in(1)
now try it
If you want only 1. row
SELECT samiksha, sharad, sac FROM TableName WHERE sharad <> 0 AND sac <> 0
or
if you want only 2. row
SELECT samiksha, sharad, sac FROM TableName WHERE samiksha <> 0
[EDIT]
DECLARE @tmp TABLE (samiksha INT, sharad INT, sac INT) INSERT INTO @tmp (samiksha,sharad,sac) VALUES(0, 10, 0), (1, 0, 0) SELECT samiksha, sharad, sac FROM @tmp WHERE sharad <> 0 AND sac <> 0 SELECT samiksha, sharad, sac FROM @tmp WHERE samiksha <> 0 SELECT SUM(samiksha) AS samiksha, SUM(sharad) AS sharad, SUM(sac) AS sac FROM @tmp
Returned values:
1. query
(none)
2. query
1 0 0
3. query
1 10 0
[/EDIT]
update all zero to null and try this
select coalesce(samiksha,sharad,sac) as samiksha , coalesce(samiksha,sharad,sac) as sharad ,coalesce(samiksha,sharad,sac) as sac from table
accept it if it works for you.
这篇关于在sql查询的行中隐藏0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文