来个MySQL大佬

SELECT  A.customer_name,A.goods_name,A.code,A.wholesale_number,A.wholesale_price,A.wholesale_amount,B.purchase_quantity,C.purchase_quantity1,D.purchase_quantity2
FROM
    (SELECT T1.customer_name,T1.goods_name,T1.code,SUM(T1.wholesale_number) AS wholesale_number,SUM(T1.wholesale_price) AS wholesale_price,SUM(T1.wholesale_amount) AS wholesale_amount
        FROM 设备清单两表相加结果 T1
            GROUP BY T1.customer_name,T1.goods_name,T1.code) A
    LEFT JOIN
         (SELECT T2.customer_name,T2.goods_name,T2.code,SUM(T2.purchase_quantity) AS purchase_quantity FROM tb_purchase_request_hua T2
            GROUP BY T2.customer_name,T2.goods_name,T2.code) B
               ON A.customer_name=B.customer_name AND A.goods_name=B.goods_name AND A.code=B.code
    LEFT JOIN
      (SELECT T3.customer_name,T3.goods_name,T3.code,SUM(T3.purchase_quantity1) AS purchase_quantity1 FROM tb_purchase_receipt_hua T3
            GROUP BY T3.customer_name,T3.goods_name,T3.code) C
              ON A.customer_name=C.customer_name AND A.goods_name=C.goods_name AND A.code=C.code
    LEFT JOIN
      (SELECT T4.customer_name,T4.goods_name,T4.code,SUM(T4.purchase_quantity2) AS purchase_quantity2 FROM tb_installation_checklist_hua T4
            GROUP BY T4.customer_name,T4.goods_name,T4.code) D
              ON A.customer_name=D.customer_name AND A.goods_name=D.goods_name AND A.code=D.code;



报错View's SELECT contains a subquery in the FROM clause
设备清单两表相加结果  这个主表也是视图来了~可以解救下吗，大佬，，运行是可以正常，但建议视图就报错了

哇哈哈哈 发表于 2024-6-14 16:26
来个完整代码。大佬，小白一个

CREATE view view_A AS
(SELECT T1.customer_name,T1.goods_name,T1.code,SUM(T1.wholesale_number) AS wholesale_number,SUM(T1.wholesale_price) AS wholesale_price,SUM(T1.wholesale_amount) AS wholesale_amount
FROM 设备清单两表相加结果 T1
GROUP BY T1.customer_name,T1.goods_name,T1.code);

CREATE view view_B AS
(SELECT T2.customer_name,T2.goods_name,T2.code,SUM(T2.purchase_quantity) AS purchase_quantity
FROM tb_purchase_request_hua T2
GROUP BY T2.customer_name,T2.goods_name,T2.code);

CREATE view view_C AS
(SELECT T3.customer_name,T3.goods_name,T3.code,SUM(T3.purchase_quantity1) AS purchase_quantity1
FROM tb_purchase_receipt_hua T3
GROUP BY T3.customer_name,T3.goods_name,T3.code);

CREATE view view_D AS
(SELECT T4.customer_name,T4.goods_name,T4.code,SUM(T4.purchase_quantity2) AS purchase_quantity2
FROM tb_installation_checklist_hua T4
GROUP BY T4.customer_name,T4.goods_name,T4.code);

CREATE VIEW my_view AS
SELECT  A.customer_name,A.goods_name,A.code,A.wholesale_number,A.wholesale_price,A.wholesale_amount,B.purchase_quantity,C.purchase_quantity1,D.purchase_quantity2
FROM view_A A
LEFT JOIN view_B B ON A.customer_name=B.customer_name AND A.goods_name=B.goods_name AND A.code=B.code
LEFT JOIN view_C C ON A.customer_name=C.customer_name AND A.goods_name=C.goods_name AND A.code=C.code
LEFT JOIN view_D D ON A.customer_name=D.customer_name AND A.goods_name=D.goods_name AND A.code=D.code;

sql语法都差不多，发个demo或者 建表语句都行

mysql好像有的版本不支持视图子查询，可以把ABCD子查询先写成视图，再查这四个视图

凄美地 发表于 2024-6-14 16:18
mysql好像有的版本不支持视图子查询，可以把ABCD子查询先写成视图，再查这四个视图

来个完整代码。大佬，小白一个

1. SQL 语句中的错误提示 "View's SELECT contains a subquery in the FROM clause" 通常指的是在视图的 SELECT 语句中使用了子查询，这在某些数据库系统中是不允许的，或者有特定的限制。
   
2. 
   
3. 在你提供的 SQL 语句中，你试图从一个视图 `设备清单两表相加结果` 中选择数据，并且这个视图本身可能也是由一个子查询构成的。这可能是导致错误的原因。
   
4. 
   
5. 解决这个问题的方法之一是将视图的定义改为一个临时表或者直接在查询中使用子查询，而不是创建视图。以下是将你的视图转换为子查询的示例：
   
6. 
   
7. ```sql
   
8. SELECT
   
9.     A.customer_name,
   
10.     A.goods_name,
    
11.     A.code,
    
12.     A.wholesale_number,
    
13.     A.wholesale_price,
    
14.     A.wholesale_amount,
    
15.     B.purchase_quantity,
    
16.     C.purchase_quantity1,
    
17.     D.purchase_quantity2
    
18. FROM
    
19.     (SELECT
    
20.         T1.customer_name,
    
21.         T1.goods_name,
    
22.         T1.code,
    
23.         SUM(T1.wholesale_number) AS wholesale_number,
    
24.         SUM(T1.wholesale_price) AS wholesale_price,
    
25.         SUM(T1.wholesale_amount) AS wholesale_amount
    
26.      FROM
    
27.         (你的原始查询或表名) AS T1
    
28.      GROUP BY
    
29.         T1.customer_name,
    
30.         T1.goods_name,
    
31.         T1.code) A
    
32. LEFT JOIN
    
33.     (SELECT
    
34.         T2.customer_name,
    
35.         T2.goods_name,
    
36.         T2.code,
    
37.         SUM(T2.purchase_quantity) AS purchase_quantity
    
38.      FROM
    
39.         tb_purchase_request_hua AS T2
    
40.      GROUP BY
    
41.         T2.customer_name,
    
42.         T2.goods_name,
    
43.         T2.code) B
    
44.     ON A.customer_name = B.customer_name
    
45.    AND A.goods_name = B.goods_name
    
46.    AND A.code = B.code
    
47. LEFT JOIN
    
48.     (SELECT
    
49.         T3.customer_name,
    
50.         T3.goods_name,
    
51.         T3.code,
    
52.         SUM(T3.purchase_quantity1) AS purchase_quantity1
    
53.      FROM
    
54.         tb_purchase_receipt_hua AS T3
    
55.      GROUP BY
    
56.         T3.customer_name,
    
57.         T3.goods_name,
    
58.         T3.code) C
    
59.     ON A.customer_name = C.customer_name
    
60.    AND A.goods_name = C.goods_name
    
61.    AND A.code = C.code
    
62. LEFT JOIN
    
63.     (SELECT
    
64.         T4.customer_name,
    
65.         T4.goods_name,
    
66.         T4.code,
    
67.         SUM(T4.purchase_quantity2) AS purchase_quantity2
    
68.      FROM
    
69.         tb_installation_checklist_hua AS T4
    
70.      GROUP BY
    
71.         T4.customer_name,
    
72.         T4.goods_name,
    
73.         T4.code) D
    
74.     ON A.customer_name = D.customer_name
    
75.    AND A.goods_name = D.goods_name
    
76.    AND A.code = D.code;
    
77. ```
    
78. 
    
79. 请注意，我将 `设备清单两表相加结果` 替换成了 `(你的原始查询或表名)`，你需要将其替换为实际的表名或者原始查询。
    
80. 
    
81. 如果问题依旧存在，可能需要检查数据库的具体错误信息和文档，以确定是否是数据库系统的限制或者需要其他特定的解决方案。

复制代码

凄美地 发表于 2024-6-14 16:34
CREATE view view_A AS
(SELECT T1.customer_name,T1.goods_name,T1.code,SUM(T1.wholesale_number) AS  ...

多谢大佬！可以了~~~~

豪～豪 发表于 2024-6-17 08:56

按上面大佬可以了，谢谢大佬

感谢各位大佬的支持~
后边有问题呢欢迎您继续发帖交流