选择Union，然后从两个表与列VarChar inter Int连接加入

发布于 2025-01-26 12:11:54 字数 1464 浏览 7 评论 0原文

在数据库“ ProdBase”中，我有三张表：

表产品

|id  | productname |   productprice  | productsupplierID (int11)  |
+----+-------------+-----------------+----------------------------+
|  1 |   Shirt     |     20          |         3                  |
|  2 |   Tshirt    |     25          |         5                  |

表供应商

|supplierid (int11)  | suppliername (varchar) |
+--------------------+------------------------+
|  3                 |    CompanyA            |
|  5                 |    CompanyB            |

表工作人员

|id  |  staffname  |   staffposition   | 
+----+-------------+-------------------+
|  1 |    John     |   sales           |
|  2 |    Megan    |   accounting      |

表产品使用表供应商：SupplierID的查找字段。

我创建了一个数据库视图，以对工会桌产品和员工进行。以及以下查询。

SELECT 'prodbase'.'Products'.'productname' AS 'Product', 'prodbase'.'Products'.'productsupplierID' AS 'Supplier' FROM 'prodbase'.'Products'
UNION
SELECT 'prodbase'.'Staff'.'staffname' AS 'Staff', 'prodbase'.'Staff'.'staffposition' AS 'Position' FROM 'prodbase'.'Staff'
ORDER BY 'Supplier'
DESC LIMIT 5;

查询正在工作，但它在供应商列中显示数字（ID）。

Product: Shirt  
Staff: John  
Position: sales    
Supplier: 3

如何使用JOIN创建此工会查询，因此它显示了SupplierID的供应名称？ 所需输出的示例：

Product: Shirt  
Staff: John  
Position: sales  
Supplier: CompanyA

原文

In database "prodbase" i have three tables:

Table Products

|id  | productname |   productprice  | productsupplierID (int11)  |
+----+-------------+-----------------+----------------------------+
|  1 |   Shirt     |     20          |         3                  |
|  2 |   Tshirt    |     25          |         5                  |

Table Supplier

|supplierid (int11)  | suppliername (varchar) |
+--------------------+------------------------+
|  3                 |    CompanyA            |
|  5                 |    CompanyB            |

Table Staff

|id  |  staffname  |   staffposition   | 
+----+-------------+-------------------+
|  1 |    John     |   sales           |
|  2 |    Megan    |   accounting      |

Table Products uses a look-up field from table Supplier: supplierID.

I have created a database View to union table Products and Staff.
with following query.

SELECT 'prodbase'.'Products'.'productname' AS 'Product', 'prodbase'.'Products'.'productsupplierID' AS 'Supplier' FROM 'prodbase'.'Products'
UNION
SELECT 'prodbase'.'Staff'.'staffname' AS 'Staff', 'prodbase'.'Staff'.'staffposition' AS 'Position' FROM 'prodbase'.'Staff'
ORDER BY 'Supplier'
DESC LIMIT 5;

Query is working, but it displays number (ID) in Supplier column.

Product: Shirt  
Staff: John  
Position: sales    
Supplier: 3

How to create this UNION query with JOIN, so it displays Suppliername insted of supplierID?
example of desired output:

Product: Shirt  
Staff: John  
Position: sales  
Supplier: CompanyA

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

以为你会在 2025-02-02 12:11:54

您需要解决一些问题，以使数据按照所需的方式返回。首先，我认为您不需要这里的工会。一个工会是在您想将多个查询组合为一个时。我认为您只需要在数据上使用内部加入即可获得所需的输出。说到人际关系，您缺少一个重要的关系。员工，产品和供应商之间没有关系，因此，除非您合并一个关系表，否则无法提供您想要的输出。我创建了一个可以想象的关系表的示例，称为“销售”，该表将员工与出售的产品联系起来。这不是一个完美的例子，但是它可以使您实际使用数据。使用关系表，您可以在此简单的示例中内部加入以获取工作人员出售的所有产品。

 创建表产品（ID int，productName varchar（50），productPrice int，productupplierId int）;
创建表供应商（SupplierID INT，supplierName varchar（50））;
创建表工作人员（ID INT，StaffName varchar（50），Staff位置VARCHAR（50））;

创建表销售（StaffID INT，productid int）；

插入产品值（1，'衬衫'，20,3），（2，'T恤'，25,5）;
插入供应商值（3，'companya'），（5，'companyB'）;
插入员工价值观（1，'John'，'sales'），（2，'Megan'，“会计”）；

插入销售价值（1,1）；

 选择
    products.productname作为产品，
    员工。词名作为员工，
    员工。签字作为位置，    
    products.productprice作为价格， 
    供应商。SupplierName作为供应商 
从 
  销售 
    内部加入产品。         
    内在加入供应商供应商。
    内部加入员工工作人员。ID= sale.Staffid

产品|工作人员|位置|价格|供应商
：------- | ：---- | ：------- | ----：| ：--------
衬衫|约翰|销售| 20 |公司

db＆lt;＆gt;＆gt

;我根据评论删除了销售。我想您可以交叉加入员工，但是，如果没有产品/供应商和员工之间的关系，就没有合乎逻辑的方法可以将这些数据放在其上。

SELECT
    Products.productname AS Product,
    Products.productprice AS Price, 
    Supplier.suppliername AS Supplier 
FROM  
    Products 
    INNER JOIN Supplier ON Supplier.supplierid=Products.productsupplierID

There are a few problems you need to sort out to have the data returned the way you want. First, I don't think you need a UNION here. A UNION is when you want to combine multiple queries into one. I think you just simply need to use an INNER JOIN on your data to get the desired output. Speaking of relationships, you are missing an important one. There is no relationship between staff, product and supplier, thusly there is no way to provide the output you desire unless you incorporate a relationship table. I created an example of a conceivable relationship table called Sale which links a staff to a product sold. This is not a perfect example, but it will allow you to actually work with the data. With the relationship table, you can INNER JOIN to get all products sold by staff members, in this simple example.

CREATE TABLE Products(id INT, productname VARCHAR(50), productprice int, productsupplierID int);
CREATE TABLE Supplier(supplierid INT, suppliername VARCHAR(50));
CREATE TABLE Staff(id INT, staffname VARCHAR(50),staffposition VARCHAR(50));

CREATE TABLE Sale(staffid INT, productId INT);

INSERT INTO Products VALUES(1,'Shirt',20,3),(2,'Tshirt',25,5);
INSERT INTO Supplier VALUES(3,'CompanyA'),(5,'CompanyB');
INSERT INTO Staff VALUES(1,'John','Sales'),(2,'Megan','Accounting');

INSERT INTO Sale VALUES(1,1);

SELECT
    Products.productname AS Product,
    Staff.staffname AS Staff,
    Staff.staffposition AS Position,    
    Products.productprice AS Price, 
    Supplier.suppliername AS Supplier 
FROM 
  Sale 
    INNER JOIN Products ON Products.id=Sale.productId         
    INNER JOIN Supplier ON Supplier.supplierid=Products.productsupplierID
    INNER JOIN Staff ON Staff.id = Sale.staffid

Product | Staff | Position | Price | Supplier
:------ | :---- | :------- | ----: | :-------
Shirt   | John  | Sales    |    20 | CompanyA

db<>fiddle here

Edited - I removed Sale based on comment. I guess you could cross join on staff, however, without a relationship between a product/supplier and staff there is no logical way to lay that data over it.

SELECT
    Products.productname AS Product,
    Products.productprice AS Price, 
    Supplier.suppliername AS Supplier 
FROM  
    Products 
    INNER JOIN Supplier ON Supplier.supplierid=Products.productsupplierID

回复收藏 0 原文

~没有更多了~