摘要:昨天有同事问我一个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 11 Attr2 22 Attr2 23 Attr1 14 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 =......[
阅读全文]