语句如下
select distinct b.djbh,b.xgdjbh,a.s_fdbs,t_fdbs as fdbs,a.rq,b.spid
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and xgdjbh like 'CQH%' and c.duifbsh='ZDA' and s_fdbs='ZDA'我想按日期a.rq(日期)和t_fdbs(门店)来查询出来spid的值是唯一的,意思就是每一天的每个门店(t_fdbs)的spid是不可以重复的。而时间段2010-09-01到2010-09-30这个时间段spid是可以重复的。而我这个写法实现不了,请给位高手帮帮忙吧。
select distinct b.djbh,b.xgdjbh,a.s_fdbs,t_fdbs as fdbs,a.rq,b.spid
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and xgdjbh like 'CQH%' and c.duifbsh='ZDA' and s_fdbs='ZDA'我想按日期a.rq(日期)和t_fdbs(门店)来查询出来spid的值是唯一的,意思就是每一天的每个门店(t_fdbs)的spid是不可以重复的。而时间段2010-09-01到2010-09-30这个时间段spid是可以重复的。而我这个写法实现不了,请给位高手帮帮忙吧。
你贴出表结构,测试数据,以及你期望的结果。
SELECT DISTINCT b.djbh, b.xgdjbh, a.s_fdbs, t_fdbs AS fdbs, a.rq, b.spid
FROM zphz_kp AS a CROSS JOIN
zpmx_kp AS b CROSS JOIN
qinghdjhz AS c
WHERE (a.djbh = b.djbh) AND (b.xgdjbh = c.djbh)
AND (a.rq BETWEEN '2010-09-01' AND '2010-09-30')
AND (xgdjbh LIKE 'CQH%') AND (c.duifbsh = 'ZDA')
AND (s_fdbs = 'ZDA')
having count(...)>1
with tbl as (
select distinct b.djbh,b.xgdjbh,a.s_fdbs,t_fdbs as fdbs,a.rq,b.spid
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh
and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and left(xgdjbh,3) = 'CQH'
and c.duifbsh='ZDA'
and a.s_fdbs='ZDA'
)
select *
from tbl a
where not exists (
select *
from tbl b
where a.spid = b.spid and a.rq > b.rq )
having count(..)>1
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and xgdjbh like 'CQH%' and c.duifbsh='ZDA' and s_fdbs='ZDA'
group by a.rq,t_fdbs,spid having count(1) = 1
select m.* from
(
select b.djbh,b.xgdjbh,a.s_fdbs,t_fdbs as fdbs,a.rq,b.spid
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and xgdjbh like 'CQH%' and c.duifbsh='ZDA' and s_fdbs='ZDA'
) m where exists(select 1 from
(
select a.rq,t_fdbs,spid
from zphz_kp a,zpmx_kp b,qinghdjhz c
where a.djbh=b.djbh and b.xgdjbh=c.djbh and a.rq>='2010-09-01' and a.rq<='2010-09-30'
and xgdjbh like 'CQH%' and c.duifbsh='ZDA' and s_fdbs='ZDA'
group by a.rq,t_fdbs,spid having count(1) = 1
) n
where m.rq = n.rq and m.t_fdbs = n.t_fdbs and m.spid = n.spid
)