sql选择是否多次 [英] sql select if else multiple times

问题描述

大家好，我有一张桌子，其中包含人的名字和他拥有的物品(1-50个范围值).仅表示两列.现在我必须选择:

< big>名称数量(1-10)数量(10-20)数量(20-30)数量(30-40)数量(40-50)
A 2
B 38
C 13
D 7</big>
注意:这里的值是一个人的确切数量.


我想问一下如何在单个查询中执行此操作.我知道同一列可以有多个if，如果值在1到10之间，则在第一列下选择它，否则将其留空.
其次是使用倍数(如果还可以的话)，否则这是较差的做法.

Hi all I have a table which contains the name of person and item he has(1-50 range values). means two columns only.Now I have to select as :

<big>Name qty(1-10) qty(10-20) qty(20-30) qty(30-40) qty(40-50)
A 2
B 38
C 13
D 7</big>
Note: Here value is the exact quantity a person have.


I want to ask how to perform this in a single query. I know I can have multiple if else for the same column like if value is between 1- 10 then select it under first column else leave it blank.
Second is using multiple if else is okay or it is the worse practice.

推荐答案

SELECT 
Name,
SUM(CASE WHEN qty > 1  AND qty <= 10 THEN qty ELSE 0 END) as qty1_10,
SUM(CASE WHEN qty > 10 AND qty <= 20 THEN qty ELSE 0 END) as qty11_20,
SUM(CASE WHEN qty > 20 AND qty <= 30 THEN qty ELSE 0 END) as qty21_30,
SUM(CASE WHEN qty > 30 AND qty <= 40 THEN qty ELSE 0 END) as qty31_40,
SUM(CASE WHEN qty > 40 AND qty <= 50 THEN qty ELSE 0 END) as qty41_50
FROM Table1
GROUP BY Name

用例

Use Case

Syntax

CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END
Example:

DECLARE @TestVal INT --Declare a variable
SET @TestVal = 3 --Assiggning Variable value as 3

SELECT
CASE @TestVal
WHEN 1 THEN 'First' --@TestValvalue is 1 then 'First' return
WHEN 2 THEN 'Second'--@TestValvalue is 2 then 'Second' return
WHEN 3 THEN 'Third'--@TestValvalue is 3 then 'Third' return
ELSE 'Other'--@else then 'Other' return
END

更多详细信息，请参见
http://blog.sqlauthority.com/2007/04 /14/sql-server-case-statementexpression-examples-and-explanation/ [ http://msdn.microsoft.com/en-us/library/ms181765.aspx [ ^ ]

More details Refer
http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/[^]

http://msdn.microsoft.com/en-us/library/ms181765.aspx[^]

这篇关于sql选择是否多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！