This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / SQL UNION query查找满足条件的次数。一个ID可以有多项记录。记录可以在N个fields中的任意一个。相当于满spreadsheet 扫描，输出出现了多少次。
SELECT ID, Count(Column1) AS A FROM Table WHERE [condition] GROUP BY ID
UNION ALL
SELECT ID, Count(Column2) AS A FROM Table WHERE [condition]
GROUP BY ID
UNION ALL ......
这样下来每一列就出现一个总数，变成了新的一行。如果有三列，同一个ID就有三项汇总记录。我是经过再一次汇总得到了最后的总数。
用一个query可以办到吗？
-23456789(大白呼);
2005-6-28
{449}
(#2370843@0)
-
Try this...SELECT ID,sum_c1,sum_c2,sum_c3 (sum_c1+sum_c2+sum_c3) AS sum_all from(
SELECT ID, Count(Column1) AS sum_c1,null,null, null FROM Table WHERE [condition] GROUP BY ID
UNION ALL
SELECT ID, null,Count(Column2) AS sum_c2,null, null FROM Table WHERE [condition]
GROUP BY ID
UNION ALL
SELECT ID, null, null, Count(Column3) AS sum_c3, null FROM Table WHERE [condition]
GROUP BY ID)
-e_dragon(e_dragon);
2005-6-28
{380}
(#2370854@0)
-
The final result shall be 1 sum only, not 3.
-23456789(大白呼);
2005-6-29
(#2371352@0)
-
try it,pl/sqlSELECT id NVL(A.A,NVL(B.A,NVL(C.A...))),NVL(A.B,NVL(B.B,NVL(C,B...))).....
FROM (SELECT id Count(Column1) A, NULL B,... FROM Table WHERE [condition] GROUP BY ID ) A,
(SELECT id, NULL A, Count(Column2) B,... FROM Table WHERE [condition] GROUP BY ID ) B,
.
.
.
.
.
-cockle(cockle);
2005-6-29
{283}
(#2371568@0)
-
SELECT ID, Count(*) FROM Table GROUP BY ID
-schen(睹往睹来);
2005-6-29
(#2372374@0)
-
totally, totally, totally wrong. every column has its own condition to be met.
-23456789(大白呼);
2005-6-29
(#2372443@0)
-
如果你只想得到一个总和的话, 我给你的SQL概念是可行的, 也许类型什么的会报错. 我以前写过这样的查询. 或者你把数据表和想要的输出写出来, 我再给你写一个SQL语句
-e_dragon(e_dragon);
2005-6-29
(#2372522@0)
-
if u r using Oracle, try to use decode funtion to reach the result you need:select sum(decode(c1,'111',1,0)+decode(c2,'111',1,0)+decode(c3,'111',1,0))
from table
-zhaoguan(昭关);
2005-6-29
{92}
(#2372529@0)
-
..select count(*) from TB where condition1
UNION ALL
select count(*) from TB where condition2
......
UNION ALL
select count(*) from TB where conditionN
compute SUM(Count(*))
-zxcvb(朝天椒);
2005-6-29
{177}
(#2372613@0)
-
...please revise it to GROUP BY ID
-23456789(大白呼);
2005-6-29
{31}
(#2372658@0)
-
business levelselect id, count(*) from TB
group by id
having condition1
UNION ALL
select id, count(*) from TB
group by id
having condition2
......
UNION ALL
select id, count(*) from TB group by id
having conditionN
compute SUM(Count(*))
-zxcvb(朝天椒);
2005-6-29
{237}
(#2372672@0)
-
business level reply3x
by the way, why having condition? having is on count(*). the condition has to be on the original value, not aggrevated sum.
-23456789(大白呼);
2005-6-29
{129}
(#2372679@0)
-
1. 用了group by 就不能用where, having在这里代替where
2. count(*)和count(field_name)结果是一样的，因为后面要用comput count(*)所以...3. 你要求从table的每个field去找你要的结果，比如说你要找QA在你的table里出现了几次，你的table 有a,b,c,d4个fields, 那么第一个select的having 后就是a='QA', 第2个select的having 后就是b='QA', 。。。
-zxcvb(朝天椒);
2005-6-29
{195}
(#2372741@0)
-
能用where就尽量不用having，having的效率要比where的效率慢很多的说。
-hard20(hard20);
2005-6-29
(#2372885@0)