大家好，比如我有一张表：
- SQL code
declare @importantLead table ( custID int, amount decimal(18,0), ownerID int, deptID int )
然后，这里面的东西就是说，按照amount降序排列，每个owner都有好几个custID，现在是需要从上往下查找，比如找到前4行的amount占到总的60%，则认为这些是重要的客户，这时候统计下有多少个，只需要得到重要客户的个数。
请教下如何实现。
------解决方案--------------------
- SQL code
declare @importantLead table ( custID int, amount decimal(18,0), ownerID int, deptID int )insert @importantLeadselect 1,20,1,1 union allselect 1,24,2,1 union allselect 2,52,2,2 union allselect 3,96,1,3 union allselect 3,45,3,3 union allselect 3,96,3,3 union allselect 3,34,4,3 union allselect 3,18,2,3declare @total floatset @total=(select SUM(amount) from @importantLead)declare @sum floatset @sum=(select SUM(amount) from(select top 4 amount from @importantLead order by amount desc)a)if (@[email protected])>=0.6 select count(distinct custID) as 重要客户数 from(select top 4 custID from @importantLead order by amount desc)b/*重要客户数2*/
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE table tba ( custID int, amount decimal(18,0), ownerID int, deptID int ) INSERT INTO tbaSELECT 1,1000,1,1 UNIONSELECT 2,2000,1,1 UNIONSELECT 3,5000,1,1 UNIONSELECT 4,1000,1,1 UNIONSELECT 5,500,1,1 UNIONSELECT 6,100,1,1 UNIONSELECT 7,100,1,1 UNIONSELECT 8,100,1,1 UNIONSELECT 9,0,1,1 UNIONSELECT 10,0,1,1 UNIONSELECT 11,0,1,1GOWITH tbb AS( SELECT custID,amount,ownerID,deptID,RN=ROW_NUMBER() OVER (ORDER BY amount Desc) FROM tba)SELECT custID,(SELECT SUM(amount) FROM tbb AS B WHERE A.RN >= B.RN) AS amount,(SELECT SUM(amount) FROM tbb AS B WHERE A.RN >= B.RN)/(SELECT SUM(amount) FROM tbb) AS ProportionFROM tbb AS AcustID amount Proportion3 5000 0.5102042 7000 0.7142851 8000 0.8163264 9000 0.9183675 9500 0.9693876 9600 0.9795917 9700 0.9897958 9800 1.0000009 9800 1.00000010 9800 1.00000011 9800 1.000000