AND在单列中查询 [英] AND query in single column
问题描述
嗨组
我有一个相当特殊的问题，而且我真的不知道怎么解决这个问题
SQL语句：
给定一个视图（v），结果是：
ID X
- ---------------
1 a
1 b
2 a
2 c
3 a
我想用以下内容查询视图：
SELECT ID FROM v WHERE（X =''''和X =''b''）会导致：
ID
---- -------------
1
或其他情况：
SELECT ID FROM v WHERE（X =''''或X =''c''）
会给：
ID
-----------------
1
2
3
怎么办呢？
TIA
bernhard
-
www.daszeichen.ch
删除ni xspam回复
Bernhard Sturm（st**********@datacomm.ch）写道：我有一个相当特殊的问题，我真的不知道如何在SQL语句中解决这个问题：
给定一个视图（v），结果是：
ID X
-----------------
1 a
1 b
2 a
2 c
我想用以下内容查询视图：
SELECT ID FROM v WHERE（X =''a''AND X =''b''）这将导致：
SELECT ID FROM v WHERE X =''a''
INTERSECT
SELECT ID FROM v WHERE X =''b''
除了该语法仅适用于SQL 2005.对于SQL 2000，你
可以这样做：
SELECT ID FROM va
WHERE X =''a''
AND EXISTS（SELECT *
FROM vb
WHERE a.ID = b.ID
AND bX = 'b''）
ID
-----------------
1
或在另一种情况下：
SELECT ID FROM v WHERE（X =''''或X =''c''）
将给出：
SELECT ID FROM v WHERE X =''a''
UNION
SELECT ID FROM v WHERE X =''b''
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
假设（id，x）是唯一的。以下给出了第一个结果
你要求：
SELECT id
FROM v
在哪里x IN（''A''，''B''）
GROUP BY id
有COUNT（*）= 2
此操作称为关系部门，您可以在该名称下找到其他
示例和讨论。
您的第二个OR查询：
SELECT id
FROM v
WHERE x IN（''A''，''C''）
GROUP BY id
-
David Portas
SQL Server MVP
-
David Portas写道：
SELECT id
FROM v
WHERE x IN（''A''，''B''）
GROUP BY id
HAVING COUNT（*）= 2
此操作称为关系分部，您可以在该名称下找到其他
示例和讨论。
您的第二个OR查询：
SELECT id
FROM v
WHERE x IN（''A''，''C''）
GROUP BY id
非常感谢您的回答。离开你的建议我已经来了以下解决方案（这似乎适用于我的目的）：
SELECT id
FROM v
WHERE x =''''和EXISTS
（SELECT id FROM v WHERE x =''b''）
GROUP BY id
这可以轻松采用''或''案例：
SELECT id
FROM v
WHERE x =''''或EX EXTS
（SELECT id FROM v WHERE x =''b''）
GROUP BY id
您怎么看？
bernhard
-
www.daszeichen.ch
删除nixspam回复
Hi group
I have a rather peculiar question, and I really don''t know how to solve
this within an SQL statement:
Given a view (v), that results in:
ID X
-----------------
1 a
1 b
2 a
2 c
3 a
I''d like to query the view with something like:
SELECT ID FROM v WHERE (X=''a'' AND X=''b'') which would result in:
ID
-----------------
1
or in another case:
SELECT ID FROM v WHERE (X=''a'' OR X=''c'')
would give:
ID
-----------------
1
2
3
how can this be done?
TIA
bernhard
--
www.daszeichen.ch
remove nixspam to reply
Bernhard Sturm (st**********@datacomm.ch) writes:I have a rather peculiar question, and I really don''t know how to solve
this within an SQL statement:
Given a view (v), that results in:
ID X
-----------------
1 a
1 b
2 a
2 c
3 a
I''d like to query the view with something like:
SELECT ID FROM v WHERE (X=''a'' AND X=''b'') which would result in:
SELECT ID FROM v WHERE X = ''a''
INTERSECT
SELECT ID FROM v WHERE X = ''b''
Except that that syntax only works in SQL 2005. For SQL 2000, you
could do:
SELECT ID FROM v a
WHERE X = ''a''
AND EXISTS (SELECT *
FROM v b
WHERE a.ID = b.ID
AND b.X = ''b'')
ID
-----------------
1
or in another case:
SELECT ID FROM v WHERE (X=''a'' OR X=''c'')
would give:
SELECT ID FROM v WHERE X = ''a''
UNION
SELECT ID FROM v WHERE X = ''b''
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Assuming the (id, x) is unique. The following gives the first result
you asked for:
SELECT id
FROM v
WHERE x IN (''A'',''B'')
GROUP BY id
HAVING COUNT(*)=2
This operation is called Relational Division and you can find other
examples and discussion about it under that name.
Your second "OR" query:
SELECT id
FROM v
WHERE x IN (''A'',''C'')
GROUP BY id
--
David Portas
SQL Server MVP
--
David Portas wrote:
SELECT id
FROM v
WHERE x IN (''A'',''B'')
GROUP BY id
HAVING COUNT(*)=2
This operation is called Relational Division and you can find other
examples and discussion about it under that name.
Your second "OR" query:
SELECT id
FROM v
WHERE x IN (''A'',''C'')
GROUP BY id
thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):
SELECT id
FROM v
WHERE x=''a'' AND EXISTS
(SELECT id FROM v WHERE x=''b'')
GROUP BY id
This can easily adopted to the ''or'' case:
SELECT id
FROM v
WHERE x=''a'' OR EXISTS
(SELECT id FROM v WHERE x=''b'')
GROUP BY id
What do you think?
bernhard
--
www.daszeichen.ch
remove nixspam to reply
这篇关于AND在单列中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
