多条件去重

[zgp0007]

select se.spromoid as 调价单号,sc.[value] as 店铺,st.goods_no as 货号/*,st.unitprice as 当前零售价*/ from SPromoEx se
left join SPromoExCustomer sc on sc.spromoid=se.spromoid
left join SPromoExSpType7 st on st.spromoid=se.spromoid
left join goods g on g.goods_no=st.goods_no
where 1=1 AND g.brand='BOSSSUNWEN' and se.cancel='0'
GROUP BY st.goods_no,sc.[value],se.spromoid/*,st.unitprice*/,st.goods_no
ORDER BY st.goods_no,sc.[value],se.spromoid desc

需要把店铺、货号重复的数据只保留单号最大的记录

[zgp0007]

请高人给指点一下吧小白，弄一天这个了

[一支糖包仔]

select max(se.spromoid) from( --你的sql ) a 可以用这种方式来取最大id

[一支糖包仔]

你多试试看，思路反正就是可以在外层包起来，然后再细查你需要的（最好在最外层再定义别名）。 我电脑没装数据库，不方便测试我打的是否正确。

[zgp0007]

引用 2 楼 一支糖包仔 的回复:

select max(se.spromoid) from(
--你的sql
) a
可以用这种方式来取最大id

谢谢，你的办法我试了，也不行，你讲的道理我明白，但还是得不到我想到的

[顺势而为1]

;with cte1
as (
		select se.spromoid,sc.[value],st.goods_no/*,st.unitprice as 当前零售价*/ 
		from SPromoEx se
			left join SPromoExCustomer sc on sc.spromoid=se.spromoid
			left join SPromoExSpType7 st on st.spromoid=se.spromoid
			left join goods g on g.goods_no=st.goods_no
		where 1=1 AND g.brand='BOSSSUNWEN' and se.cancel='0'
		GROUP BY st.goods_no,sc.[value],se.spromoid/*,st.unitprice*/,st.goods_no
	),
cte2 as (

           Select Row_ID=ROW_NUMBER() OVER (Partition By [value],goods_no Order By spromoid desc),*
		   From cte1 )

Select spromoid as 调价单号,[value] as 店铺,goods_no as 货号 
From cte2
Where Row_ID=1
ORDER BY goods_no,[value],spromoid desc 

[Hello World,]

group by 把调价单号去掉，用max来获取：

SELECT      MAX(se.spromoid) AS 调价单号,
            sc.[value] AS 店铺,
            st.goods_no AS 货号   /*,st.unitprice as 当前零售价*/
FROM        SPromoEx se
            LEFT JOIN SPromoExCustomer sc ON sc.spromoid = se.spromoid
            LEFT JOIN SPromoExSpType7 st ON st.spromoid  = se.spromoid
            LEFT JOIN goods g ON g.goods_no              = st.goods_no
WHERE       1 = 1 AND   g.brand = 'BOSSSUNWEN' AND  se.cancel = '0'
GROUP BY    st.goods_no,
            sc.[value]
ORDER BY    st.goods_no,
            sc.[value] DESC;

[小野马1209]

--模拟数据
declare @temp table(spromoid varchar(50),value varchar(50),goods varchar(50))
insert into @temp
select 'pgz0001013','B004','123456' UNION ALL
select 'pgz0001014','B007','123456' UNION ALL
select 'pgz0001018','B007','123456' 

--第一步，将查询的结果按店铺及货号分组,按调价单号倒序 
Select ROW_NUMBER() OVER (Partition By value,goods order by spromoid desc) as No,* from @temp

--第二步
SELECT * FROM (Select ROW_NUMBER() OVER (Partition By value,goods order by spromoid desc) as No,* from @temp) A
WHERE No=1

[幸福感奇差]

select * from SPromoEx where 调价单号 in (select max(调价单号) from SPromoEx group by 店铺, 货号)