SELECT
TableA.XXX AS XXX,
TableB.YYY AS YYY,
TableA.ZZZ AS ZZZ
....
FROM
TableA,
TableB
WHERE
TableA.MMM = TableB.NNN AND
TableA.PPP = TableB.QQQ
....Now I want to get not only the results,
but also the count of results that have the same XXX and YYY value.
Can I just do this by verifying the sql statement showed above?
TableA.XXX AS XXX,
TableB.YYY AS YYY,
TableA.ZZZ AS ZZZ
....
FROM
TableA,
TableB
WHERE
TableA.MMM = TableB.NNN AND
TableA.PPP = TableB.QQQ
....Now I want to get not only the results,
but also the count of results that have the same XXX and YYY value.
Can I just do this by verifying the sql statement showed above?
sorry but i have no Chinese IME installed on the computer.
but my broswer supports Chinese well so that's OK for you to speak Chinese.
i assure you that i can read it...(-_-)TABLE1's definition:ID CHR_A CHR_B (and so on, CHR_C, etc.)
-------------------------------
1 X P
2 X Q
3 Y P
4 Y P
5 Y Q
6 Z PTABLE2's definition:ID NUM_A (and so on... NUM_B, NUM_C, etc.)
---------------
1 300
2 300
3 500
4 500
5 500
6 500Now as i execute the sql statement below,SELECT
TABLE1.CHR_A,
TABLE2.NUM_A,
TABLE1.CHR_B,
--(and so on....
-- TABLE2.NUM_B,
-- TABLE1.CHR_C,
-- etc.)FROM TABLE1, TABLE2
WHERE
TABLE1.ID = TABLE2.ID
the result i got is like this:
CHR_A NUM_A CHR_B ...
-------------------------------------------
X 300 P
X 300 Q
Y 500 P
Y 500 P
Y 500 Q
Z 500 P and now i want a new column named "CNT",
to show the count of rows that have the same value for CHR_A and NUM_A.
the result i'm expecting is like is:CHR_A NUM_A CHR_B CNT
--------------------------------------------
X 300 P 2
X 300 Q 2
Y 500 P 3
Y 500 P 3
Y 500 Q 3
Z 500 P 1
the real system is much more complicated.it seems i should use [GROUP BY] to do this,
but there are also many columns such as CHR_B that i don't want to apply [GROUP BY] on.
the question might be easy but i'm totally puzzled.
help, pls
but also the count of results that have the same XXX and YYY value.
Can I just do this by verifying the sql statement showed above?
我来翻译:
不仅仅想得到这个查询结果,还想知道同样的XXX,YYY的件数。是不是可以验证一下sql statement可以得到。答案:
不可能。
想得到这个查询结果的件数,可以得到。
想得到同样的XXX,YYY的件数,不通过其他语句,是做不到了。
就是在原来的结果集上面,想根据XXX和YYY字段来合计,得到一个件数合计。
thank u very much for the translation and your answer. can i 得到同样的XXX,YYY的件数 by modifying the sql statement?
假设这个结果集为CSelect CHR_A,NUM_A,CHR_B,(Select Count(*) FROM C C1 WHERE C1.CHR_A = C.CHR_A AND C1.NUM_A = C.NUM_A)
FROM C应该是要这个了。
Count函数可以使用over关键词。SELECT
TABLE1.CHR_A,
TABLE2.NUM_A,
TABLE1.CHR_B,
COUNT(*) OVER (partition by ABLE1.CHR_A,TABLE2.NUM_A, ) As CNT
FROM TABLE1, TABLE2
WHERE
TABLE1.ID = TABLE2.ID
不过,没有记错的话,旧的版本不支持。
that's exactly what i want.
but i hope to do all this in just ONE statement.
the answer would be... impossible?
>
>不过,没有记错的话,旧的版本不支持。 that really works! Thank u very much! :D no problem with Oracle 9i or above, right?
got it. thank you! :Dand thanks to all. :)