sql server-MS sql输出销售事务和库存

Question

请帮助仅显示销售项目（IF列btLatinAbbrev=销售额）和库存项目（如果QTY列为1）。

基于上述条件，我还想要一个名为Datatype的列。如果列btLatinAbbrev=销售额，则列Datatype应填写S（S表示销售额）；如果QTY列包含1，则列数据类型应填写I（I表示库存）。

下面是导出的表视图FROM MS SQL 2008

+---------------+-----------------+-----+----------+
| btLatinAbbrev |       SN        | Qty | DATATYPE |
+---------------+-----------------+-----+----------+
| Sales         | 355274073159784 |   0 |          |
| Sales         | 355274073190128 |   0 |          |
| Sales         | 355274073158679 |   0 |          |
| Sales         | 355274073166904 |   0 |          |
| Sales         | 355274073159925 |   0 |          |
| Sales         | 355274073159255 |   0 |          |
| Sales         | 355274073158679 |   0 |          |
| Sales         | 355274073166904 |   0 |          |
| Sales         | 355274073190128 |   0 |          |
| Sales         | 355274073159255 |   0 |          |
| Sales         | 355274073159925 |   0 |          |
| Pur.          | 355274073158679 |   0 |          |
| Pur.          | 355274073159230 |   1 |          |
| Pur.          | 355274073157333 |   1 |          |
| Pur.          | 355274073158851 |   1 |          |
| Pur.          | 355274073166904 |   0 |          |
| Pur.          | 355274073190128 |   0 |          |
| Pur.          | 355274073159784 |   0 |          |
| Pur.          | 355274073159255 |   0 |          |
| Pur.          | 355274073158828 |   1 |          |
| Pur.          | 355274073159925 |   0 |          |
| Sales         | 355274073159784 |   0 |          |
| Sales         | 355274073158679 |   0 |          |
| Sales         | 355274073166904 |   0 |          |
| Sales         | 355274073190128 |   0 |          |
| Sales         | 355274073159255 |   0 |          |
| Sales         | 355274073159925 |   0 |          |
| Pur.          | 355274073158679 |   0 |          |
| Pur.          | 355274073159230 |   1 |          |
| Pur.          | 355274073157333 |   1 |          |
| Pur.          | 355274073158851 |   1 |          |
| Pur.          | 355274073166904 |   0 |          |
| Pur.          | 355274073190128 |   0 |          |
| Pur.          | 355274073159784 |   0 |          |
| Pur.          | 355274073159255 |   0 |          |
| Pur.          | 355274073158828 |   1 |          |
| Pur.          | 355274073159925 |   0 |          |
| Sales         | 355274073159784 |   0 |          |
+---------------+-----------------+-----+----------+

Answer 1

尝试

SELECT btLatinAbbrev,
       Qty,
       CASE WHEN btLatinAbbrev = 'Sales' THEN 'S'
            ELSE 'I'
       END AS DataType
FROM   myTable
WHERE  btLatinAbbrev = 'Sales' OR Qty = 1;

您没有指定在记录既是销售项目又是库存项目的情况下要做什么
上面的查询将其定义为销售项目。

Answer 2

解决方案类型1

with CTE as 
(
select 'sales' 'btLatinAbbrev ','355274073159784' 'SN','0' 'Qty'
union all
select 'sales','355274073159785','1'
union all
select 'pur','355274073159786','2'
union all
select 'Pur','355274073159787','0'
)
select btLatinAbbrev, 
case when btLatinAbbrev IN ('sales') and QTY >0 then 'S'
     when btLatinAbbrev IN ('Pur') and QTY >0 then 'I' end 'Datatype'
 from cte 

2解决方案类型2

with CTE as 
    (
    select 'sales' 'btLatinAbbrev ','355274073159784' 'SN','0' 'Qty'
    union all
    select 'sales','355274073159785','1'
    union all
    select 'pur','355274073159786','2'
    union all
    select 'Pur','355274073159787','0'
    )
    select btLatinAbbrev, sn,
    case when btLatinAbbrev IN ('sales') then 'S' --else 'S' 
         when btLatinAbbrev IN ('Pur')  then 'I' end 'DAtatype'
     from cte where qty > 0