一个语句求名次，分不多，解答正确后一定补上分，多谢了

一个语句求名次，分不多，解答正确后一定补上分，谢谢了

SQL code

外面的主要想查询出名次Place字段如查询到结果SupplyName      tongguo      PlaceTW1           100%        1sea           100%        1fds           60%             2sam           33%         3SELECT SupplyName,tongguo,Place=(SELECT COUNT(DISTINCT Ran) FROM  ([color=#FF0000]select SupplyName,tongguo,convert(float,tongguoS)  AS   RanFROM(select SupplyName,ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1))+'%' as tongguo, ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1)) as tongguoS from Supply_Sign where SignDate >= '2011-12-1' and SignDate <'2012-01-1' group by SupplyName)Supply_Sign order by Ran DESC[/color]这一段红色可以查询到以下数据TW1    100%    100sea    100%    100fds    60%    60sam    33%    33)WHERE Ran>=a.Ran)FROM tb aORDER BY Place

以上语句在执行的时候总报错

[解决办法]
tb表有Ran字段吗，估计没有
用cte，如果是2005+的话

[解决办法]
2005+用cte和排名函数

[解决办法]
外面的主要想查询出名次Place字段
如查询到结果

SupplyName tongguo Place

TW1 100% 1
sea 100% 1
fds 60% 2
sam 33% 3


SELECT SupplyName,tongguo,Place=(SELECT COUNT(DISTINCT Ran) FROM

(

select SupplyName,tongguo,convert(float,tongguoS) AS Ran
FROM(select SupplyName,ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1))+'%' as tongguo,
ltrim(sum(case Violation when '1' then 1 when '2' then 1 else 0 end)*100/count(1)) as tongguoS
from Supply_Sign where SignDate >= '2011-12-1' and SignDate <'2012-01-1' group by SupplyName)Supply_Sign
order by Ran DESC

这一段红色可以查询到以下数据

TW1 100% 100

sea 100% 100

fds 60% 60

sam 33% 33
) ttt --------些处加一个别名看看

WHERE Ran>=a.Ran)
FROM tb a
ORDER BY Place
[解决办法]

SQL code

ok 那就做个标记.. 

 

查看更多 下一篇