从提取多个值的列中查询单个值

Using the following query:

使用以下查询:

    SELECT pe.prodtree_element_name_l, MAX(rs.resource_value) AS resource_value
    FROM prodtree_element pe
    LEFT JOIN resource_shortstrings rs
        ON pe.prodtree_element_name_l_rk = rs.resource_key
    WHERE rs.language_id = '5'
        AND pe.prodtree_element_name_l <> ''
    GROUP BY prodtree_element_name_l

I'm trying to figure out how to grab ANY of the "resource_value". The problem being that while this works for a number of other queries, I have one particular table that uses ntext datatypes instead of varchars (which can't utilize the MAX function). So basically, MAX doesn't work here. Is there a substitute I can use on MS SQL Server 2005?

我想弄清楚如何抓住任何“resource_value”。问题在于,虽然这适用于许多其他查询,但我有一个特定的表使用ntext数据类型而不是varchars(它不能使用MAX函数)。所以基本上,MAX在这里不起作用。我可以在MS SQL Server 2005上使用替代品吗?

I need the prodtree_element_name_l column grouped, but I only need one value from the resource_value column, and I don't care what it is as most of them are identical regardless (although some are not, hence I can't group that one as well).

我需要将prodtree_element_name_l列分组,但我只需要来自resource_value列的一个值,我不在乎它是什么,因为它们中的大多数都是相同的(尽管有些不是,因此我也不能将那个组合在一起) )。

UPDATE:

Whoops, I was wrong, prodtree_element_name_l is ALSO an NTEXT. That might help a little :p

哎呀,我错了,prodtree_element_name_l也是一个NTEXT。这可能有点帮助:p

3 个解决方案

#1

This will get the first random entry

这将获得第一个随机条目

SELECT DISTINCT 
     pe.prodtree_element_name_l, 
    (SELECT TOP 1 rs2.resource_value
    FROM resource_shortstrings rs2
    WHERE rs2.language_id = '5'
      AND rs2.resource_key = pe.prodtree_element_name_l_rk) AS "resource_value"
FROM prodtree_element pe
LEFT JOIN resource_shortstrings rs
    ON pe.prodtree_element_name_l_rk = rs.resource_key
WHERE rs.language_id = '5'
    AND pe.prodtree_element_name_l IS NOT NULL
--GROUP BY prodtree_element_name_l

NOTE

In your query you aare using a LEFT JOIN but also a filter on the left joined table, therefore limiting the recordset. I LEFT that in place as I figured it would change your results...but there is not point in doing the LEFT JOIN.

在您的查询中,您使用LEFT JOIN但在左连接表上也使用过滤器,因此限制了记录集。我认为这会改变你的结果......但是没有必要做LEFT JOIN。

EDIT

Based on feedback in comments, I commented out the group by and switched to a distinct

根据评论中的反馈,我评论了小组,并切换到一个独特的

#2

  SELECT pe.prodtree_element_name_l, MAX(CAST(rs.resource_value AS NVARCHAR(MAX))) AS resource_value
    FROM prodtree_element pe
    LEFT JOIN resource_shortstrings rs
        ON pe.prodtree_element_name_l_rk = rs.resource_key
    WHERE rs.language_id = '5'
        AND pe.prodtree_element_name_l <> ''
    GROUP BY prodtree_element_name_l

#3

I received the error:

我收到了错误:

The data types ntext and varchar are incompatible in the not equal to operator.

Unless I missed something?

除非我错过了什么?

Edit: CHECK TOP.

编辑:检查顶部。

#1