在查询中使用字段的值作为列名

发布于 2024-09-06 14:09:32 字数 750 浏览 7 评论 0原文

我试图找出一种方法来仅获取与单元所在的操作步骤相关的特定字段。我有一个包含单元及其操作步骤的表，然后我有另一个表，其中每个步骤都有一列。我只想提取与该单元当前步骤相关的列。

这是我的尝试，但我似乎找不到一种方法来使用操作的值作为参考。我的问题是我需要与子查询中 t1.operation 的值匹配的字段，而不是与 t1.operation 的值匹配的字段。这有什么意义吗？

SELECT t1.*,
  (SELECT t1.operation
   FROM report_tables.roc_capacity_standards As t2
   WHERE t1.assembly=t2.part_number) As standard
FROM report_tables.resource_transactions As t1
WHERE t1.date BETWEEN '2010-06-01'
                  AND '2010-06-19'
GROUP BY job, employee
ORDER BY operation;

在网上搜索后，我发现有人说这是不可能的，主要是因为 MySQL 不知道它正在查询什么，以便根据索引等进行优化。不过，这与动态表名有关，所以我不确定它是相同的。

在这里找到：参考

原文

I'm trying to figure out a way to only grab the specific field that pertains to an operation step a unit is in. I have one table with units and their operation step, then I have another table that has a column for each step. I want to pull only the column that relates to that unit's current step.

Here is my attempt but I can't seem to find a way to use the value of operation as a reference. My problem is I need the field that matches the value of t1.operation in the subquery rather than the value of t1.operation. Did that make any sense?

SELECT t1.*,
  (SELECT t1.operation
   FROM report_tables.roc_capacity_standards As t2
   WHERE t1.assembly=t2.part_number) As standard
FROM report_tables.resource_transactions As t1
WHERE t1.date BETWEEN '2010-06-01'
                  AND '2010-06-19'
GROUP BY job, employee
ORDER BY operation;

After searching online I found that one person said it is not possible mainly because MySQL would not know what it is querying in order to optimize it based on indexes etc. That was pertaining to dynamic table names though so I wasn't sure it was the same.

That was found here: Reference

分享到QQ

分享到微博

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

发布评论

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

童话 2024-09-13 14:09:32

我相信您的问题在于表的设计而不是查询。

如果我是正确的，您的表格看起来与此类似：

units (unit_id, current_operation, date, ...)
operations (unit_id, operation_1_value, operation_2_value, operation_3_value, ...)

这是糟糕的设计，因为它会导致像您现在这样的问题。

更好的设计是

units (unit_id, *other unit specific info* ...)
operations (operation_id, operation_name, *other general operation info*)
unit_operation (unit_id, operation_id, *operation_results, *operation_values, *date_started, *date_ended)

I believe your problem lies in the design of your tables and not your query.

If I am correct, your tables look similar to this:

units (unit_id, current_operation, date, ...)
operations (unit_id, operation_1_value, operation_2_value, operation_3_value, ...)

This is poor design as it leads to problems like your having now.

A better design would be

units (unit_id, *other unit specific info* ...)
operations (operation_id, operation_name, *other general operation info*)
unit_operation (unit_id, operation_id, *operation_results, *operation_values, *date_started, *date_ended)

回复收藏 0 原文

~没有更多了~