SQL查询以选择仅包含不包含一种语言的产品。

huangapple
go评论118阅读模式
英文:

SQL QUERY FOR selecting only products which does not contain one language

问题

我需要仅选择那些*没有语言ID为"en-ZA"*的产品。

在下面的例子中，我需要仅选择产品编号为"102"。

产品编号 语言ID
100 En-Us
100 En-ZA
100 fr
101 En-Us
101 En-ZA
101 fr
102 En-Us
102 fr
英文:

I need to select only those product which is not having language-id "en-ZA".

In the below example I need to select only product number "102".

ProductNumber Language-ID
100 En-Us
100 En-ZA
100 fr
101 En-Us
101 En-ZA
101 fr
102 En-Us
102 fr

答案1

得分: 2

你可以通过以下查询来实现这个目的，使用 CASE WHEN：

  1. SELECT ProductNumber 
  2. FROM TableName
  3. GROUP BY ProductNumber
  4. HAVING SUM(CASE WHEN [Language-ID]='En-ZA' THEN 1 ELSE 0 END) = 0

同样的查询也可以使用 NOT EXISTS 来写成下面这样：

  1. SELECT ProductNumber
  2. FROM TableName t1
  3. WHERE NOT EXISTS (
  4.   SELECT 1
  5.   FROM TableName  t2
  6.   WHERE [Language-ID] = 'En-ZA'
  7.   AND t2.ProductNumber = t1.ProductNumber
  8. )
  9. GROUP BY ProductNumber
英文:

You can achieve this by following query using CASE WHEN

  1. SELECT ProductNumber 
  2. FROM TableName
  3. GROUP BY ProductNumber
  4. HAVING SUM(CASE WHEN [Language-ID]='En-ZA' THEN 1 ELSE 0 END) = 0

The same query can be written using NOT EXISTS like the following.

  1. SELECT ProductNumber
  2. FROM TableName t1
  3. WHERE NOT EXISTS (
  4.   SELECT 1
  5.   FROM TableName  t2
  6.   WHERE [Language-ID] = 'En-ZA'
  7.   AND t2.ProductNumber = t1.ProductNumber
  8. )
  9. GROUP BY ProductNumber

答案2

得分: 0

尝试识别可能包含 "en-ZA" 的产品编号，使用以下代码：

  1. select distinct ProductNumber
  2. from TABLE
  3. where Language-ID = 'en-ZA'

这将为您提供包含 'en-ZA' 的所有产品编号的列表。

使用该列表，您可以对主列表进行排序，如下所示：

  1. select ProductNumber, Language-ID
  2. from TABLE
  3. where ProductNumber not in (
  4.   select distinct ProductNumber
  5.   from TABLE
  6.   where Language-ID = 'en-ZA'
  7. )
英文:

Try to identify Product numbers which may contain "en-ZA" by using:

  1. select distinct ProductNumber
  2. from TABLE
  3. where Language-ID = 'en-ZA'

That gives you a list of all Product numbers which contain 'en-ZA'.

Using that list you can sort your main list like:

  1. select ProductNumber, Language-ID
  2. from TABLE
  3. where ProductNumber not in (
  4.   select distinct ProductNumber
  5.   from TABLE
  6.   where Language-ID = 'en-ZA'
  7. )

答案3

得分: 0

以下是使用“left join”进行操作的方法：

  1. select t.ProductNumber
  2. from (
  3.   select distinct ProductNumber
  4.   from mytable
  5.   where Language_ID != 'En-ZA'
  6. ) t
  7. left join (
  8.   select *
  9.   from mytable
  10.   where Language_ID = 'en-ZA'
  11. ) as s on s.ProductNumber = t.ProductNumber
  12. where s.ProductNumber is null

我们确定了Language_ID != 'En-ZA'（100, 101, 102）的行，并将其与具有Language_ID = 'en-ZA'（100, 101）的行进行匹配，然后仅选择未匹配的行，使用where s.ProductNumber is null。

演示链接在这里

英文:

This is a way to do it using left join :

  1. select t.ProductNumber
  2. from (
  3.   select distinct ProductNumber
  4.   from mytable
  5.   where Language_ID != 'En-ZA'
  6. ) t
  7. left join (
  8.   select *
  9.   from mytable
  10.   where Language_ID = 'en-ZA'
  11. ) as s on s.ProductNumber = t.ProductNumber
  12. where s.ProductNumber is null

We determine the rows where Language_ID != 'En-ZA' (100,101,102) and match it with the ones having Language_ID = 'en-ZA' (100,101), then select only the ones that are not matched using where s.ProductNumber is null

Demo here

答案4

得分: 0

使用"NOT EXISTS" - 它简单且相当快速

  1. select ProductNumber, Language-ID
  2. from YourTable as YT1
  3. where not exists (
  4.   select 1
  5.   from YourTable as YT2
  6.   where YT2.Language-ID = 'en-ZA'
  7.   and YT2.ProductNumber = YT1.ProductNumber
  8. )
英文:

use NOT EXISTS - it is simple and quite fast

  1. select ProductNumber, Language-ID
  2. from YourTable as YT1
  3. where not exists (
  4.   select 1
  5.   from YourTable as YT2
  6.   where YT2.Language-ID = 'en-ZA'
  7.   and YT2.ProductNumber = YT1.ProductNumber
  8. )

huangapple
  • 本文由 发表于 2023年5月15日 14:50:39
  • 转载请务必保留本文链接：https://go.coder-hub.com/76251514.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定