如何在SQL中检索非空数据和非重复数据？ [英] How do I retrieve both non empty and non duplicate data in SQL?

问题描述

我有重复的数据和有时为空的列（取决于它是什么产品）。



注意：所有数据都来自1个表。



例如：

I have data that duplicates and a column that is sometimes null (depending on what product it is).

Note: All the data is from 1 table.

Example:

ID         |Generic Name|Description
0001        Cetirizine   Allerzet 10mg
0002        Cetirizine   Alnix 10mg
0003                     Disposable tube
0004        Paracetamol  Biogesic Tablet

我的计划是将通用名称检索到一个没有重复项的组合框中。



我尝试了什么：



到目前为止，我有这个代码来消除空数据。我在尝试从重复项中只检索一个通用名称时遇到了麻烦。



代码：

My plan is to retrieve the Generic Name into a combo box without the duplicates.

What I have tried:

So far I have this code to eliminate the null data. I'm having trouble trying to retrieve only 1 generic name from the duplicates.

Code:

select Item_GenName from ItemMasterlistTable where nullif(convert(varchar,Item_GenName),'') is not null"

推荐答案

问题是，当你有倍数时，你必须决定SQL要返回哪一行 - 它不知道你是否想要Cetirizine是Allerzet 10mg或Alnix 10mg而且它不想扔掉信息。

您可以选择第一个或最后一个：

The problem is that when you have multiples, you have to decide for SQL which row to return - it doesn't know if you want "Cetirizine" to be "Allerzet 10mg" or "Alnix 10mg" and it hates to throw away information.
You can select the first or last:

SELECT MIN(ID) FROM ItemMasterlistTable GROUP BY [Generic Name] HAVING [Generic Name] IS NOT NULL

SELECT MAX(ID) FROM ItemMasterlistTable GROUP BY [Generic Name] HAVING [Generic Name] IS NOT NULL

然后使用JOIN检索您想要的信息：

And then use JOIN to retrieve the info you want:

SELECT g.ID, i.[Generic Name], i.Description 
FROM (SELECT MIN(ID) as ID 
      FROM ItemMasterlistTable 
      GROUP BY [Generic Name] 
      HAVING [Generic Name] IS NOT NULL) g
JOIN ItemMasterlistTable i 
     ON g.ID = i.ID

select [Generic Name] from Table where isnull([Generic Name],'')<>'' group by [Generic Name]

这篇关于如何在SQL中检索非空数据和非重复数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！