sql - if else 条件使用 sql 查询

Question

我在为下表生成 SQL 查询时遇到问题。这是我的表格:

County          | Store     | Stock | Display | Designation
--------------- | --------- | ----- | ------- | ------------
USA             | USD       | 1     | Yes     | Merchandiser
USA             | USD       | 2     | Yes     | Promoter

我想要这样的结果

County          | Store     | Stock | Display | Designation
--------------- | --------- | ----- | ------- | ------------
USA             | USD       | 2     | Yes     | Merchandiser
USA             | USD       | 2     | Yes     | Promoter

场景是如果 Designation 是 Promoter，则同时使用 Display 和 Stock 数据。如果指定来自跟单员，则使用来自发起人的数据作为库存数据我怎样才能做到这一点？

Answer 1

请尝试下面的代码来创建临时表

--===== If the test table already exists, drop it
 IF OBJECT_ID('TestDB..#mytable','U') IS NOT NULL
     DROP TABLE #mytable
 --===== Create the test table with 
 CREATE TABLE #mytable 
        (
        Country varchar(20),
        Store varchar(20),
        Stock int,
        Display varchar(5),
        Designation varchar(20)
        )
   --===== Insert the test data into the test table
 INSERT INTO #mytable 
       (Country, Store, Stock, Display, Designation)


 SELECT 'SG','a','2','YES','Merchandiser' UNION ALL
 SELECT 'SG','a','4','YES','Promoter' 

现在使用上面的查询

SELECT Country, 
       Store , 
       (CASE WHEN (Designation = 'Merchandiser') THEN (SELECT SUM(STOCK) FROM #mytable WHERE Country = Country AND Designation = 'Promoter' GROUP BY Country) ELSE STOCK END) AS "stock",
       Display, 
       Designation FROM #mytable