当前位置: 代码迷 >> Oracle技术 >> 求来看看这道SQL题。完全没思路
  详细解决方案

求来看看这道SQL题。完全没思路

热度:49   发布时间:2016-04-24 08:28:03.0
求高手进来看看这道SQL题。完全没思路
一个表 [tbuser]
ID NAME INFO
1 aa11 t1
2 aa22 t1
3 bb33 t2
4 bb44 t2
5 cc55 t3
6 cc66 t3
7 cs2 b
8 ct5 b
9 xw6 b
10 12sre b

问题:
找出所有以aa bb cc 开头的信息
例如
ID NAME INFO
1 aa11 t1
2 aa22 t1
3 bb33 t2
4 bb44 t2
......

请教各位大神如何写SQL代码。
目前只能找出单个字段的数据,例如以aa开头的代码
SQL code
select t.* from tbuser t where substr(t.name,0,3) like 'aa%';




------解决方案--------------------
SQL code
WITH t AS (SELECT 1 ID,'aa11' NAME,'t1' INFO FROM dualUNION ALL SELECT 2,'aa22', 't1' FROM dualUNION ALLSELECT 3, 'bb33', 't2' FROM dualUNION ALLSELECT 4, 'bb44', 't2' FROM dualUNION ALLSELECT 5, 'cc55', 't3'  FROM dualUNION ALLSELECT 6, 'cc66', 't3'  FROM dualUNION ALLSELECT 7, 'cs2', 'b'  FROM dualUNION ALLSELECT 8, 'ct5', 'b'  FROM dualUNION ALLSELECT 9, 'xw6', 'b'  FROM dualUNION ALLSELECT 10, '12sre', 'b'  FROM dual)SELECT * FROM t WHERE NAME LIKE 'aa%' OR NAME LIKE 'bb%' OR NAME LIKE 'cc%' ORDER BY id;ID    NAME    INFO1    aa11    t12    aa22    t13    bb33    t24    bb44    t25    cc55    t36    cc66    t3
------解决方案--------------------
SQL code
select t.* from tbuser t where upper(substr(t.name,0,1))=upper(substr(t.name,1,2));
  相关解决方案