查询以获取列的唯一性 [英] Query to get a unique for a column

问题描述

我有一个要求，我们从2个表中获取这样的数据

标签和值是2列。

标签价值
 ==== ===== 
颜色RED 
颜色绿色
颜色蓝色
主题派对
主题排灯节
主题圣诞节
主题Holi 

现在，



如果我将红色传递给查询，它必须删除所有颜色标签，它应该返回一些东西像这样：

标签价值
 ==== ===== 
颜色RED 
主题派对
主题排灯节
主题圣诞节
主题Holi 

我正在寻找一个SQL查询来显示列数据的单个匹配行。



就在上面的例子中，当我将Red传递给value列（并且还为Tag列着色）时，除了所有t之外，什么都不应该改变除了我选择的颜色标签行之外，必须删除它们。其余的数据保持不变。





这些数据是从2张桌子共享的。



和检索数据的查询将是这样的。

  SELECT   DISTINCT  Make.tag，fest.value  FROM 制作 JOIN  fest  ON  make.ID = fest.ID 
  WHERE （fest.ID  IS   NOT   NULL ）和（不 fest.ID = ' '） AND  
（Make.tag = '  color' 或 Make.tag = ' 主题'） ORDER   BY  Make.tag，fest.ID; 

谢谢！

解决尝试这个：

  SELECT  SQ.tag 
，SQ.value 
  FROM  
（
  SELECT   DISTINCT  
 Make.tag 
，fest.value 
，ROW_NUMBER（） OVER （ ORDER   BY  Make.tag，fest.ID） AS  RID 
  FROM 生成
  JOIN  fest 
  ON  
 make.ID = fest.ID 
  WHERE  
  NULLIF （fest.ID，' '） IS   NOT   NULL  
  AND  
（Make.tag = '  color'  OR  Make.tag = ' 主题'）
） AS  SQ 
  WHERE  
  CASE   WHEN 标签= ' 颜色'  AND 值= '  RED'  那么  1  
  WHEN  Tag = ' 主题' 那么  1  
  ELSE   0  
  END  =  1  
  ORDER   BY  RID 

根据这么小部分的信息，我最好的猜测是：

  SELECT  m.Tag，f.Value 
  FROM  make  AS  m  JOIN  fest  AS  f  ON  m.ID = f.ID 
  WHERE  m.Tag =  @ tag   AND  f.Value=@val 
  UNION   ALL  
  SELECT  m.Tag，f.Value 
  FROM 使 AS  m  JOIN  fest  AS  f  ON  m.ID = f.ID 
  WHERE  m.Tag ！=  @ tag  

Hi,

I have a requirement where we get data something like this from 2 tables
Tag and value are 2 columns.

tag      Value
====     =====
Color    RED
Color    GREEN
Color    BLUE
Theme    PARTY
Theme    Diwali
Theme    Christmas
Theme    Holi

Now,

If I pass red to the query, it must remove all the color tags and it should return something like this:

tag      Value
====     =====
Color    RED
Theme    PARTY
Theme    Diwali
Theme    Christmas
Theme    Holi

I was looking for a SQL query to show a single matched row for the column data.

Just in the above example, When I pass Red to the value column(and also color to the Tag column), Nothing should change, except all the color tags rows must be removed except the one i selected. rest of the data remains same.


this data is being shared from 2 tables.

and the query to retrieve data will be something like this.

SELECT DISTINCT Make.tag, fest.value FROM Make JOIN fest ON make.ID = fest.ID 
WHERE (fest.ID IS NOT NULL) and ( not fest.ID = '') AND 
( Make.tag = 'color' OR Make.tag = 'Theme') ORDER BY Make.tag, fest.ID;

Thank you!

解决方案

Try this:

SELECT SQ.tag
      ,SQ.value
FROM
(
SELECT DISTINCT
       Make.tag
     , fest.value
     , ROW_NUMBER() OVER (ORDER BY Make.tag, fest.ID) AS RID
FROM Make
JOIN fest
    ON
        make.ID = fest.ID
WHERE
     NULLIF(fest.ID, '') IS NOT NULL
    AND
    ( Make.tag = 'color' OR Make.tag = 'Theme')
)AS SQ
WHERE
     CASE WHEN Tag = 'Color' AND Value = 'RED' THEN 1
          WHEN Tag = 'Theme' THEN 1
          ELSE 0
     END = 1
ORDER BY RID

Based on such small portion of information, my best guess is:

SELECT m.Tag, f.Value
FROM Make AS m JOIN fest AS f ON m.ID = f.ID 
WHERE m.Tag = @tag AND f.Value=@val
UNION ALL
SELECT m.Tag, f.Value
FROM Make AS m JOIN fest AS f ON m.ID = f.ID
WHERE m.Tag != @tag 

这篇关于查询以获取列的唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！