Как упорядочить запрос по значениям в разных столбцах

У меня есть набор записей с именем rsProductClass, который возвращается из таблицы в базе данных. Это очень простое SELECT * FROM Table WHERE ProductID = {ID Value Here} и таблица такая:

ProductID | UPPERTIER | LOWERTIER | NATIER  | OTHERTIER
1           20          60          10        10
2           10          90          NULL      NULL
3           NULL        40          NULL      5

Таблица может иметь или не иметь значение для каждого из различных уровней.

Что я хочу сделать, так это показать пользователю, какой столбец имеет наибольшее значение и каково имя этого столбца. Так, например, если вы просматриваете ProductID 2, то на странице должно отображаться сообщение «Скорее всего, это продукт LOWERTIER».

Мне нужно отсортировать запрос rsProductClass таким образом, чтобы он возвращал мне список столбцов в этом запросе, упорядоченный по значению в каждом столбце. Я хочу рассматривать значения NULL как нули.

Я пытался возиться с выполнением функций типа valuelist() и некоторых функций типа ArrayToList(), но он падает при значениях NULL. Скажем, я добавляю столбцы в массив, а затем использую ArraySort(), чтобы получить их в каком-то порядке, я получаю сообщение об ошибке, говорящее что-то вроде «Позиция 1 не является числовой», потому что она имеет значение NULL.

Это то, что может сделать ColdFusion? Я предполагаю, что это какой-то поворот набора записей, который выходит за рамки моих возможностей.


sql-server-2012 coldfusion coldfusion-11
person volume one    schedule 20.01.2015    source источник
comment
Какую СУБД вы используете? Это может быть то, что вы можете сделать в исходном запросе. По крайней мере, вы должны иметь возможность получить NULLs как 0s, используя COALESCE() -- например, SELECT ProductID, COALESCE(uppertier, 0) AS uppertier, etc.   -  person David Faber    schedule 20.01.2015
comment
Если вы используете MSSQL, вы можете сделать isNull(column, 0), чтобы изменить нулевые значения на 0.   -  person Matt Busche    schedule 20.01.2015
comment
Возможно, вам не удастся сделать то, что вы думаете, что хотите сделать. Например, измените запись 3 так, чтобы значение верхнего уровня было равно 50. Затем объясните, в каком порядке вы хотите, чтобы ваши записи отображались и почему.   -  person Dan Bracuk    schedule 20.01.2015
comment
@DavidFaber Я использую SQL Server 2014. Я только что подумал об использовании isNull (имя столбца, 0) для замены NULL нулями. Затем мне нужно подумать о том, как развернуть столбцы, чтобы они были расположены по значению в поле соответственно.   -  person volume one    schedule 20.01.2015
comment
@DanBracuk В этом случае UpperTier будет иметь самое высокое значение, поэтому он будет первым в списке для выбора. Если бы и UpperTier, и LowerTier были 50, то в идеале я хотел бы выбрать оба, но я достаточно счастлив, чтобы сообщить пользователю, что это, скорее всего, UpperTier, даже если его 50/50   -  person volume one    schedule 20.01.2015

Ответы (3)


arrow_upward
2
arrow_downward

Что-то вроде этого будет работать:

<cfquery name="tiers" datasource="...">
    SELECT ProductID, UPPERTIER VALUE, 'UPPERTIER' TIER
    WHERE  UPPERTIER IS NOT NULL
    UNION 
    SELECT ProductID, LOWERTIER VALUE, 'LOWERTIER' TIER
    WHERE  LOWERTIER IS NOT NULL
    UNION
    SELECT ProductID, OTHERTIER VALUE, 'OTHERTIER' TIER
    WHERE  OTHERTIER IS NOT NULL
    UNION
    SELECT ProductID, NATIER VALUE, 'NATIER' TIER
    WHERE  NATIER IS NOT NULL
    ORDER BY ProductID, VALUE
</cfquery>

<cfset productGroup = StructNew()>

<cfoutput query="tiers" group="ProductID">
  <cfset productGroup[ProductID].TIER = TIER>
  <cfset productGroup[ProductID].VALUE = VALUE>
</cfoutput>

<cfdump var="#productGroup#">

Начиная с ColdFusion 10 вы можете использовать <cfloop query="..." group="...">, до этого необходимо использовать <cfoutput>.

person Tomalak    schedule 20.01.2015

arrow_upward
2
arrow_downward

Если вы хотите развернуть свой запрос, вы можете сделать что-то вроде следующего. Я использовал COALESCE() вместо ISNULL() (в этой ситуации работает любой из них, но COALESCE() является стандартом ANSI). В столбце tier_rank будет указан ранг данного уровня, то есть уровень с наивысшим значением будет иметь ранг 1. Если есть два уровня, оба из которых имеют наивысшее значение, то оба будут иметь значение в tier_rank из 1 (поэтому следует использовать RANK() вместо ROW_NUMBER() — вы также можете использовать DENSE_RANK(), если это лучше соответствует вашим требованиям):

SELECT p1.product_id, p1.tier_name, p1.tier_value
     , RANK() OVER ( PARTITION BY p1.product_id ORDER BY p1.tier_value DESC ) tier_rank
  FROM (
  SELECT product_id, 'UPPERTIER' AS tier_name
       , COALESCE(uppertier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'LOWERTIER' AS tier_name
       , COALESCE(lowertier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'NATIER' AS tier_name
       , COALESCE(natier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'OTHERTIER' AS tier_name
       , COALESCE(othertier, 0) AS tier_value
    FROM products
) p1

Пожалуйста, посмотрите демонстрацию SQL Fiddle здесь.

Возможно, можно будет повторно выполнить приведенный выше несводной запрос, но я должен признать, что мои попытки сделать это не увенчались успехом.

person David Faber    schedule 20.01.2015
comment
@volumeone - если вы не можете изменить схему, я бы использовал этот подход. Тем не менее, учитывая похожие имена столбцов и то, как используются данные, более нормализованная структура может служить вам лучше. - person Leigh; 20.01.2015

arrow_upward
2
arrow_downward

Недавно мне пришлось сделать что-то подобное, и я заглянул в UNPIVOT в SQL Server. Следуя предложению развернуть ваш запрос, как сказал Дэвид, вы можете сделать что-то вроде этого. Это не добавляет столбец RANK, но упорядочивает значения.

SELECT ProductID, Tier, TierValue
FROM
(SELECT ProductID, ISNULL(UpperTier,0) UpperTier, ISNULL(LowerTier,0) LowerTier, ISNULL(NaTier,0) NaTier, ISNULL(OtherTier,0) OtherTier
FROM products) p
UNPIVOT
(TierValue FOR Tier IN 
(UpperTier, LowerTier, NaTier, OtherTier)
)AS unpvt
ORDER BY ProductID, TierValue Desc

SQL FIDDLE

person abbottmw    schedule 20.01.2015