带有多个case语句的SQL查询帮助 [英] Help with an SQL query with multiple case statements

问题描述

我有一个查询，实际上我的表中有8位字段，我想选择一个正确的字段.
请帮帮我.

I have a query, actually I have 8 bit fields in my table and I want to select the one that is true.
Please help me.

Dim Sql As String = "SELECT [EmployeeID],([FirstName]+[LastName]) AS EmpName,[Department],[MonthlySalary], "
            Sql = Sql & " PayPeriod=(CASE WHEN WeeklyWages='1' THEN (CASE WHEN WeeklyWages='1' THEN 'Weekly' END) "
            Sql = Sql & " ELSE (CASE WHEN SemiMonthlyWages='1' THEN (CASE WHEN SemiMonthlyWages='1' THEN 'SemiMonthly' END) "
            Sql = Sql & " ELSE(CASE WHEN MonthlyWages='1' THEN (CASE WHEN MonthlyWages='1' THEN 'Monthly' END) "
            Sql = Sql & " ELSE(CASE WHEN QuarterlyWages='1' THEN (CASE WHEN QuarterlyWages='1' THEN 'Quarterly' END) "
            Sql = Sql & " ELSE(CASE WHEN HalfYearlyWages='1' THEN (CASE WHEN HalfYearlyWages='1' THEN 'HalfYearly' END) "
            Sql = Sql & " ELSE(CASE WHEN yearlyWages='1' THEN (CASE WHEN yearlyWages='1' THEN 'Yearly' END) "
            Sql = Sql & " ELSE(CASE WHEN DailyWages='1' THEN (CASE WHEN DailyWages='1' THEN 'Daily' END) "
            Sql = Sql & " ELSE(CASE WHEN MiscWages='1' THEN (CASE WHEN MiscWages='1' THEN 'Miscellaneous' END) "
            Sql = Sql & " END)END)END)END)END)END)END)END) "
            Sql = Sql & " FROM [IRLPayRollSystem].[irlPayRoll].[Employee_Details_Entry]  where [irlPayRoll].[Employee_Details_Entry].EmployeeID = '" & Session("AddPayrollEmp") & "'"

推荐答案

ELSE(CASE WHEN MonthlyWages='1' THEN (CASE WHEN MonthlyWages='1' THEN 'Monthly' END) "

为什么每个案例都要检查两次?

我不明白为什么这行不通.似乎令人费解，为什么不遍历所有可能性的一个案例"陈述呢?为什么不使用"as"为您的列命名?如果您有8位字段，那么如果选择了多个字段，会发生什么?我认为您有一个设计缺陷，您的意思是一个字段包含一个映射到枚举的值.

Why do you check each case twice ?

I don''t see why this would not work. It seems convoluted, why not one ''case'' statement that iterates through all the possibilities ? Why not use ''as'' to give your column a name ? If you have 8 bit fields, what happens if more than one is selected ? I think you havea design flaw and you meant one field to contain a value that maps to an enum.

这篇关于带有多个case语句的SQL查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！