我的数据库是垂直设计的,即同一个ID 的属性是在多行的,我想找出满足所有条件的ID
Declare @Attributes Table
(
ID VARCHAR(20),
Name VARCHAR(20),
value VARCHAR(20)
)INSERT INTO @Attributes VALUES ('1000','color','red')
INSERT INTO @Attributes VALUES ('1001','color','red')
INSERT INTO @Attributes VALUES ('1002','color','red')
INSERT INTO @Attributes VALUES ('1003','color','red')
INSERT INTO @Attributes VALUES ('1004','color','red')
INSERT INTO @Attributes VALUES ('1005','color','red')
INSERT INTO @Attributes VALUES ('1006','color','black')INSERT INTO @Attributes VALUES ('1000','price','100')
INSERT INTO @Attributes VALUES ('1001','price','1')
INSERT INTO @Attributes VALUES ('1002','price','10')
INSERT INTO @Attributes VALUES ('1003','price','50')
INSERT INTO @Attributes VALUES ('1004','price','20')
INSERT INTO @Attributes VALUES ('1005','price','1000')
INSERT INTO @Attributes VALUES ('1006','price','300')我现在的做法是对满足条件ID进行计数,当满足条件的ID次数=我要查找的条件数,则取出此IDSELECT ID
FROM
(SELECT *
FROM @Attributes t
WHERE (t.Name='color' AND t.value='red')
OR (t.Name='price' AND ISNUMERIC(t.value)=1 AND CONVERT(INT,t.value)>80)
) temp
GROUP BY ID
HAVING COUNT(ID)=2
问题:
这样的效率不高,因为表的数据超过1千万,是否有其他的SQL语句我的想法是:因为只要有1条属性不满足,则不用考虑此ID了,可能有更快的方法
FROM
(SELECT *
FROM @Attributes t
WHERE (t.Name='color' AND t.value='red')
UNION ALL
SELECT *
FROM @Attributes t
WHERE
(t.Name='price' AND ISNUMERIC(t.value)=1 AND CONVERT(INT,t.value)>80)
) temp
GROUP BY ID
HAVING COUNT(ID)=2改成UNION ALL看效率会不会好点
from @Attributes
group by ID
having
sum(case when Name='color' AND value='red' then 1
when Name='price' AND ISNUMERIC(value)=1 and cast(value as int) > 80 then 1 else 0 end)=2
---------------------
1000
1005
改成union all 连接字段加索引试试
因为属性不是只有2个,不只price 和 color , 属性个数是变化的,而且最高达140种