SQL Server 2012：选择文字并与Where中的文字进行比较

So, basically, in my query, I want to have a statuses column that will contain 1 or more statuses based on certain criteria. A user should be able to filter/search also on those status messages in that column. I'm not sure on the syntax to make this happen.

因此,基本上,在我的查询中,我希望有一个状态列,它将包含基于特定条件的一个或多个状态。用户应该能够过滤/搜索该列中的那些状态消息。我不确定语法是否能够实现这一点。

This selects 0 to 2 status divs in my column, which I display as is.

这将在我的列中选择0到2个状态div,我按原样显示。

Here's theoretically what I want to happen:

从理论上讲,这是我想要发生的事情:

Select fields, (select CASE WHEN root_directory IS NULL THEN '<div class="gray status">Unbuilt</div>' ELSE '' END + ' ' + (select top(1) CASE WHEN isnull(payment_received,0) = 1 THEN '' ELSE '<div class="red status">Unpaid</div>' END
FROM invoice C
WHERE C.id = B.id) as statuses
FROM table
WHERE statuses LIKE '%Unbuilt%'

Thoughts on how to WHERE LIKE my statuses column?

关于如何在我的状态栏中查询的想法?

Thanks.

1 个解决方案

#1

The WHERE clause cannot refer to columns in the select list. Logically speaking, the WHERE is applied before anything is selected.

WHERE子句不能引用选择列表中的列。从逻辑上讲,在选择任何内容之前应用WHERE。

You can, however, filter on the criteria by which you are setting the statuses result column. As a bonus, you will then be able to get rid of one of the CASE expressions in the select list, since the WHERE clause will fix which erstwhile case applies.

但是,您可以根据设置状态结果列的条件进行过滤。作为奖励,您将能够摆脱选择列表中的一个CASE表达式,因为WHERE子句将修复哪个以前的情况适用。

It's unclear to which table alias B in your original table applies, but table is the only real candidate. Supposing that B refers to table table, then, something along these lines will do what you appear to want:

目前还不清楚原始表中哪个表别名B适用,但表格是唯一真正的候选者。假设B引用表格表,那么这些行中的某些内容将执行您想要的内容:

Select B.fields, '<div class="gray status">Unbuilt</div> '
  + CASE C.payment_received
     WHEN 1 THEN ''
     ELSE '<div class="red status">Unpaid</div>'
    END
FROM
  table B
  JOIN invoice C ON C.id = B.id
WHERE root_directory IS NULL

#1