外面的主要想查询出名次Place字段
如查询到结果SupplyName tongguo PlaceTW1 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% 100sea 100% 100fds 60% 60sam 33% 33
)WHERE Ran>=a.Ran)
FROM tb a
ORDER BY Place以上语句在执行的时候总报错
解决方案 »
- [向liangCK大哥以及各位大哥提问]数据库一个表与另一个表数据绑定的问题
- 行---列转换 散分
- 如何编写查询当日发贴数排名??
- 查询时如何排除某一列?
- sqlserver带where的查询速度变的很慢
- 一个存储过程使用一段时间后,其执行时间就变得很长,真的有这个事情吗?如何在程序运行时候修改存储过程.(通过程序语言好,sql本身也好)
- 如何用select查一张表的列数,并且得到各个列名
- 如何读出SQL server中的图片并显示?
- 启动sqlserver服务时,总是出现“系统错误(126),指定驱动程序无法加载“,不知如何解决???
- 呵呵,又是SQL语句.人人给分。。。。
- 简单sql问题 小弟不会 下班结帖
- sql 动态执行的问题
用cte,如果是2005+的话
SELECT * into #tmpTable 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
)tb
SELECT SupplyName,tongguo,Ran=(SELECT COUNT(DISTINCT Ran) FROM #tmpTable WHERE Ran>=a.Ran)
FROM #tmpTable a
ORDER BY Ran
drop table #tmpTable
如查询到结果SupplyName tongguo PlaceTW1 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% 100sea 100% 100fds 60% 60sam 33% 33
) ttt --------些处加一个别名看看WHERE Ran>=a.Ran)
FROM tb a
ORDER BY Place