需要帮助编写SQL查询 [英] Need help for writing SQL query

问题描述

LOCGUID状态

------------------

100 INA

100 INA

200 CUR

300 INA

300 CUR

400 INA



以上是我的示例数据。我想编写一个查询，它将为我提供仅具有状态INA的所有LOCGUID。



以下是我想要的样品输出



LOCGUID

- ---

100

400

解决方案

你可以简单地写一下



SELECT DISTINCT LOCGUID

FROM TABLE

WHERE STATUS ='INA'



对不起，我误解了列名。



如果你执行上面的查询，你会得到



100

$





作为结果。

< blockquote> OK - 让我们一步一步看看。



首先，一个非常基本的查询启动我们：

 从  LOCGUID 
  class =code-keyword> table  

这将返回LOCGUID列表（100,100,200,300,300,400）



我们只想要这些物品在Status列中有INA，所以我们可以添加WHERE子句：

 选择 LOCGUID 
 来自  table  
 其中状态= '  INA' 

我们现在有一个列表如下：（100,100,300,400）



有几种方法我们可以摆脱重复。对于更复杂的表达式，您可以使用GROUP BY子句，但我们可以简单地使用DISTINCT选择器：

 选择  distinct  LOCGUID 
 来自 表 
 其中​​状态= '  INA' 

太棒了。我们有独特的LOCGUID，其中Status ='INA':( 100,300,400）



您的清单将如下所示：



LOCGUID

----

100

300

$



错误地错过了'300'还是有其他标准可以消除它？

我认为它是LocGuid和Status是单独的列。如果是这种情况，则只需

  SELECT  LocGuid 
  FROM  TableName 
  WHERE  Status = '  INA' 

但是这似乎与您的结果示例不匹配。在你的例子中，你只有一次100而不是300.

基于此，你的意思是你想只有这样的LocGuid值不包含INA以外的其他状态。如果这就是你所追求的东西，那就像

  SELECT   DISTINCT  
 t1.LocGuid 
  FROM 表名t1 
  WHERE   NOT   EXISTS （ SELECT   1  
  FROM  TableName t2 
  WHERE  t2.LocGuid = t1.LocGuid 
  AND  t2.Status<> '   INA'）

LOCGUID Status
------------------
100 INA
100 INA
200 CUR
300 INA
300 CUR
400 INA

Above is my sample data.I want to write a query which will give me the all LOCGUID which has only Status INA.

Below is the sample output which I want

LOCGUID
----
100
400

解决方案

You can simply write

SELECT DISTINCT LOCGUID
FROM TABLE
WHERE STATUS = 'INA'

Sorry, I misunderstood with the column names.

If you execute the above query, you will get

100
300
400

as your result.

OK - let's look at this step by step.

First, a very basic query to start us off:

Select LOCGUID
from table

this will return the list of LOCGUIDs (100,100,200,300,300,400)

we only want the items that have INA in the column Status so we can add a WHERE clause:

Select LOCGUID
from table
where Status = 'INA'

We now have a list that looks like: (100,100,300,400)

There are a couple of ways we can get rid of duplicates. For more complex expressions you can use GROUP BY clauses, but we can simply use the DISTINCT selector:

Select distinct LOCGUID
from table
where Status = 'INA'

Great. We have the unique LOCGUIDs where Status = 'INA' : (100,300,400)

Your list will look like this:

LOCGUID
----
100
300
400

Did you miss the '300' by mistake or is there another criteria that would eliminate it?

I take it LocGuid and Status are separate columns. If that is the case then simply

SELECT LocGuid
FROM TableName
WHERE Status = 'INA'

However this does not seem to match to your result example. In your example you have 100 only once and 300 not at all.
Based on that, dou you mean you want to have only such LocGuid values that contain no other status than INA. If this is what you're after then perhaos something like

SELECT DISTINCT
       t1.LocGuid
FROM Tablename t1
WHERE NOT EXISTS (SELECT 1
                  FROM TableName t2
                  WHERE t2.LocGuid = t1.LocGuid
                  AND   t2.Status <> 'INA')

这篇关于需要帮助编写SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！