昨天有同事问我一个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')
这样看起来是不是更易读呢?而且增加条件也很简单。