SQL Server SQL Query with grouping Issue

toiithl6 于 2023-04-10 发布在其他

关注(0)|答案(2)|浏览(113)

I have a SQL query that pull data from the SQL Server. I need to group a field (Alpha field) and if there are more than one record, display 'NO MATCH'. This query will help users spot jobs with a missing Alpha value.

Here is the query to get the data with the grouping:

select name,Alpha from T
where name = 'John'
GROUP BY name, Alpha

Here are the results from the query:

name    Alpha
John    4
John    7
John    20

I am trying to get the result to say 'NO MATCH' if the data doesn't match. The example above have three records that do not match. How do I get the result to be 'No match'? If all records are a match display the alpha value.

Here is the link to the SQL Fiddle I created with the example:

http://sqlfiddle.com/#!18/ccd77/1/0

sql-server

来源：https://stackoverflow.com/questions/75934737/sql-query-with-grouping-issue

2条答案

按热度按时间

ssgvzors1#

It's possibly more performant to use MIN = MAX instead of COUNT(DISTINCT .

This has the same result in this case, but does not require a sort on Alpha .

SELECT
  name,
  IIF(MIN(Alpha) = MAX(Alpha), CAST(MAX(Alpha) AS VARCHAR(12)), 'No Matched') AS Alpha
FROM T
WHERE name = 'John'
GROUP BY
  name;

赞(0）回复(0）举报 2023-04-10

gkl3eglg2#

I have added two more records having same Alpha filed:

INSERT INTO t VALUES (26, 'Andy',  15) ;
INSERT INTO t VALUES (27, 'Andy',  15) ;

You can use COUNT with DISTINCT to check if all values are the same:

select name
      ,IIF(COUNT(DISTINCT Alpha) = 1, CAST(MAX(Alpha) AS VARCHAR(12)), 'No Matched') AS Alpha
from T
--where name = 'John'
GROUP BY name

gives:

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server SQL Query with grouping Issue

2条答案

相关问题

热门标签

最新问答