我有一个会计记账表。sc_tt_dealer_balance。
关键字段有 1:客户编号dealer_no, 2:记账日期charge_date, 3:单号bill_no, 4:余额balance。我现在可以查询某一个客户下单号重复的数据。
我用了分析函数:select * from (
select db.*, count(db.bill_no) over (partition by db.bill_no) cnt
from sc_tt_dealer_balance db
where 1=1
and db.dealer_no like '851403%'
)
where cnt > 1;这样可以查询出851403客户下单号相同的数据,现在我有一个需求,数据中有客户编号,记账日期,单号,余额都相同的数据,这个我怎么查出数据呢?
求sql语句
我这样写报错
select * from (
select db.*, count(db.bill_no, db.dealer_no, db.charge_date, db.balance) over (partition by db.bill_no, db.dealer_no, db.charge_date, db.balance) cnt
from sc_tt_dealer_balance db
where 1=1
--and db.dealer_no like '851403%'
)
where cnt > 1;
关键字段有 1:客户编号dealer_no, 2:记账日期charge_date, 3:单号bill_no, 4:余额balance。我现在可以查询某一个客户下单号重复的数据。
我用了分析函数:select * from (
select db.*, count(db.bill_no) over (partition by db.bill_no) cnt
from sc_tt_dealer_balance db
where 1=1
and db.dealer_no like '851403%'
)
where cnt > 1;这样可以查询出851403客户下单号相同的数据,现在我有一个需求,数据中有客户编号,记账日期,单号,余额都相同的数据,这个我怎么查出数据呢?
求sql语句
我这样写报错
select * from (
select db.*, count(db.bill_no, db.dealer_no, db.charge_date, db.balance) over (partition by db.bill_no, db.dealer_no, db.charge_date, db.balance) cnt
from sc_tt_dealer_balance db
where 1=1
--and db.dealer_no like '851403%'
)
where cnt > 1;
select * from (
select db.*, count(*) cnt
from sc_tt_dealer_balance db
where 1=1
group by db.bill_no, db.dealer_no, db.charge_date, db.balance
where cnt > 1;这样应该可以的
这个报错的:不是group by 语句。
select *
from sc_tt_dealer_balance t
where (t.dealer_no, t.charge_date,t.bill_no,t.balance) in
(select dealer_no, charge_date,bill_no,balance
from sc_tt_dealer_balance
group by dealer_no, charge_date,bill_no,balance
having count(*) > 1)
select db.*, count(*) cnt
from sc_tt_dealer_balance db
where 1=1
group by db.bill_no, db.dealer_no, db.charge_date, db.balance)
where cnt > 1;少了右括号