代码之家 › 专栏 › 技术社区 › Syntax Error ine

基于数字的过滤结果集

sql-server-2005 tsql sql-server sql

Syntax Error ine · 技术社区 · 6 年前

2 但没有进入 1 mdline 列,基于唯一的 mmatter 专栏?

这里是SQL小提琴: http://sqlfiddle.com/#!18/8c17e/2

SELECT top 1000
       [mdindex]
      ,[mmatter]
      ,[mdline]
      ,[mddesc]
FROM [desc]
WHERE [mmatter] IN (
    SELECT [mmatter]
    FROM [desc]
    GROUP BY [mmatter]
     HAVING COUNT(distinct [mdline]) > 1
)
order by mmatter

结果:

+---------+---------------+--------+-------------------------+
| mdindex | mmatter       | mdline | mddesc                  |
+---------+---------------+--------+-------------------------+
| 142     | X30539.000021 | 1      | Avocet, pied            |
+---------+---------------+--------+-------------------------+
| 143     | X30539.000021 | 2      | Margay                  |
+---------+---------------+--------+-------------------------+
| 111     | X30820.004199 | 1      | African buffalo         |
+---------+---------------+--------+-------------------------+
| 112     | X30820.004199 | 2      | Siskin, pine            |
+---------+---------------+--------+-------------------------+
| 113     | X30820.004199 | 3      | African jacana          |
+---------+---------------+--------+-------------------------+
| 114     | X30820.0042   | 2      | Caracara, yellow-headed |
+---------+---------------+--------+-------------------------+
| 115     | X30820.0042   | 3      | Whip-tailed wallaby     |
+---------+---------------+--------+-------------------------+
| 116     | X30820.0042   | 4      | Greater rhea            |
+---------+---------------+--------+-------------------------+
| 120     | X30820.004202 | 1      | Nuthatch, red-breasted  |
+---------+---------------+--------+-------------------------+
| 121     | X30820.004202 | 2      | Arctic tern             |
+---------+---------------+--------+-------------------------+
| 122     | X30820.004202 | 3      | Tyrant flycatcher       |
+---------+---------------+--------+-------------------------+
| 123     | X30820.004203 | 1      | Plover, three-banded    |
+---------+---------------+--------+-------------------------+
| 124     | X30820.004203 | 2      | Tortoise, radiated      |
+---------+---------------+--------+-------------------------+
| 129     | X30820.004204 | 2      | Laughing dove           |
+---------+---------------+--------+-------------------------+
| 130     | X30820.004204 | 3      | Iguana, marine          |
+---------+---------------+--------+-------------------------+

这是我要从上述结果集中返回的数据:

+-----+---------------+---+-------------------------+
| 114 | X30820.0042   | 2 | Caracara, yellow-headed |
+-----+---------------+---+-------------------------+
| 115 | X30820.0042   | 3 | Whip-tailed wallaby     |
+-----+---------------+---+-------------------------+
| 116 | X30820.0042   | 4 | Greater rhea            |
+-----+---------------+---+-------------------------+
| 129 | X30820.004204 | 2 | Laughing dove           |
+-----+---------------+---+-------------------------+
| 130 | X30820.004204 | 3 | Iguana, marine          |
+-----+---------------+---+-------------------------+

请注意,此结果集中的行没有 mdline公司

我尝试了这个,但是得到一个语法错误,可能是因为嵌套了太多的查询?

SELECT * FROM  
    ( SELECT top 1000
      [mdindex]
          ,[mmatter]
          ,[mdline]
          ,[mddesc]
    FROM [desc]
    WHERE [mmatter] IN (
        SELECT [mmatter]
        FROM [desc]
        GROUP BY [mmatter]
         HAVING COUNT(distinct [mdline]) > 1
        )
        order by mmatter
    )
WHERE mdline not like '1'

2 回复 | 直到 6 年前

Zohar Peled 6 年前

您可以通过添加 not exists 致您的where条款:

SELECT [mdindex]
    ,[mmatter]
    ,[mdline]
    ,[mddesc]
FROM [table1] t0
WHERE [mmatter] IN (
    SELECT [mmatter]
    FROM [table1]
    GROUP BY [mmatter]
    HAVING COUNT(distinct [mdline]) > 1
)
AND NOT EXISTS
(
    SELECT 1
    FROM [table1] t1
    WHERE t0.mmatter = t1.mmatter
    AND t0.mdline = 1
)
ORDER BY mmatter

Yogesh Sharma 6 年前

exists &安培; not exists :

select t1.*
from table1 t1
where not exists (select 1 
                  from table1 t2
                  where t2.mmatter = t1.mmatter and t2.mdline = 1 
                  ) and
       exists (select 1 
               from table1 t2
               where t2.mmatter = t1.mmatter and t2.mdline <> t1.mdline
               );