Mysql 嵌套查询问题

发布于 2024-10-16 06:05:33 字数 470 浏览 2 评论 0原文

下面是我的查询，我收到“子查询返回超过 1 行”错误：

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`) 
  VALUES(
      (SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1),
      (SELECT `id` FROM `milk_producer` WHERE active='1'),
      'N'
  )

SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1

返回多个值。

我希望这些多个值将插入到表中，例如：

1 03 N
2 03 N
3 03 N

原文

below is my query and I am getting a "Subquery returns more than 1 row" error:

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`) 
  VALUES(
      (SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1),
      (SELECT `id` FROM `milk_producer` WHERE active='1'),
      'N'
  )

SELECT `id` FROM `billing_period` ORDER BY `billing_start_date` DESC LIMIT 0,1

returns multiple values.

I want that these multiple values will insert into table, e.g:

1 03 N
2 03 N
3 03 N

分享到QQ

分享到微博

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

发布评论

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

恏ㄋ傷疤忘ㄋ疼 2024-10-23 06:05:33

问题是您无法执行INSERT INTO ... SELECT和以及INSERT INTO ... VALUES。选择一个。

在您的情况下，按行插入数据时存在不合逻辑的相关性 - 除非 milk_ Producer 只有 1 行 WHERE active='1'。同样，如果您尝试加载多行，为什么要将内部查询LIMIT限制为 1 个结果？

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`)
    SELECT `id`, ??, 'N' FROM `billing_period` 
    ORDER BY `billing_start_date` DESC

The problem is you can't perform an INSERT INTO ... SELECT and and INSERT INTO ... VALUES. Choose one.

In your case, there is an illogical correlation upon inserting data by row -- unless milk_producer only has 1 row WHERE active='1'. Similarly, if you're trying to load multiple rows, why are you LIMITing the inner query to 1 result?

INSERT INTO billing_temp (`billing_period_id`,`soc_id`,`bill_y_n`)
    SELECT `id`, ??, 'N' FROM `billing_period` 
    ORDER BY `billing_start_date` DESC

回复收藏 0 原文