SELECT with computed correlation dependent column

Question

I do not deal with a lot of SQL, and most of the time I do CRUD operations. Sometimes I complicate things. So this question may be a beginners question, but I'm ready. I was just trying to figure it out for hours, and it is useless.

So imagine the following table structure:

> | ID | Col1 | Col2 | Col3 | .. | Col8 |

I want to select an identifier and a computed column. The calculated column has a range from 0 to 8 and contains the number of matches with the query. I also want to limit the result set to only including rows that have a certain number of matches.

So from this sample data:

> | 1 | 'a' | 'b' | 1 | 2 |  
> | 2 | 'b' | 'c' | 1 | 2 |  
> | 3 | 'b' | 'c' | 4 | 5 |  
> | 4 | 'x' | 'x' | 9 | 9 |  

I want to query on Col1 = 'a' OR Col2 = 'c' OR Col3 = 1 OR Col4 = 5, where the calculated result> 1 and the result set look like this:

> | ID | Cal |
> | 1  |  2  |
> | 2  |  2  |
> | 3  |  2  |

T-SQL SQL Server 2005, , DB.

.

Answer 1

SQL 2005, CTE .

WITH Matches AS
(
    SELECT ID, CASE WHEN Col1 = 'a' THEN 1 ELSE 0 END + 
                CASE WHEN Col2 = 'c' THEN 1 ELSE 0 END +
                CASE WHEN Col3 = 1  THEN 1 ELSE 0 END +
                CASE WHEN Col4 = 5  THEN 1 ELSE 0 END AS Result
    FROM Table1
    WHERE Col1 = 'a' OR Col2 = 'c' OR Col3 = 1 OR Col4 = 5 
)
SELECT ID, Result
FROM Matches
WHERE Result > 1 

Answer 2

, , 1 0:

SELECT * FROM (
  SELECT ID, (Col1='a') + (Col2='c') + (Col3=1) + (Col4=5) AS calculated
  FROM MyTable
) q
WHERE calculated > 1; 

, , + , =. , , WHERE .

, WHERE , , , , , , , . , , , .

---

Re Quassnoi comment, , , . :

SELECT * FROM (
  SELECT ID, 
      CASE WHEN Col1='a' THEN 1 ELSE 0 END
    + CASE WHEN Col2='c' THEN 1 ELSE 0 END 
    + CASE WHEN Col3=1   THEN 1 ELSE 0 END
    + CASE WHEN Col4=5   THEN 1 ELSE 0 END AS calculated
  FROM MyTable
) q
WHERE calculated > 1;

Answer 3

This query is more convenient for indexing:

SELECT  id, SUM(match)
FROM    (
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col1 = 'a'
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col2 = 'c'
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col3 = 1
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col4 = 5
        ) q
GROUP BY
        id
HAVING  SUM(match) > 1

This will be effective only if all the columns you are looking for are indexed first and secondly have high power (many different values).

See this blog post for performance details:

• 3 out of 4 matching