strSQL是字符串类型
strSQL = "SELECT wa_gzdata.cpsn_num, " & _
"wa_gzdata.cpsn_name, " & _
"wa_gzdata.f_3-300 " & _
"FROM wa_gzdata " & _
"WHERE ( wa_gzdata.imonth = " & mm & " ) and " & _
"( wa_gzdata.f_3 <> 0.00 ) " & _
"and ( wa_gzdata.cDept_Num <> '9')" & _
"and ( iPsnGrd_id <> 6 )" & _
"and ( iPsnGrd_id <> 7 )" & _
"and ( iPsnGrd_id <> 16)" & _
"and ( iPsnGrd_id <> 17)" & _
"and ( iPsnGrd_id <> 18)" & _
"and ( iPsnGrd_id <> 20)" & _
"and ( iPsnGrd_id <> 21)" & _
"and ( iPsnGrd_id <> 22)"
怎么可以设计当iPsnGrd_id等于6,7,16,17,18,20,21,22时wa_gzdata.f_3 不减300而不等于那几个值时wa_gzdata.f_3要减300,而且当减去300后如果值小于零则把他默认为零。谢谢!
strSQL = "SELECT wa_gzdata.cpsn_num, " & _
"wa_gzdata.cpsn_name, " & _
"wa_gzdata.f_3-300 " & _
"FROM wa_gzdata " & _
"WHERE ( wa_gzdata.imonth = " & mm & " ) and " & _
"( wa_gzdata.f_3 <> 0.00 ) " & _
"and ( wa_gzdata.cDept_Num <> '9')" & _
"and ( iPsnGrd_id <> 6 )" & _
"and ( iPsnGrd_id <> 7 )" & _
"and ( iPsnGrd_id <> 16)" & _
"and ( iPsnGrd_id <> 17)" & _
"and ( iPsnGrd_id <> 18)" & _
"and ( iPsnGrd_id <> 20)" & _
"and ( iPsnGrd_id <> 21)" & _
"and ( iPsnGrd_id <> 22)"
怎么可以设计当iPsnGrd_id等于6,7,16,17,18,20,21,22时wa_gzdata.f_3 不减300而不等于那几个值时wa_gzdata.f_3要减300,而且当减去300后如果值小于零则把他默认为零。谢谢!
CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是使用可读性更强的值替换代码或缩写。下面的查询使用 CASE 函数重命名书籍的分类,以使之更易理解。USE pubs
SELECT
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1
case when iPsnGrd_id in (6,7,16,17,18,20,21,22)
then wa_gzdata.f_3
else ( case when wa_gzdata.f_3-300>0 then wa_gzdata.f_3-300>0 else 0 end ) end
....