SQL如何按日期判断一列值中的唯一值

sky1216520 2010-09-23 10:20:45
语句如下
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是可以重复的。而我这个写法实现不了,请给位高手帮帮忙吧。
...全文
150 6 点赞 打赏 收藏 举报
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Rotel-刘志东 2010-09-23
group by ...
having count(..)>1
  • 打赏
  • 举报
回复
claro 2010-09-23
--try
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 )
  • 打赏
  • 举报
回复
喜-喜 2010-09-23
[Quote=引用 2 楼 rucypli 的回复:]
group by ...
having count(...)>1
[/Quote]

up..
  • 打赏
  • 举报
回复
rucypli 2010-09-23
group by ...
having count(...)>1
  • 打赏
  • 举报
回复
zsh0809 2010-09-23
帮你整理下SQL,不过没看明白你的目的。
你贴出表结构,测试数据,以及你期望的结果。
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')
  • 打赏
  • 举报
回复
dawugui 2010-09-23
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



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
)
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-23 10:20
社区公告
暂无公告