代码之家 › 专栏 › 技术社区 › Bodhi

如何查询与特定字符串匹配的行?

exists subquery tsql sql-server sql

0

Bodhi · 技术社区 · 7 年前

现在,我要筛选那些徽章以6542和3214开头的员工,也就是同时携带6542和3214开头徽章的员工

谢谢你

更新1

有些记录从6542或3214开始只有一个徽章。但我只想要同时带着两个徽章的员工。

5 回复 | 直到 7 年前

1

jarlh 7 年前

做一个 GROUP BY ,使用 HAVING

select empid, name
from Table
where badge like '6542-%' or badge like '3214-%'
group by empid, name
having count(distinct badge) > 1

或使用 INTERSECT :

select empid, name from Table where badge like '6542-%'
intersect
select empid, name from Table where badge like '3214-%'

2

1

picklerick 7 年前

就像在徽章上一样

    Select empid, name 
    from TableName 
    where badge like '6542%' or badge like '3214%'
    group by empid, name
    having count(*)>1

3

0

Nikhil 7 年前

这将起作用:

select empid from table_name where regexp_like(Badge,'^(6542)(.*)$')
intersect
select empid from table_name where regexp_like(Badge,'^(3214)(.*)$');

sql server等效项:

select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge)  !=0
intersect
select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge)  !=0

4

0

Shushil Bohara 7 年前

我们可以利用 COUNT DISTINCT 具有 CASE HAVING 如下所示

DECLARE @test AS TABLE(EMPID INT, Badge VARCHAR(50), idNumber INT, EName VARCHAR(50))
INSERT INTO @test VALUES
(1148, '6542-74488', 66448, 'Adam Jhon'),
(1148, '642-8562', 66448, 'Adam Jhon'),
(1148, '3214-52874', 66448, 'Adam Jhon'),

(1149, '3214-45220', 209541, 'Tom Koyaski'),
(1150, '3214-23134', 63339, 'Shirin Abdulla'),
(1151, '3214-42355', 65498, 'Linda Jhon'),

(1151, '6542-2546', 65498, 'Linda Jhon'),
(1152, '3214-47632', 208673, 'Gayeth'),
(1153, '6542-73085', 83209, 'Maria Smith'),

(1153, '3214-58073', 65498, 'Maria Smith'),
(1154, '3214-26735', 208673, 'Ayan Jacob'),
(1155, '642-26739', 53959, 'Wo Li')

SELECT empid, Ename 
FROM @test 
WHERE badge LIKE '6542%' OR badge LIKE '3214%'
GROUP BY empid, Ename
HAVING COUNT (DISTINCT(CASE WHEN badge like '6542%' THEN 1 
                WHEN badge LIKE '3214%' THEN 2 END))>1

输出 :

empid   Ename
1148    Adam Jhon
1151    Linda Jhon
1153    Maria Smith

5

0

Ravi Bharati Mathapati 7 年前

就这么做

    Select a.empid, a.name 
    from TableName as a
    inner join TableName as b on a.Empid = b.Empid and a.idnumber = b.idnumber and b.badge like '3214%'
    where a.badge like '6542%'