右链了2个表后,请问要如何去写去除重复数据的sql呢?
DISTINCT 和hvaing count都用过了,但是依然有重复数据
SELECT s.prodid,p.prodname,prodprice,
(select top 1 filename from product_photos where prodid=s.prodid order by adddate) as [filename],DTime,[site],0 from orderform o(nolock)
right join
order_sub s(nolock) on o.orderformid=s.orderfromid
right join
product p(nolock) on p.prodid=s.prodid
and
datediff(Day,DTime,getdate())<180
where
o.dhrnickname='aa'
order by o.dayTime DESC
DISTINCT 和hvaing count都用过了,但是依然有重复数据
SELECT s.prodid,p.prodname,prodprice,
(select top 1 filename from product_photos where prodid=s.prodid order by adddate) as [filename],DTime,[site],0 from orderform o(nolock)
right join
order_sub s(nolock) on o.orderformid=s.orderfromid
right join
product p(nolock) on p.prodid=s.prodid
and
datediff(Day,DTime,getdate())<180
where
o.dhrnickname='aa'
order by o.dayTime DESC
解决方案 »
- C# ado连接数据库
- 主问有没有MS-SQL Server的专业书籍呢?
- 表从sql中批量导入到excel中
- 麻烦高手进来看一下:允许触发器递归后(自调用),其中定义的游标名称如何避免重复? (根本就是同一个.)
- 学习中遇到一个问题!请教大家
- 求救,我把数据库B给合到数据库A里,但是B的表关系没了,怎么办,B里的表有很多主键,外键,索引等,到了A数据库全都没了。这样不行啊,必须有个简单的办法
- 查询出存储过程内循环(循环24小时)的数据
- 谁能帮我解决这个难题
- 存储过程中遇到的问题,帮个忙,谢谢!
- 新手的问题,但很着急:关于SQL SERVER2000客户端与服务器连接的问题
- 数据库日志中可以查看插入删除更新等操作的记录吗?
- SQL2008事务同步复制问题 请高人指点
是根据 s.proid 来判断是否为重复数据
SELECT
s.prodid,p.prodname,prodprice,
(select top 1 filename from product_photos where prodid=s.prodid order by adddate) as [filename],DTime,[site],0 from orderform o(nolock)
right join
order_sub s(nolock) on o.orderformid=s.orderfromid
right join
product p(nolock) on p.prodid=s.prodid
and
datediff(Day,DTime,getdate())<180
where
o.dhrnickname='aa'
and
s.proid=(select max(s.proid) from order_sub where orderformid=s.orderformid)
order by o.dayTime DESC
图。就是prodid 不能有重复的。
这个DTime 要最新的。
(select top 1 filename from product_photos where prodid=s.prodid order by adddate) as [filename],
max(DTime) as DTime,[site],0 from orderform o(nolock)
right join order_sub s(nolock) on o.orderformid=s.orderfromid
right join product p(nolock) on p.prodid=s.prodid
and datediff(Day,DTime,getdate())<180
where o.dhrnickname='aa'
group by s.prodid,p.prodname,prodprice,[filename],[site]
order by o.dayTime DESC 取最大的DTime用:max(DTime) as DTime,
取最小的DTime用:min(DTime) as DTime,
Dtime在哪个表?你只要显示每组数据中Dtime最大的记录是吗?
DTime在 order_sub
没错 就是按你说的那样显示。。
谢谢大大费心
应该是
同proid 的数据 只保留一条 Dtime 最大的
其他的重复proid 的数据去除掉
(
SELECT s.prodid, p.prodname, prodprice, (
SELECT TOP 1 FILENAME
FROM product_photos
WHERE prodid = s.prodid
ORDER BY adddate
) AS [filename], DTime, [site], 0 num,dayTime,
rn=ROW_NUMBER()OVER(PARTITION BY s.prodid ORDER BY DTime DESC)
FROM orderform o(NOLOCK)
RIGHT JOIN order_sub s(NOLOCK)
ON o.orderformid = s.orderfromid
RIGHT JOIN product p(NOLOCK)
ON p.prodid = s.prodid
AND DATEDIFF(DAY, DTime, GETDATE())<180
WHERE o.dhrnickname = 'aa'
)
SELECT prodid, prodname,prodprice,[filename],DTime,[site],num
FROM t
WHERE rn=1
ORDER BY dayTime DESC 先试下这个,看出来的结果是不是你要的
确实就是这个结果。。
但是用到while的话。
敢问一句。。性能会不会很差。
因为本身的数据量就灰常灰常的大。。