昨天有同事问我一个SQL语句，估计在不少地方会用到，所以在这里分享一下。
有一个对象，每个对象有0到N个属性，根据数据库设计的3NF，我们会设计一个这样的表
CREATE TABLE [dbo].[Objects](
[ObjectID] [int] NOT NULL PRIMARY KEY,
[ObjectNane] [varchar](100) NOT NULL
)
CREATE TABLE [dbo].[ObjectAttributes](
[ObjectID] [int] NOT NULL,
[AttrName] [varchar](100) NOT NULL,
[AttrValue] [varchar](500) NULL
)
ALTER TABLE ObjectAttributes ADD CONSTRAINT PK_ObjectAttributes PRIMARY KEY CLUSTERED
(
ObjectId,
AttrName
)
那么，我们希望找出Attr1 (AttrName) = "1" (AttrValue) 并且Attr2 (AttrName) = "2" (AttrValue)的记录。
比如，ObjectAttributes的内容为
1 Attr1 1
1 Attr2 2
2 Attr2 2
3 Attr1 1
4 Attr3 1
那么我们希望的结果是[dbo].[Objects]表中ObjectID为1的记录。
你会怎样写这个SQL语句呢？
我的同事想了一个办法，对ObjectAttributes 表查两次，然后Union All，Group By之后求Count。
完整的SQL语句如下：
SELECT o.*
FROM Objects AS o INNER JOIN
(SELECT ObjectId
FROM (SELECT ObjectId
FROM ObjectAttributes
WHERE (AttrName = 'Attr1') AND (AttrValue = '1')
UNION ALL
SELECT ObjectId
FROM ObjectAttributes
WHERE (AttrName = 'Attr2') AND (AttrValue = '2')) AS M1
GROUP BY ObjectId
HAVING (COUNT(*) = 2)) AS M2
ON o.ObjectID = M2.ObjectId
OK, 虽然看起来怪麻烦的，but it works.能管用就好，效率其实也不算差。就是如果判断条件再复杂一点，而且有交集并集的时候就不好办了。
其实，有一个更自然的写法可以解决这个问题。
SELECT *
FROM Objects AS o
WHERE EXISTS
(SELECT * FROM ObjectAttributes
WHERE ObjectId = o.ObjectID AND AttrName = 'Attr1' AND AttrValue = '1')
AND EXISTS
(SELECT * FROM ObjectAttributes
WHERE ObjectId = o.ObjectID AND AttrName = 'Attr2' AND AttrValue = '2')
这样看起来是不是更易读呢？而且增加条件也很简单。
希望以上内容对你有帮助，方便的话，请帮我投2票，谢谢！投票需要有Myspace帐号，不过注册很容易的哦。
打印 | 张贴于 2007-12-07 13:22:00 | Tag:暂无标签
留言反馈
①VS2005能否编写非托管C++
②我想要一个VC++6.0，只是一个VC++6.0不要VS6.0
哪位可以帮忙，谢谢指点。
我会经常来看的，写在回复上就可以了
(
SELECT objectid FROM ObjectAttributes a WHERE
(a.AttrName='attr1' AND a.AttrValue=1) or
(a.AttrName='attr2' AND a.AttrValue=2) GROUP BY objectid HAVING(COUNT(1)=2)
)t, Objects b WHERE t.objectid=b.objectid
http://video-salope-.com.persona-fou.info
http://fioricet-for-sale.lilli-jav.info
http://sexe-video-telecharger.preteen-fou.info
索引建到
objectid+attrname
俺没缴费:(
To: Lostinet
你的方案也不错，其实三种方法执行效率没啥差别。只是我贴的那种易读性高，而且便于处理复杂的逻辑。
比如Color = Red And (Size = Big Or Price < 100)之类的。
P.S. ObjectID+AttrName是Primary Key，你没有注意到么？
SELECT * FROM OBJECTS
WHERE ObjectID IN (
SELECT ObjectID FROM ObjectAttributes
WHERE (AttrName = 'Attr1' AND AttrValue = '1')
OR (AttrName = 'Attr2' AND AttrValue = '2')
GROUP BY ObjectID
HAVING COUNT(*)=2
)
ObjectID+AttrName应高保证是Unique的吧.
如果不是,
那么用
COUNT(DISTINCT AttrName)=2