列名左连接字段值的 SQL 查询

Question

是否可以从这些表中通过一次查询获得我想要的结果？问题是我怀疑因为代码的名字只是在列名中，所以这是不可能的。

+-----------+----+-------------+-----------+----------+
| unioncode | qt | productCode | brandCode | shopCode |
+-----------+----+-------------+-----------+----------+
| 00212AA   | 10 |           3 |         2 |        1 |
| 00212AA   | -4 |           3 |         2 |        1 |
| 00212AA   |  3 |           3 |         2 |        1 |
| 00372BC   |  7 |           6 |         4 |        1 |
+-----------+----+-------------+-----------+----------+

+------+-------------+---------+
| code | description |  type   |
+------+-------------+---------+
|    1 | BuyTech     | shop    |
|    2 | Apple       | brand   |
|    3 | iPhone      | product |
|    5 | Samsung     | product |
|    6 | Smartphone  | product |
+------+-------------+---------+

我正在努力创建一个查询来获得我想要的结果如下:

+-----------+-------+-------------+-----------+----------+
| unioncode | totQt | productName | brandName | shopName |
+-----------+-------+-------------+-----------+----------+
| 00212AA   |     9 | iPhone      | Apple     | BuyTech  |
+-----------+-------+-------------+-----------+----------+
| 00372BC   |     7 | Smartphone  | Null      | BuyTech  |
+-----------+-------+-------------+-----------+----------+

Answer 1

你应该在第二张 table 上加入三次。

很抱歉，没有 brandCode = 4。

create table t1 (unioncode varchar(10), qt int, productCode int, brandCode int, shopCode int);
insert into t1 values
('00212AA', 10, 3, 2, 1),
('00212AA', -4, 3, 2, 1),
('00212AA',  3, 3, 2, 1),
('00372BC',  7, 6, 4, 1);
GO

create table t2 (code int, description varchar(20), type varchar(10));
insert into t2 values
(1, 'BuyTech', 'shop'),
(2, 'Apple', 'brand'),
(3, 'iPhone', 'product'),
(5, 'Samsung', 'product'),
(6, 'Smartphone', 'product');
GO

select unioncode, 
       tot_qt, 
       tt2.description as productName, 
       tt3.description as shopName, 
       tt4.description as brandName
from
     (select unioncode, sum(qt) tot_qt, productCode, shopCode, brandCode
      from   t1
      group by unioncode, productCode, shopCode, brandCode
     ) tt1
left join  t2 tt2
on    tt2.code = tt1.productCode
and   tt2.type = 'product'
left join  t2 tt3
on    tt3.code = tt1.shopCode
and   tt3.type = 'shop'
left join  t2 tt4
on    tt4.code = tt1.brandCode
and   tt4.type = 'brand'
GO

unioncode | tot_qt | productName | shopName | brandName:-------- | -----: | :---------- | :------- | :--------00212AA   |      9 | iPhone      | BuyTech  | Apple    00372BC   |      7 | Smartphone  | BuyTech  | null     

dbfiddle here