现有表:a
数据:
商品id 商品属性id 字符值 数字值
sid cid stringvalue intvalue
1000 1000 3
1000 1001 0,2,5,12, 0
1000 1002 31003 1000 2
1003 1001 0,1,4,5,7,13, 0
1003 1002 2
现要查询满足条件的sid,查询所有商品中属性1000(cid)值<=3,并且属性1001(cid)值为like '%,5,%'的记录,然后根据sid distinct去重。
按照上面逻辑描述写(cid=1000 and intvalue<=3) and (cid=1001 and stringvalue like '%,5,%'),这样只返回一条记录
逻辑有点复杂,我是已经绕不出来了,麻烦各位高手。
数据:
商品id 商品属性id 字符值 数字值
sid cid stringvalue intvalue
1000 1000 3
1000 1001 0,2,5,12, 0
1000 1002 31003 1000 2
1003 1001 0,1,4,5,7,13, 0
1003 1002 2
现要查询满足条件的sid,查询所有商品中属性1000(cid)值<=3,并且属性1001(cid)值为like '%,5,%'的记录,然后根据sid distinct去重。
按照上面逻辑描述写(cid=1000 and intvalue<=3) and (cid=1001 and stringvalue like '%,5,%'),这样只返回一条记录
逻辑有点复杂,我是已经绕不出来了,麻烦各位高手。
Select Distinct [sid] From 表a Where()
select distinct t1.sid from a t1
where exists (select 1 from a t2 where t2.sid=t1.sid and t2.cid=1000 and t2.intvalue<=3)
and exists (select 1 from a t3 where t3.sid=t1.sid and t3.cid=1001 and t3.stringvalue like '%,5,%')
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
DROP TABLE A
END
GO
CREATE TABLE A
(
sid INT,
cid INT,
stringvalue VARCHAR(100),
intvalue INT
)
INSERT INTO A
SELECT 1000, 1000, '',3 UNION
SELECT 1000, 1001, '0,2,5,12,', 0 UNION
SELECT 1000, 1002, '',3 UNION
SELECT 1001, 1000, '',4 UNION
SELECT 1001, 1001, '0,2,5,12,', 0 UNION
SELECT 1001, 1002, '',3 UNION
SELECT 1003, 1000, '',2 UNION
SELECT 1003, 1001, '0,1,4,5,7,13,', 0 UNION
SELECT 1003, 1002, '',2SELECT sid FROM A
WHERE cid=1000 and intvalue<=3
INTERSECT
SELECT Sid FROM A
WHERE cid=1001 and stringvalue like '%,5,%'sid
1000
1003你的问题写在一个where里是不会有结果的,因为同一个cid不可能又等于1000又等于1001.
上面说是就是goods_expandvalue表,我需要查询扩展属性属合条件的商品Id。商表扩展属性值表中sid代表商品id,cid代表扩展属性表中的扩展属性id
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([sid] int,[cid] int,[stringvalue] varchar(13),[intvalue] int)
insert [test]
select 1000,1000,'3',null union all
select 1000,1001,'0,2,5,12,',0 union all
select 1000,1002,'3',null union all
select 1003,1000,'2',null union all
select 1003,1001,'0,1,4,5,7,13,',0 union all
select 1003,1002,'2',null
--你的只需把 and 改成or即可
select * from test
where (cid=1000 and intvalue<=3) or
(cid=1001 and stringvalue like '%,5,%')
/*
sid cid stringvalue intvalue
1000 1001 0,2,5,12, 0
1003 1001 0,1,4,5,7,13, 0
*/