来个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;

Syl · Syl

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

凄美地 · 凄美地

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

哇哈哈哈 · 哇哈哈哈

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

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

豪～豪 · 豪～豪

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-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

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

Lay.Li · Lay.Li

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

		自动登录	找回密码
密码			立即注册

[已处理] 来个MySQL大佬

最佳答案

8 个回复

时代开发者征文活动

活字格高级认证

悬赏达人

活字格认证

圣诞拼拼乐

微信认证勋章