SELECT t.*,t2.* ,t3.*,t3.[Wwater] as wwt,t3.[Nwater] as nwt
,isnull((select top 1 cast(nwater as varchar)+'/'+cast(wwater as varchar) from [PYPDA].[dbo].[ZM_STA_HIS] t4
where t4.dtime>GETDATE()-1 and datediff(mi,t4.[dTime],t2.[dTime])>15 and t2.zmid=t4.zmid order by t4.[dTime] desc)
,-9999) Watera
FROM [PYPDA].[dbo].[stu207_was_b] t
,[PYPDA].[dbo].[ZM_STA] t2 ,[PYPDA].[dbo].[ZM_Watering] t3
where t.stcd=t2.zmid and t2.zmid=t3.zmid and t.starea like '%{0}%'
order by t2.lev,t.stpqt.*,t2.* ,t3.*不用优化先,主要是怎么把 isnull((select top 1 cast(nwater as varchar)+'/'+cast(wwater as varchar) from [PYPDA].[dbo].[ZM_STA_HIS] t4
where t4.dtime>GETDATE()-1 and datediff(mi,t4.[dTime],t2.[dTime])>15 and t2.zmid=t4.zmid order by t4.[dTime] desc)
,-9999) Watera
提出来吧
,isnull((select top 1 cast(nwater as varchar)+'/'+cast(wwater as varchar) from [PYPDA].[dbo].[ZM_STA_HIS] t4
where t4.dtime>GETDATE()-1 and datediff(mi,t4.[dTime],t2.[dTime])>15 and t2.zmid=t4.zmid order by t4.[dTime] desc)
,-9999) Watera
FROM [PYPDA].[dbo].[stu207_was_b] t
,[PYPDA].[dbo].[ZM_STA] t2 ,[PYPDA].[dbo].[ZM_Watering] t3
where t.stcd=t2.zmid and t2.zmid=t3.zmid and t.starea like '%{0}%'
order by t2.lev,t.stpqt.*,t2.* ,t3.*不用优化先,主要是怎么把 isnull((select top 1 cast(nwater as varchar)+'/'+cast(wwater as varchar) from [PYPDA].[dbo].[ZM_STA_HIS] t4
where t4.dtime>GETDATE()-1 and datediff(mi,t4.[dTime],t2.[dTime])>15 and t2.zmid=t4.zmid order by t4.[dTime] desc)
,-9999) Watera
提出来吧
t.*
,t2.*
,t3.*
,t3.[Wwater] as wwt
,t3.[Nwater] as nwt
,isnull(cast(t4.nwater as varchar)+'/'+cast(t4.wwater as varchar),-9999) Watera
,row_number() over(order by isnull(t4.[dTime],GETDATE()) desc) num_id
FROM [PYPDA].[dbo].[stu207_was_b] t
inner join [PYPDA].[dbo].[ZM_STA] t2
on t.stcd=t2.zmid
inner join [PYPDA].[dbo].[ZM_Watering] t3
on t2.zmid=t3.zmid
left join [PYPDA].[dbo].[ZM_STA_HIS] t4
on t4.dtime>GETDATE()-1
and datediff(mi,t4.[dTime],t2.[dTime])>15
and t2.zmid=t4.zmid
where t.starea like '%{0}%'
and num_id=1
order by t2.lev,t.stpq
;
--这里返回的内容,会多个排序列SNO
SELECT * FROM
(SELECT t.*,
t2.*,
t3.*,
t3. Wwater as wwt,
t3. Nwater as nwt,
CASE WHEN t4.dtime > GETDATE() - 1
and datediff(mi, t4.dTime, t2.dTime) > 15 THEN cast(nwater as varchar) + '/' + cast(wwater as varchar)
ELSE -9999 Watera,
row_number() over(PARTITION BY t4.zmid ORDER BY t4.dTime desc) SNO
FROM PYPDA.dbo.stu207_was_b t,
PYPDA.dbo.ZM_STA t2 LEFT JOIN PYPDA.dbo.ZM_STA_HIS t4 ON t2.zmid = t4.zmid
PYPDA.dbo.ZM_Watering t3
where t.stcd = t2.zmid
and t2.zmid = t3.zmid
and t.starea like '%{0}%'
order by t2.lev,
t.stpq
t.*,
t2.*,
t3.* )A
WHERE SNO = 1
CASE WHEN ..... THEN ... ELSE ... END
t.stpq ,
t.lttd ,
t2.dtime,
t3.dt )A 中A 不正确
t.stpq
t.*,
t2.*,
t3.*
删除,如果必须排序,放在最外层不能放在子查询中,完整的错误提示是:
“除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。”
修改方法是,在一开始的SELECT t.*,t2.* ,t3.*,处,不要用*,确保每个列名列出,发现同列名只保留一个或者使用别名。