哇哈哈哈 发表于 2024-6-14 16:04:02

来个MySQL大佬


SELECTA.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:04:03

哇哈哈哈 发表于 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
SELECTA.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;

Syl 发表于 2024-6-14 16:09:12

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

凄美地 发表于 2024-6-14 16:18:04

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

哇哈哈哈 发表于 2024-6-14 16:26:10

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

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

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

SQL 语句中的错误提示 "View's SELECT contains a subquery in the FROM clause" 通常指的是在视图的 SELECT 语句中使用了子查询，这在某些数据库系统中是不允许的，或者有特定的限制。

在你提供的 SQL 语句中，你试图从一个视图 `设备清单两表相加结果` 中选择数据，并且这个视图本身可能也是由一个子查询构成的。这可能是导致错误的原因。

解决这个问题的方法之一是将视图的定义改为一个临时表或者直接在查询中使用子查询，而不是创建视图。以下是将你的视图转换为子查询的示例：

```sql
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
      (你的原始查询或表名) AS 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 AS 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 AS 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 AS 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;
```

请注意，我将 `设备清单两表相加结果` 替换成了 `(你的原始查询或表名)`，你需要将其替换为实际的表名或者原始查询。

如果问题依旧存在，可能需要检查数据库的具体错误信息和文档，以确定是否是数据库系统的限制或者需要其他特定的解决方案。

哇哈哈哈 发表于 2024-6-17 09:01:11

凄美地 发表于 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 09:53:41

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


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

Lay.Li 发表于 2024-6-17 10:59:19

感谢各位大佬的支持~
后边有问题呢欢迎您继续发帖交流:i0tw2:
页: [1]
查看完整版本: 来个MySQL大佬