sql语句 表A 有三个字段 id,type,value
取出type为101,102的记录，
但是如果type为102的话需要考虑id对应的103属性值是否满足3301开头
请问sql怎么写？在线等，挺急的！！谢谢
SELECT T1.ID, T1.TYPE, T1.VALUE
FROM A T1
WHERE T1.TYPE = '101'
UNION ALL
SELECT T2.ID, T2.TYPE, T2.VALUE
FROM A T2
WHERE T2.ID IN (SELECT T3.ID
FROM A T3
WHERE T3.TYPE = '102'
AND T3.VALUE NOT LIKE '3301%');
没听明白你到底是啥意思呀 101 102 当type=102 的时候 又要考虑 id对应的103属性值是否满足3301开头 103属性值是个什么东西
A
id type value
1 101 123456
2 102 456878
2 103 344010201001
3 102 56789
3 103 330101011
取出的数据
101 123456
102 56789
SELECT * FROM A WHERE EXISTS (SELECT * FROM A WHERE A.type='102' AND A.id like '3301%' OR A.type='101')
select id，type，value from table where id in （select id from table where type in （101,102） intersect select id from table where type = 103 and value like "3301%"） 这个应该可以满足你的要求 没有做优化 需要的话 可以自己尝试下 后面的 intersect 是 求交集
SELECT t.id,
t.type,
t.value
FROM a t
WHERE t.type = '101'
OR (t.type = '102' AND T.ID IN (
SELECT T1.ID FROM A T1 WHERE T1.ID=T.ID AND T1.VALUE LIKE '3301%'
));
SELECT t.id,
t.type,
t.value
FROM a t
WHERE t.type = '101'
OR (t.type = '102' AND T.ID IN (
SELECT T1.ID FROM A T1 WHERE T1.ID=T.ID AND T1.VALUE LIKE '3301%'
**and T1.type = '103' **
));
select * from A
where type='101'
or (type='102' and id in(select id from(select * from A where type='103' and id in (select id from A where type='102') and value like '3301%')))
这样？
select t1.id,t1.type,t1.value
from a t1
where t1.type =101 or (t1.type=102 and t1.id in
(select t2.id from a t2 where t1.id = t2.id and t2.value like '3301%' GROUP BY t2.id)
)
SELECT *
FROM A a
WHERE a.type IN (101,
102)
AND a.type = (SELECT a1.type
FROM A a1, A a2
WHERE a1.type = 103
AND a1.value LIKE '3301%'
AND a1.id = a2.id
AND a2.type = 102)
错了，应该是这样的。。
SELECT *
FROM a a
WHERE a.type IN (101,
102)
UNION
SELECT *
FROM a a1
WHERE a1.id = (SELECT a2.id
FROM a a2,
a a3
WHERE a2.type = 103
AND a2.value LIKE '3301%'
AND a2.id = a3.id
AND a3.type = 102)
AND a1.type = 103
“但是如果type为102的话需要考虑id对应的103属性值是否满足3301开头“
请问你这需求描述什么意思？ type = 102 ，id= 103 value以3301开头?
还是你原需求要描述的就时101和102的记录，type=103时value以3301开头的？
给你个sql，记录101，102的，type103时value以3301开头的
select id, type, value
from tbl_name
where type in ('101', '102')
or (type = '103' and id like '3301%')