MySQL SQL：根据另一个表中的列值选择列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8676354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Selecting columns based on column value from another table
提问by Elroy Flynn
I have the following tables:
我有以下表格：
UserPrivileges:
+--------+------+------+------+
| UserID | Col1 | Col2 | Col3 |
+--------+------+------+------+
| 1 | 0 | 1 | 1 |
| 2 | 0 | 0 | 1 |
| 3 | 1 | 0 | 0 |
| 4 | 1 | 1 | 0 |
+--------+------+------+------+
Data:
+--------+------+------+------+
| DataID | Col1 | Col2 | Col3 |
+--------+------+------+------+
| 1 | A | B | C |
| 2 | D | E | F |
| 3 | G | H | I |
| 4 | J | K | L |
+--------+------+------+------+
My question at its simplest form doesn't has anything to do with the Datatable but I just explain it anyways so that I might be doing it the wrong way.
我的问题最简单的形式与表格没有任何关系，Data但我只是解释它，所以我可能会以错误的方式做它。
How would I select the Column names from UserPrivileges based on the value ? So that I can use the result in another query to select only those columns.
我将如何根据值从 UserPrivileges 中选择列名称？这样我就可以在另一个查询中使用结果来只选择那些列。
Something along these lines:
沿着这些路线的东西：
SELECT (COLUMNS_NAME_QUERY_FROM_UserPrivileges(UserID='#')) WHERE DataID = '#' FROM Data
SELECT (COLUMNS_NAME_QUERY_FROM_UserPrivileges(UserID='#')) WHERE DataID = '#' FROM Data
Or I don't mind a better way to manage user Privileges for specific columns.
或者我不介意管理特定列的用户权限的更好方法。
回答by Elroy Flynn
The answer depends upon your requirements for the result. Do you require a result with a consistent set of columns, regardless of user privs? If so, you could set the disallowed values to null (or some other special value) using a IF clause, e.g.,
答案取决于您对结果的要求。无论用户权限如何，您是否需要具有一致列集的结果？如果是这样，您可以使用 IF 子句将不允许的值设置为 null（或其他一些特殊值），例如，
SELECT IF (p.col1 = 0 THEN NULL ELSE d.col1) AS col1,
IF (p.col2 = 0 THEN NULL ELSE d.col2) AS col2,
IF (p.col3 = 0 THEN NULL ELSE d.col3) AS col3
FROM Data d,
UserPrivileges p
WHERE p.userId = '#'
AND d.DataId = '#'
Of course, the "special value" could be a problem, since you need a value that would never appear in the data. If you needed to know that difference between a null because the real value is null vs. null because it is a prohibited column then you can't use null.
当然，“特殊值”可能是一个问题，因为您需要一个永远不会出现在数据中的值。如果您需要知道空值之间的区别，因为真正的值是空值与空值，因为它是禁止的列，那么您不能使用空值。
Another approach would have you simple include the privilege indicator for each column appear in the result, and let your business logic use that to determine which values are visible to the user.
另一种方法是让您简单地在结果中包含每个列的权限指示符，并让您的业务逻辑使用它来确定哪些值对用户可见。
A very different approach would have the result set to contain only the allowed columns. In this case you'll need to build your sql statement dynamically. I don't know if you are doing this in a stored procedure or in a host language, but the basic idea is something like this:
一种非常不同的方法是让结果集只包含允许的列。在这种情况下，您需要动态构建 sql 语句。我不知道你是在存储过程中还是在宿主语言中这样做，但基本思想是这样的：
string sqlCmd = "SELECT "
+ (SELECT (FIELDS_NAME_QUERY(UserID='#')
FROM USER_PRIVILEGES
WHERE userid='#')
+ FROM data d
execute sqlCmd
"execute" meaning whatever you have available to execute a string as a sql command.
“执行”意味着您可以将字符串作为 sql 命令执行的任何内容。
more after clarification by OP:
OP澄清后的更多信息：
Ok, you need sql function that returns a string that looks like "colname1, colname2, ...". The following resembles what it would look like in sql server. syntax
好的，您需要一个 sql 函数来返回一个类似于“colname1, colname2, ...”的字符串。下面类似于它在 sql server 中的样子。句法
create function
FIELDS_NAME_QUERY (@userid int)
begin
select col1, col2, col3... INTO @col1priv, @col2priv, @col3priv FROM userPrivileges WHERE UserId = @UserId
declare @result varhcar(60)
set @result = ''
if (@col1priv = 1) @result = 'col1'
if (@col2priv = 1) @result = @result + ' ,col2'
if (@col3priv = 1) @result = @result + ' ,col3'
return @result
end
回答by Uday Sawant
have not tried it, but something like this should work
还没有尝试过，但这样的事情应该可行
SELECT (SHOW COLUMNS FROM table WHERE expr) FROM data WHERE DataID = '#'
Check this post for details - How can I get column names from a table in Oracle?
查看这篇文章了解详细信息 -如何从 Oracle 中的表中获取列名？
Let us know how you solve this...
让我们知道你是如何解决这个问题的...
