如何在DB2中做更多的事情 [英] How to do more things in DB2
问题描述
Ian继续尝试学习DB2-SQL的传奇
18. CASE语句
这些甚至是可能的：
SELECT
CASE
WHEN v.id = 1 THEN''Hello'';
WHEN v .id = 2 THEN''，'';
WHEN v.id = 3那么''世界';
ELSE''！''
结束案例如foo
FROM（价值观1,2,3,4）AS v（id）
期望的输出
foo
========
你好
，
world
！
另一个例子：
SELECT
CustomerID，
SUM （
CASE
WHEN OrderValue> 1000 THEN OrderAmount;
ELSE 0
END CASE）AS TotalOfBigOrders
来自订单
另一个例子：
SELECT
i.InfractionID，
CASE
当i.EmployeeID不为空时（Employees.EmployeeName）;
当i.PatronID不是NU时LL THEN（Patrons.PatronName）;
ELSE''未知参与者'';
结束案例作为PersonDoingTheInfaction
FROM Infactions i
LEFT JOIN Patrons
ON i.PatronID = Patrons.PatronID
LEFT JOIN员工
ON i.EmployeeID =员工。 EmployeeID
或上述其他所需技巧：
SELECT
i.InfactionID，
CASE
当i.EmployeeID不为空时（
SELECT员工名称
来自员工
WHERE Employees.EmployeeID = i.EmployeeID）;
当i.PatronID不为空时（
SELECT Patronname
来自顾客
WHERE Patrons.PatronsID = i.PatronID）;
ELSE''未知参与者'';
结束案例作为PersonDoingTheInfraction
FROM Infractions i
The continuing saga of Ian trying to learn DB2-SQL
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
!
Another example:
SELECT
CustomerID,
SUM(
CASE
WHEN OrderValue > 1000 THEN OrderAmount;
ELSE 0
END CASE) AS TotalOfBigOrders
FROM Orders
Another example:
SELECT
i.InfractionID,
CASE
WHEN i.EmployeeID IS NOT NULL THEN (Employees.EmployeeName);
WHEN i.PatronID IS NOT NULL THEN (Patrons.PatronName);
ELSE ''Unknown Participant'';
END CASE 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 Employees
WHERE Employees.EmployeeID = i.EmployeeID);
WHEN i.PatronID IS NOT NULL THEN (
SELECT Patronname
FROM Patrons
WHERE Patrons.PatronsID = i.PatronID);
ELSE ''Unknown participant'';
END CASE AS PersonDoingTheInfraction
FROM Infractions i
推荐答案
文章< dv ********* @ enews4.newsguy.com> ， ia *********** @ avatopia.com
说。 ..
In article <dv*********@enews4.newsguy.com>, ia***********@avatopia.com
says...
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
CASE WHEN 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(
CASE WHEN OrderValue > 1000 THEN OrderAmount ELSE 0 END ) AS TotalOfBigOrders FROM Orders
Another example:
SELECT
i.InfractionID,
CASE WHEN 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 Employees WHERE Employees.EmployeeID = i.EmployeeID) WHEN i.PatronID IS NOT NULL THEN (
SELECT Patronname
FROM Patrons WHERE Patrons.PatronsID = i.PatronID)
ELSE ''Unknown participant''
END AS PersonDoingTheInfraction FROM Infractions i
Works完全相同的Oracle 9i和* squinthard * SQL Server 2000
干杯
Serge
- < br $>
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Works exactly the same in Oracle 9i and *squinthard* SQL Server 2000
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM 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-SQL
That 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 "END
CASE"
SELECT
CASE
WHEN 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)
Also, is this particular case you can shorten it to:
SELECT
CASE v.id
WHEN 1 THEN ''Hello''
WHEN 2 THEN '', ''
WHEN 3 THEN ''world''
ELSE ''!''
END AS foo
FROM (VALUES 1, 2, 3, 4) AS v(id)
B.
这篇关于如何在DB2中做更多的事情的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
