如何在sql中编写案例？ [英] how to write case in sql?

问题描述

select case when h.VVID<=15 then COUNT(h.fs) end as [<=15],
case when h.VVID between 16 and 18  then COUNT(h.fs) end as [16 - 18],
case when h.VVID between 19 and 21 then COUNT(h.fs) end as [19 - 21],
case when h.VVID between 22 and 24 then COUNT(h.fs) end as [22 - 24],
case when h.VVID>=25 then COUNT(h.fs) end as [>=25]
from
(
select t.fs,sum(t.VVID) VVID,sum(t.VID) VID,sum(t.ID) ID from
(
select dr.C_FS_Code fs,

case when ds.c_doc_grade='G00001'  then COUNT(distinct ds.C_DSC_Code) end as [VVID],

case when ds.c_doc_grade='G00002'  then COUNT(distinct ds.C_DSC_Code) end as [VID],

case when ds.c_doc_grade='G00003'  then COUNT(distinct ds.C_DSC_Code) end as [ID]

from tbl_dwr dr

join tbl_dwr_details ds on ds.N_Srno=dr.N_Srno

join Tbl_FS_Mst fs on fs.C_Code=dr.C_FS_Code

join Tbl_Doc_Stock_Chem_Add_Mst sd on sd.C_Code=ds.C_DSC_Code

where MONTH(dr.D_Date_Report) in (3,4) and YEAR(dr.D_Date_Report)=2015

and fs.N_Type=1
--and dr.C_FS_Code='MSR001'
group by dr.C_FS_Code,ds.c_doc_grade
)t
group by t.fs
order by t.fs
)h

执行此查询后会收到如下错误



消息1033，级别15，状态1，行34

ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效，除非TOP或FOR XML也是指定的。



请给出解决方案...

after executing this query am getting error like in below

Msg 1033, Level 15, State 1, Line 34
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

please give solution for this...

推荐答案

错误应该是很清楚。您在派生表中有一个ORDER BY语句（嵌套查询）。你有ORDER BY t.fs，但它在h派生表中。



要么删除顺序，要把它放在外面，之后）h或者添加SELECT前100名案例......

The error should be pretty clear. You have an ORDER BY statement in your derived table (nested query). You have ORDER BY t.fs but that is inside the h derived table.

Either remove the order by and put it on the outside, after )h or add SELECT TOP 100 PERCENT Case...

这篇关于如何在sql中编写案例？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！