如何在DB2中做更多的事情

WHERE Patrons.PatronsID = i.PatronID）; ELSE''未知参与者''; 结束案例作为PersonDoingTheInfraction FROM Infractions iThe continuing saga of Ian trying to learn DB2-SQL18. CASE StatementsAre these even possible:SELECTCASEWHEN v.id=1 THEN ''Hello'';WHEN v.id=2 THEN '', '';WHEN v.id=3 THEN ''world'';ELSE ''!''END CASE AS fooFROM (VALUES 1, 2, 3, 4) AS v(id)Desired outputfoo========Hello,world!Another example:SELECTCustomerID,SUM(CASEWHEN OrderValue > 1000 THEN OrderAmount;ELSE 0END CASE) AS TotalOfBigOrdersFROM OrdersAnother example:SELECTi.InfractionID,CASEWHEN i.EmployeeID IS NOT NULL THEN (Employees.EmployeeName);WHEN i.PatronID IS NOT NULL THEN (Patrons.PatronName);ELSE ''Unknown Participant'';END CASE AS PersonDoingTheInfactionFROM Infactions iLEFT JOIN PatronsON i.PatronID = Patrons.PatronIDLEFT JOIN EmployeesON i.EmployeeID = Employees.EmployeeIDor another desired technique of the above:SELECTi.InfactionID,CASEWHEN i.EmployeeID IS NOT NULL THEN (SELECT EmployeeNameFROM EmployeesWHERE Employees.EmployeeID = i.EmployeeID);WHEN i.PatronID IS NOT NULL THEN (SELECT PatronnameFROM PatronsWHERE Patrons.PatronsID = i.PatronID);ELSE ''Unknown participant'';END CASE AS PersonDoingTheInfractionFROM Infractions i推荐答案文章< dv ********* @ enews4.newsguy.com> ， ia *********** @ avatopia.com 说。 ..In article <dv*********@enews4.newsguy.com>, ia***********@avatopia.comsays... Ian继续尝试学习DB2-SQL的传奇 The continuing saga of Ian trying to learn DB2-SQL 你可以从Greame Birchall那里学到很多SQL Cookbook， google for DB2 SQL Cookbook并检查第一个链接。You can learn a lot reading the SQL Cookbook from Greame Birchall,google for DB2 SQL Cookbook and check the first link. Ian Boyd写道：Ian Boyd wrote:继续传奇Ian试图学习DB2-SQL 18.案例陈述 这些甚至是可能的： SELECT CASE 当v.id = 1那么''你好'' WHEN v.id = 2那么''，' WHEN v.id = 3那么'世界''ELSE''！'' END as foo FROM（VALUES 1,2,3,4）AS v（id） 期望的输出 foo ======== 你好，世界 另一个例子： SELECT CustomerID， SUM（ CASE WHEN OrderValue> 1000那么OrderAmount ELSE 0 END）AS TotalOfBigOrders FROM Orders 另一个例子： 选择 i.InfractionID， CASE 当i.EmployeeID不为空时（Employees.EmployeeName） 当i.PatronID不为空时（Patrons.PatronName）ELSE''未知参与者''结束为PersonDoingTheInfaction来自Infactions i LEFT JOIN Patrons ON i.PatronID = Patrons.PatronID LEFT JOIN员工 ON i.EmployeeID = Employees.EmployeeID 或上述另一种理想的技术： i.InfactionID， CASE 当i.EmployeeID不为空时（ SELECT EmployeeName FROM Employees WHERE Employees.EmployeeID = i.EmployeeID）当i.PatronID不为空时（选择Patronname 来自顾客 WHERE Pat rons.PatronsID = i.PatronID） ELSE''未知参与者'' END作为PersonDoingInInfraction FROM Infractions i The continuing saga of Ian trying to learn DB2-SQL 18. CASE Statements Are these even possible: SELECT CASEWHEN v.id=1 THEN ''Hello''WHEN v.id=2 THEN '', ''WHEN v.id=3 THEN ''world'' ELSE ''!''END AS foo FROM (VALUES 1, 2, 3, 4) AS v(id) Desired output foo ======== Hello , world ! Another example: SELECT CustomerID, SUM( CASEWHEN OrderValue > 1000 THEN OrderAmount ELSE 0END ) AS TotalOfBigOrders FROM Orders Another example: SELECT i.InfractionID, CASEWHEN i.EmployeeID IS NOT NULL THEN (Employees.EmployeeName)WHEN i.PatronID IS NOT NULL THEN (Patrons.PatronName) ELSE ''Unknown Participant''END AS PersonDoingTheInfaction FROM Infactions i LEFT JOIN Patrons ON i.PatronID = Patrons.PatronID LEFT JOIN Employees ON i.EmployeeID = Employees.EmployeeID or another desired technique of the above: SELECT i.InfactionID, CASE WHEN i.EmployeeID IS NOT NULL THEN ( SELECT EmployeeName FROM EmployeesWHERE Employees.EmployeeID = i.EmployeeID) WHEN i.PatronID IS NOT NULL THEN ( SELECT Patronname FROM PatronsWHERE Patrons.PatronsID = i.PatronID)ELSE ''Unknown participant''END AS PersonDoingTheInfraction FROM Infractions i Works完全相同的Oracle 9i和* squinthard * SQL Server 2000 干杯 Serge - Serge Rielau DB2解决方案开发 IBM多伦多实验室Works exactly the same in Oracle 9i and *squinthard* SQL Server 2000CheersSerge--Serge RielauDB2 Solutions DevelopmentIBM Toronto Lab Ian Boyd写道：Ian Boyd wrote: Ian试图学习DB2-SQL的持续传奇 这可以解释一下。 ：P 18. CASE声明 这些甚至是可能的： SELECT CASE WHEN v.id = 1那么''你好''; 当v.id = 2那么''，''; 当v.id = 3那么''世界'; ELSE''！'' END CASE as foo FROM（VALUES 1,2,3,4）AS v（id） 期望的输出 foo ======== 你好，世界！ The continuing saga of Ian trying to learn DB2-SQLThat would explain quite a bit. :P 18. CASE Statements Are these even possible: SELECT CASE WHEN v.id=1 THEN ''Hello''; WHEN v.id=2 THEN '', ''; WHEN v.id=3 THEN ''world''; ELSE ''!'' END CASE AS foo FROM (VALUES 1, 2, 3, 4) AS v(id) Desired output foo ======== Hello , world ! 每次结束后放下分号条款和CASE一词来自END CASE SELECT CASE WHEN v.id = 1然后''你好'' WHEN v.id = 2那么''，' WHEN v.id = 3那么''世界'' ELSE''！'' END as foo FROM（VALUES 1,2,3,4）AS v（id） 另外，在这种特殊情况下你可以缩短它： SELECT CASE v.id WHEN 1那么''你好'' 当2'时''，' 当3'那么''世界' ELSE''！'' END as foo FROM（VALUES 1,2,3,4）AS v（id） B 。Drop the semi-colon after each clause, and the word "CASE" from "ENDCASE"SELECTCASEWHEN v.id=1 THEN ''Hello''WHEN v.id=2 THEN '', ''WHEN v.id=3 THEN ''world''ELSE ''!''END AS fooFROM (VALUES 1, 2, 3, 4) AS v(id)Also, is this particular case you can shorten it to:SELECTCASE v.idWHEN 1 THEN ''Hello''WHEN 2 THEN '', ''WHEN 3 THEN ''world''ELSE ''!''END AS fooFROM (VALUES 1, 2, 3, 4) AS v(id)B. 这篇关于如何在DB2中做更多的事情的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！