mysql - 级别的 SQL 表模式 - 子级别类型表

Question

我创建了四个表格，给出了分层类别列表。喜欢类别表；

+----+--------------+---------------+----------------+
| ID | CategoryName | Ref. Category | Something else |
+----+--------------+---------------+----------------+

引用。类别(链接到第 1 列)

举个例子；

+---+------------+---+------+
| 1 | Elektronik | 0 | ...  |
| 2 | Drink      | 0 | ...  |
| 3 | Computer   | 1 | ...  |
| 4 | Mouse      | 3 | ...  |
| 5 | Keyboard   | 3 | ...  |
| 6 | Juice      | 2 | ...  |
| 7 | Mouse Cable| 4 | ...  |
+---+------------+---+------+

员工表:

+------------+------+---------+-----------+
| EmployeeID | Name | Surname | Something |
+------------+------+---------+-----------+

订单表:

+---------+-------+------+-----------+
| OrderID | EmpID | Date | Something |
+---------+-------+------+-----------+

订单明细表:

+---------+-----------+----------+-------+------------+
| OrderID | ProductID | Quantity | Price | CategoryID |
+---------+-----------+----------+-------+------------+ 

我想获得特定员工特定类别及以上级别(Ref. Categories up to 0 level)的总价，如下所示对于 EmployeeID 10

+-----------+------------+--------------+---------------+
| EmployeeID| CategoryId | CategoryName | Total Prices  |
| 10        |    7       | Mouse Cable  | 3000          |
| 10        |    4       | Mouse        | 5000          |
| 10        |    3       | Computer     | 12000         |
| 10        |    1       | Elektronik   | 35000         |
+-----------+------------+--------------+---------------+

我想创建一个以特定 RefID 开始的查询，然后获取 CategoryID，在下一步中，RefId 将是 CategoryID 并继续这样直到 RefID 0

Answer 1

答案会很长，但下面的查询适合您

DECLARE @rowResult TABLE (
    EmployeeID int,
    CategoryID int,
    Total int);
DECLARE @EmployeeID int;
DECLARE @CategoryID int;
DECLARE @RefID int;

SET @EmployeeID = 100;
SET @CategoryID = 7;

SELECT @RefID = RefID FROM CATEGORIES WHERE CategoryID = @CategoryID;

WHILE @RefID > 0
BEGIN

    INSERT INTO @rowResult
    SELECT e.EmployeeID, d.CategoryID, SUM(d.Quantity * d.Price) AS 'Total'
    FROM Employees e 
      INNER JOIN Orders o ON o.EmpID = e.EmployeeID
      INNER JOIN OrderDetails d ON d.OrderID = o.OrderID
    WHERE e.EmployeeID = @EmployeeID AND d.CategoryID = @CategoryID
    GROUP BY e.EmployeeID, d.CategoryID ;

    SET  @CategoryID = @RefID;
SELECT @RefID = RefID FROM CATEGORIES WHERE CategoryID = @CategoryID;
END

SELECT * FROM @rowResult
GO