数据库sql查询？

Question

sql语句 表A 有三个字段 id,type,value
取出type为101,102的记录，
但是如果type为102的话需要考虑id对应的103属性值是否满足3301开头
请问代码怎么写？在线等，挺急的！！谢谢

Answer 1

使用子查询即可，根据你提供的查询示例，SQL 语句如下：

SELECT * FROM demo 
  WHERE `type`=101 OR 
        (`type`=102 AND `id` = (
                        SELECT `id` FROM demo  WHERE `type`=103 
                                               AND `id` IN 
                                                        (SELECT dt.id FROM (SELECT * FROM demo WHERE `type`=102) AS dt)
                                               AND `value` like'3301%'
));

查询效果如下：

Answer 2

where `type`=101 or (`type`=102 and `value` like'3301%')
是这样吗？没太读懂问题。

Answer 3

试试这个，思路:

• 将101和102作为一个表a
• 将103不满足开头'3301'的记录作为一个表b

select t1.type,t1.value from t as t1 left join t as t2 on (t2.id = t1.id AND t2.type = 103 AND t2.value like '3301%')
where t1.type = 101 OR  ( t1.type = 102 AND t2.id  is not NULL )

Answer 4

子查询

SELECT a.Id, a.Type, a.Value
FROM T_A a
WHERE a.Type = 101 OR
    (a.Type = 102 AND a.Id IN
        (SELECT b.Id
        FROM T_A b
            INNER JOIN T_A c ON b.Id = c.Id AND c.Value LIKE '3301%' AND c.Type = 103
        WHERE b.Type = 102)
        )