帮我看看这个SQL语句怎么改。。
==查出的记录先按[key]字段内容的长度排序,再取出CODE的唯一值 可下面报错拉。
select distinct(code) from (
SELECT code,len([key]) as length FROM TCode
WHERE (CID = 50397184) AND (FieldName = 'f10 ')
AND (Code%2 =0 or Code =1)
AND ( 'MINISD卡;支持MINISD存储卡(可热插拔),2GBMINISD存储卡的大致存储容量指标,视频(MPEG4CIF编码,播放速率每秒15帧,AAC音频),最多可储存175分钟的视频片段 ' Like '% ' + [KEY] + '% ')
order by length desc
) t
order by lengths desc
===错误内容
消息 1033,级别 15,状态 1,第 8 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
[解决办法]
select distinct(code) from (
SELECT code,len([key]) as length FROM TCode
WHERE (CID = 50397184) AND (FieldName = 'f10 ')
AND (Code%2 =0 or Code =1)
AND ( 'MINISD卡;支持MINISD存储卡(可热插拔),2GBMINISD存储卡的大致存储容量指标,视频(MPEG4CIF编码,播放速率每秒15帧,AAC音频),最多可储存175分钟的视频片段 ' Like '% ' + [KEY] + '% ')
) t
order by lengths desc
[解决办法]
select distinct(code) from (
SELECT top 100 percent code,len([key]) as length FROM TCode
WHERE (CID = 50397184) AND (FieldName = 'f10 ')
AND (Code%2 =0 or Code =1)
AND ( 'MINISD卡;支持MINISD存储卡(可热插拔),2GBMINISD存储卡的大致存储容量指标,视频(MPEG4CIF编码,播放速率每秒15帧,AAC音频),最多可储存175分钟的视频片段 ' Like '% ' + [KEY] + '% ')
order by length desc
) t
order by lengths desc
[解决办法]
--加个top 100 perenct
select distinct(code) from (
SELECT top 100 percent code,len([key]) as length FROM TCode
WHERE (CID = 50397184) AND (FieldName = 'f10 ')
AND (Code%2 =0 or Code =1)
AND ( 'MINISD卡;支持MINISD存储卡(可热插拔),2GBMINISD存储卡的大致存储容量指标,视频(MPEG4CIF编码,播放速率每秒15帧,AAC音频),最多可储存175分钟的视频片段 ' Like '% ' + [KEY] + '% ')
order by length desc
) t
order by lengths desc
[解决办法]
掉了東西,修改下
select code from (
SELECT code,len([key]) as length FROM TCode
WHERE (CID = 50397184) AND (FieldName = 'f10 ')
AND (Code%2 =0 or Code =1)
AND ( 'MINISD卡;支持MINISD存储卡(可热插拔),2GBMINISD存储卡的大致存储容量指标,视频(MPEG4CIF编码,播放速率每秒15帧,AAC音频),最多可储存175分钟的视频片段 ' Like '% ' + [KEY] + '% ')
) t
Group By code
order by Max(length) desc