use hr
go
create view view_industryindicator
as
select distinct a.autoId as id,c.com_id as comid,a.year as paraYear,a.rangeId as rangeId,d.rangeName as rangeName,a.paraId as paraId,
e.superId as superId,e.paraName as paraName,b.rangeId as rangeValue,a.excellent as excellent,a.good as good,a.average as average,a.lowers as lowers,
a.worse as worse from indestryindicator a inner join industry b on a.indIndId = b.indIndId inner join company c on b.indId = c.indId inner join industryrange d on a.rangeId = d.rangeId
inner join industrypara e on a.paraId = e.paraId order by a.year desc,c.com_id,a.rangeId,a.autoId;
报下面这个错，是什么原因？
消息 1033，级别 15，状态 1，过程 view_industryindicator，第 6 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
------解决方案--------------------
视图里不要排序
------解决方案--------------------
视图里面把order by 删除就可以了。
使用的时候 select * from 视图名 + order by
------解决方案--------------------
distinct 和 order by 冲突。如果这两个一起使用 select 后面必须加 TOP
------解决方案--------------------
- SQL code
SELECT DISTINCT TOP 100 PERCENT --就需要添加top a.autoId AS id , c.com_id AS comid , a.year AS paraYear , a.rangeId AS rangeId , d.rangeName AS rangeName , a.paraId AS paraId , e.superId AS superId , e.paraName AS paraName , b.rangeId AS rangeValue , a.excellent AS excellent , a.good AS good , a.average AS average , a.lowers AS lowers , a.worse AS worse FROM indestryindicator a INNER JOIN industry b ON a.indIndId = b.indIndId INNER JOIN company c ON b.indId = c.indId INNER JOIN industryrange d ON a.rangeId = d.rangeId INNER JOIN industrypara e ON a.paraId = e.paraId ORDER BY a.year DESC , --视图里面排序 c.com_id , a.rangeId , a.autoId ;