"但是每个公司最多只能选3个产品" 没注意这个条件,可以用分析函数row_number select top 7 * from (select ProdID,ProdName,CompID, ROW_NUMBER() over(partition by CompID order by ProdID) as rn from table1) where rn<=3 order by ProdID
select top 7 * from (select ProdID,ProdName,CompID, ROW_NUMBER() over(partition by CompID order by ProdID) as rn from table1) a where rn<=3 order by ProdID
oracle出身,总是忘了sql sever中需要起别名的,
create table #table1 (prodid int ,prodname varchar(10),compid int) insert #table1 select '1','车床','1' union all select '2','铣床','1' union all select '3','钻床','1' union all select '4','铣床','1' union all select '5','冲床','1' union all select '6','铣床','2' union all select '7','钻床','2' union all select '8','车床','2' union all select '9','铣床','3' union all select '10','钻床','3' union all select '11','铣床','3' union all select '12','冲床','3' select top 7 p.prodid,p.prodname,p.compid from (select *,ROW_NUMBER()over(partition by compid order by prodid ) as n from #table1 ) as p where p.n<4 order by prodid
CREATE TABLE #TABLE1 (PRODID INT ,PRODNAME VARCHAR(10),COMPID INT) INSERT #TABLE1 SELECT '1','车床','1' UNION ALL SELECT '2','铣床','1' UNION ALL SELECT '3','钻床','1' UNION ALL SELECT '4','铣床','1' UNION ALL SELECT '5','冲床','1' UNION ALL SELECT '6','铣床','2' UNION ALL SELECT '7','钻床','2' UNION ALL SELECT '8','车床','2' UNION ALL SELECT '9','铣床','3' UNION ALL SELECT '10','钻床','3' UNION ALL SELECT '11','铣床','3' UNION ALL SELECT '12','冲床','3'
SELECT TOP 7 * FROM (SELECT PRODID,PRODNAME,COMPID, ROW_NUMBER() OVER(PARTITION BY COMPID ORDER BY PRODID) AS RN FROM #TABLE1 ) A WHERE RN<=3 ORDER BY PRODIDDROP TABLE #TABLE1
用row_number() over(parition by compid order by proid) as r 进行分组排序就好啦
没注意这个条件,可以用分析函数row_number
select top 7 * from
(select ProdID,ProdName,CompID,
ROW_NUMBER() over(partition by CompID order by ProdID) as rn from table1)
where rn<=3
order by ProdID
select top 7 * from
(select ProdID,ProdName,CompID,
ROW_NUMBER() over(partition by CompID order by ProdID) as rn from table1) a
where rn<=3
order by ProdID
insert #table1
select '1','车床','1' union all
select '2','铣床','1' union all
select '3','钻床','1' union all
select '4','铣床','1' union all
select '5','冲床','1' union all
select '6','铣床','2' union all
select '7','钻床','2' union all
select '8','车床','2' union all
select '9','铣床','3' union all
select '10','钻床','3' union all
select '11','铣床','3' union all
select '12','冲床','3' select top 7 p.prodid,p.prodname,p.compid from (select *,ROW_NUMBER()over(partition by compid order by prodid ) as n
from #table1 ) as p
where p.n<4
order by prodid
CREATE TABLE #TABLE1 (PRODID INT ,PRODNAME VARCHAR(10),COMPID INT)
INSERT #TABLE1
SELECT '1','车床','1' UNION ALL
SELECT '2','铣床','1' UNION ALL
SELECT '3','钻床','1' UNION ALL
SELECT '4','铣床','1' UNION ALL
SELECT '5','冲床','1' UNION ALL
SELECT '6','铣床','2' UNION ALL
SELECT '7','钻床','2' UNION ALL
SELECT '8','车床','2' UNION ALL
SELECT '9','铣床','3' UNION ALL
SELECT '10','钻床','3' UNION ALL
SELECT '11','铣床','3' UNION ALL
SELECT '12','冲床','3'
SELECT TOP 7 * FROM
(SELECT PRODID,PRODNAME,COMPID,
ROW_NUMBER() OVER(PARTITION BY COMPID ORDER BY PRODID) AS RN FROM #TABLE1
) A
WHERE RN<=3
ORDER BY PRODIDDROP TABLE #TABLE1