SQL：根据另一个表中的列值选择列

本文介绍了SQL：根据另一个表中的列值选择列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下表格：

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

我的问题在其最简单的形式与 Data 表没有任何关系，但我只是解释它所以我可能以错误的方式做。

My question at its simplest form doesn't has anything to do with the Data table but I just explain it anyways so that I might be doing it the wrong way.

如何根据值从UserPrivileges中选择列名？因此，我可以在另一个查询中使用结果仅选择这些列。

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.

这些行的内容：

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.

推荐答案

答案取决于您对结果的要求。您是否需要具有一致的列集合的结果，而不考虑用户priv？如果是这样，您可以使用IF子句将不允许的值设置为null（或某些其他特殊值），例如

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

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 = '#'

当然，特殊值可能是一个问题，因为您需要一个不会出现在数据中的值。如果你需要知道一个null之间的差异，因为实际值是null对null，因为它是一个禁止列，那么你不能使用null。

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.

一种非常不同的方法会将结果集设置为仅包含允许的列。在这种情况下，您需要动态构建您的sql语句。我不知道你是在存储过程还是主机语言中这样做，但基本思想是这样的：

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:

string sqlCmd = "SELECT " + (SELECT (FIELDS_NAME_QUERY(UserID='#') FROM USER_PRIVILEGES WHERE userid='#') + FROM data d execute sqlCmd

execute意味着你可以执行一个字符串作为sql命令。

"execute" meaning whatever you have available to execute a string as a sql command.

好的。

，您需要sql函数返回一个看起来像colname1，colname2，...的字符串。下面类似于sql server中的样子语法 create function FIELDS_NAME_QUERY（@userid int） 开始 选择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

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