选择在其中一列中具有指定值的行 [英] SELECT rows that have specified values in one of the columns

问题描述

我的 SQL Server 2012 上有下表:

ID Name Status Address Phone
1  Tom    I       U      D
2  Joe    D       U      D
3  Pam    D       I      U
4  Ken    U       U      U

如何在其中一列中选择带有I"的行?例如，我希望查询从表中返回第 1 行和第 3 行.

How do I select the rows with 'I' in one of the columns? for example, I expect the query to return 1st and 3rd row from the table.

我知道下面的查询有效，但是我需要一个不指定列名的查询，因为我需要处理一个超过 20 列的表.

I know the query below works however I need a query that does not specify the column names as I need to deal with a table with more than 20 columns.

SELECT * FROM table WHERE (Status = 'I' or Address = 'I' or Phone = 'I')

推荐答案

一种方法是使用 XML:

One way is to use XML:

SELECT t.*
FROM tab t
CROSS APPLY (SELECT * FROM tab t2 WHERE t.id = t2.id FOR XML RAW('a')) sub(c)
WHERE sub.c LIKE '%"I"%';

输出:

┌────┬──────┬────────┬─────────┬───────┐
│ ID │ Name │ Status │ Address │ Phone │
├────┼──────┼────────┼─────────┼───────┤
│  1 │ Tom  │ I      │ U       │ D     │
│  3 │ Pam  │ D      │ I       │ U     │
└────┴──────┴────────┴─────────┴───────┘

DBFiddle 演示

排除某些列的更高级选项.基本上模拟 SELECT * EXCEPT id, name:

A bit more advanced option that excludes some columns. Basically simulating SELECT * EXCEPT id, name:

SELECT DISTINCT t.*
FROM tab t
CROSS APPLY (VALUES(CAST((SELECT t.* for XML RAW) AS xml))) B(XMLData)
CROSS APPLY (SELECT 1 c
             FROM B.XMLData.nodes('/row')  AS C1(n)
             CROSS APPLY C1.n.nodes('./@*') AS C2(a)
             WHERE a.value('local-name(.)','varchar(100)') NOT IN ('id','name')
               AND a.value('.','varchar(max)')  = 'I') C;

DBFiddle Demo2

这篇关于选择在其中一列中具有指定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！