第一个表的DB2 SQL COUNT()结果 [英] DB2 SQL COUNT() result from first table
问题描述
我之前发布了一些内容,但没有正确解释我要完成的工作.我正在第二次尝试.
I posted something earlier and did not explain properly what I am trying to accomplish. I am trying a second time.
我想从两个不同的表中返回字段值,但只希望该表的计数.在这种情况下,@ SOP表.现在,它给了我@SOPREV表的总数.
I want to return field value from two different table but want the count for only of the table. In this case the @SOP table. Right now it gives me total count of the @SOPREV table.
SELECT A.SOPSEQ, A.SOPNBR, B.REVUSR2 , COUNT(A.SOPSEQ) OVER() AS AAA
FROM @SOP A
INNER JOIN @SOPREV B on B.SOPSEQ = A.SOPSEQ
WHERE UPPER(A.SOPSTS) = 'IN REVIEW' AND UPPER(B.REVSTS) = 'IN REVIEW'
GROUP BY A.SOPSEQ, A.SOPNBR, B.REVUSR2
ORDER BY A.SOPNBR, A.SOPREV
OFFSET (:StartingRow - 1) * :NbrOfRows ROWS
FETCH NEXT :NbrOfRows + 1 ROWS ONLY;
表@SOP:
|SOPSEQ |SOPNBR |SOPSTS |
|111 |123 |Review |
|222 |456 |Review |
|333 |789 |Review |
表@SOPREV:
|SOPSEQ |REVUSR2 |
|111 |Mark |
|111 |John |
|333 |Erik |
推荐答案
我不确定您到底想要什么,但是如果您只想从第一个表中获取计数,则可以使用 COUNT(DISTINCT列)
,如下所示:
I'm not entirely sure about what exactly you want, but if you want the count from the first table only, you can get it using COUNT(DISTINCT column)
as shown below:
select
a.sopseq,
a.sopnbr,
b.revusr2,
count(distinct a.sopseq) as aaa
from sop a
inner join soprev b on b.sopseq = a.sopseq
where upper(a.sopsts) = 'IN REVIEW' and upper(b.revsts) = 'IN REVIEW'
group by a.sopseq, a.sopnbr, b.revusr2
order by a.sopnbr, a.sopseq
结果:
SOPSEQ SOPNBR REVUSR2 AAA
------ ------ ------- ---
111 123 Mark 1
111 123 john 1
333 789 Erik 1
作为参考,我使用的数据脚本是:
For reference, the data script I used is:
create table sop (
sopseq int,
sopnbr int,
sopsts varchar(10)
);
insert into sop (sopseq, sopnbr, sopsts) values
(111, 123, 'In Review'),
(222, 456, 'In Review'),
(333, 789, 'In Review');
create table soprev (
sopseq int,
revusr2 varchar(10),
revsts varchar(10)
);
insert into soprev (sopseq, revusr2, revsts) values
(111, 'Mark', 'In Review'),
(111, 'john', 'In Review'),
(333, 'Erik', 'In Review');
这篇关于第一个表的DB2 SQL COUNT()结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!