NUM_BUY COD_FACT COD_PAYM
1234 A 1
1235 A 1
1236 A 2
1237 B 1
1238 B 3
1239 B 3
我想查出同一cod_fact下cod_paym不同於大多數cod_paym的記錄
需要查出以下兩筆記錄
NUM_BUY COD_FACT COD_PAYM
1236 A 2
1237 B 1
1234 A 1
1235 A 1
1236 A 2
1237 B 1
1238 B 3
1239 B 3
我想查出同一cod_fact下cod_paym不同於大多數cod_paym的記錄
需要查出以下兩筆記錄
NUM_BUY COD_FACT COD_PAYM
1236 A 2
1237 B 1
from table_name
group by cod_fact
having count(cod_paym) < 2
1236 A 2
1237 B 1
你的COD_FACT好像不同吧?一个A,一个B????
--试试这个
select COD_FACT,COD_PAYM,max(NUM_BUY) group by COD_FACT,COD_PAYM having count(1)=1
NUM_BUY number(10),
COD_FACT varchar2(3),
COD_PAYM number(2)
);
insert into wiler values(1234,'A',1);
insert into wiler values(1235,'A',1);
insert into wiler values(1236,'A',2);
insert into wiler values(1237,'B',1);
insert into wiler values(1238,'B',3);
insert into wiler values(1239,'B',3);
commit;with tab_a as (
select cod_fact,cod_paym,count(*) num
from wiler
group by cod_fact,cod_paym
)
select c.* from
(
select cod_fact,min(num) min_n
from tab_a
group by cod_fact
) a,tab_a b,wiler c
where a.cod_fact=b.cod_fact and a.min_n=b.num
and b.cod_fact=c.cod_fact
and b.cod_paym=c.cod_paym
/
select COD_FACT,COD_PAYM
from table_name
group by cod_fact,COD_PAYM
having count(cod_paym) < 2
其他朋友的語句都有些錯誤
麻煩再幫忙了謝謝!
---------- ---------- ----------
1234 A 1
1235 A 1
1236 A 2
1237 B 1
1238 B 3
1239 B 36 rows selectedSQL>
SQL> with new_t as (
2 select cod_fact,cod_paym,count(*) cn from t
3 group by cod_fact,cod_paym
4 )
5 select t.num_buy, cod_fact, cod_paym
6 from t
7 where (cod_fact, cod_paym) not in
8 (select cod_fact, cod_paym
9 from new_t
10 where (cod_fact, cn) in
11 (select cod_fact, max(cn) cn from new_t group by cod_fact))
12 ; NUM_BUY COD_FACT COD_PAYM
---------- ---------- ----------
1236 A 2
1237 B 1
CREATE TABLE tx4(UM_BUY int,COD_FACT VARCHAR2(10), COD_PAYM INT );
INSERT INTO TX4 VALUES (1234, 'A', 1);
INSERT INTO TX4 VALUES (1235, 'A', 1);
INSERT INTO TX4 VALUES (1236, 'A', 2);
INSERT INTO TX4 VALUES (1237, 'B', 1);
INSERT INTO TX4 VALUES (1238, 'B', 3);
INSERT INTO TX4 VALUES (1239, 'B', 3);
COMMIT;
SELECT *
FROM TX4
WHERE (COD_FACT, COD_PAYM) IN
(SELECT COD_FACT, COD_PAYM
FROM ((SELECT X.*, ROW_NUMBER() OVER(ORDER BY CC) RN
FROM (SELECT COD_FACT, COD_PAYM, COUNT(*) CC
FROM TX4
GROUP BY COD_FACT, COD_PAYM) X))
WHERE RN <= 2);
输出:
UM_BUY COD_FACT COD_PAYM
1236 A 2
1237 B 1
select COD_FACT, COD_PAYM
from weiyi
group by COD_FACT,COD_PAYM
having count(COD_PAYM) < 2
)
select * from weiyi A where A.Cod_Fact in (
select B.COD_FACT from new_weiyi B where A.COD_FACT = B.COD_FACT
and A.COD_PAYM = B.COD_PAYM
);
FROM
(
SELECT COD_FACT, MIN(MINCNT) MINCNT FROM
(
SELECT UM_BUY, COD_FACT, COD_PAYM, COUNT(COD_FACT || COD_PAYM) OVER (PARTITION BY COD_FACT || COD_PAYM ORDER BY COD_FACT) MINCNT FROM TX4
) GROUP BY COD_FACT
) A,
(
SELECT UM_BUY, COD_FACT, COD_PAYM, COUNT(COD_FACT || COD_PAYM) OVER (PARTITION BY COD_FACT || COD_PAYM ORDER BY COD_FACT) MINCNT FROM TX4
) B
WHERE A.COD_FACT = B.COD_FACT AND A.MINCNT = B.MINCNT
SQL> SELECT *
2 FROM TABLE_NAME T1
3 WHERE NOT EXISTS (SELECT 1
4 FROM TABLE_NAME T2
5 WHERE T1.COD_FACT = T2.COD_FACT
6 AND T1.COD_PAYM = T2.COD_PAYM
7 AND T1.NUM_BUY <> T2.NUM_BUY); NUM_BUY COD_FACT COD_PAYM
---------- -------- ----------
1236 A 2
1237 B 1
from (
select cod_fact,cod_paym,rank() over (order by count(*) asc) xh
from wiler
group by cod_fact,cod_paym
) a,wiler b
where a.cod_fact=b.cod_fact
and a.cod_paym=b.cod_paym
and a.xh=1