I'm looking to be able to see if the value for a column is the same throughout a collection of rows, based on the value of the first row in SQL.
我希望能够根据SQL中第一行的值，查看整个行集合中列的值是否相同。
Currently I'm using the following query based on the answer to this similar question.
目前我正在使用以下查询基于这个类似问题的答案。
SELECT CASE
WHEN NOT EXISTS(
SELECT * FROM dbo.Table
WHERE colA = 'valueA'
AND colD <> (
SELECT TOP 1 colD
FROM dbo.Table
WHERE LTRIM(colA) = 'valueA'
AND colB = 'valueB'
AND (
colC = 'valueC1'
OR colC = 'valueC2'
... OR colC = 'valueCn'
)
)
AND colB = 'valueB'
AND (
colC = 'valueC1'
OR colC = 'valueC2'
... OR colC = 'valueCn'
)
) THEN 'Y'
ELSE 'N'
END AS my_result
As far as I've been able to test so far, this works, but I've only used a few test cases. It's also not very scalable, which could be a problem since colC could potentially have hundreds of values.
到目前为止，我已经能够测试，但是我只使用了一些测试用例。它也不是很可扩展，这可能是一个问题，因为colC可能有数百个值。
Is there another command or method to handle this better within SQL, or perhaps would it be better to pull the inner SELECT statement into it's own query, and then take the result and add it to the outer SELECT statement in another resulting query? Or, am I going about this all wrong to begin with?
是否有另一个命令或方法可以在SQL中更好地处理这个问题，或者将内部SELECT语句拉入其自己的查询中更好，然后将结果添加到另一个结果查询中的外部SELECT语句中？或者，我是否会开始这么做错了？
This is in SQL Server 2008 R2 SP3, which I've added as a tag. I'm looking for a statement that will preferably return some sort of boolean value that I can evaluate on, similar to how this statement currently returns either Y or N. However, if there's a better solution that requires a different output, I can most likely adjust to deal with that. I'm more concerned in having a good, scalable solution.
这是在SQL Server 2008 R2 SP3中，我已将其添加为标记。我正在寻找一个声明，它最好返回一些我可以评估的布尔值，类似于这个语句当前返回Y或N.但是，如果有一个更好的解决方案需要不同的输出，我可以可能会调整以解决这个问题。我更关注拥有一个好的，可扩展的解决方案。
Used_By_Already has a good start, however I'm not going to know what the data is ahead of time, so I need a purely dynamic solution.
Used_By_Already有一个良好的开端，但我不会提前知道数据是什么，所以我需要一个纯动态的解决方案。
The planned use case for this is to feed in a single value for colA and colB, and n values for colC, the structure always being colA AND colB AND (colC1 OR colC2 OR...colCn). These values are being used to retrieve the first datetime from colD that is then checked against every value in colD, again filtering by the criteria in the first query. I won't know the value of the datetime in colD until the first query is done.
计划的用例是为colA和colB提供单个值，为colC提供n个值，结构始终为colA AND colB AND（colC1 OR colC2 OR ... colCn）。这些值用于从colD检索第一个日期时间，然后根据colD中的每个值进行检查，再次按第一个查询中的条件进行筛选。在完成第一个查询之前，我不会知道colD中datetime的值。
The only information I have ahead of time before going into this query, is colA, colB, and each value of colC. colC will always have at least one value, but could potentially have hundreds of values. I'm needing to check if every instance filtered by my WHERE statement was recorded in the system at the same datetime object, or if they were different. I don't know what that datetime is, and in all honesty, it's irrelevant when it happened, so long as each time it happened is the same.
在进入此查询之前，我提前获得的唯一信息是colA，colB和colC的每个值。 colC将始终至少有一个值，但可能有数百个值。我需要检查我的WHERE语句过滤的每个实例是否都记录在同一日期时间对象的系统中，或者它们是否不同。我不知道那个约会时间是什么，而且说实话，它发生时无关紧要，只要每次发生都是一样的。
SQL小提琴
CREATE TABLE Example
([colA] nvarchar(20), [colB] nchar(1), [colC] smallint, [colD] datetime)
;
INSERT INTO Example
([colA], [colB], [colC], [colD])
VALUES
('123610', 'S', '1', '2017-06-17 11:53:52'),
('123610', 'S', '2', '2017-06-17 11:53:52'),
('123610', 'R', '3', '2017-06-17 11:53:52'),
('123610', 'S', '4', '2017-06-17 11:53:52'),
('123611', 'S', '1', '2017-06-17 11:53:52'),
('123610', 'S', '5', '2017-06-14 11:53:52'),
('123610', 'S', '3', '2017-06-17 11:53:52'),
('123610', 'S', '7', '2017-06-15 11:53:52'),
('123610', 'S', '8', '2017-06-17 11:53:52'),
('123610', 'S', '9', '2017-06-17 11:53:52')
;
Query 1
查询1
SELECT CASE
WHEN NOT EXISTS(
SELECT * FROM Example
WHERE colA = '123610'
AND colD <> (
SELECT TOP 1 colD
FROM Example
WHERE colA = '123610'
AND colB = 'S'
AND (
colC = '1'
OR colC = '2'
OR colC = '7'
OR colC = '5'
)
)
AND colB = 'S'
AND (
colC = '1'
OR colC = '2'
OR colC = '7'
OR colC = '5'
)
) THEN 'Y'
ELSE 'N'
END AS my_result
结果
|my_result|
|---------|
| N |
Query 2
查询2
SELECT CASE
WHEN NOT EXISTS(
SELECT * FROM Example
WHERE colA = '123610'
AND colD <> (
SELECT TOP 1 colD
FROM Example
WHERE colA = '123610'
AND colB = 'S'
AND (
colC = '1'
OR colC = '2'
OR colC = '3'
OR colC = '8'
)
)
AND colB = 'S'
AND (
colC = '1'
OR colC = '2'
OR colC = '3'
OR colC = '8'
)
) THEN 'Y'
ELSE 'N'
END AS my_result
结果
|my_result|
|---------|
| Y |
Query 3
查询3
SELECT CASE
WHEN NOT EXISTS(
SELECT * FROM Example
WHERE colA = '123610'
AND colD <> (
SELECT TOP 1 colD
FROM Example
WHERE colA = '123610'
AND colB = 'S'
AND (
colC = '1'
OR colC = '4'
OR colC = '3'
OR colC = '8'
)
)
AND colB = 'S'
AND (
colC = '1'
OR colC = '4'
OR colC = '3'
OR colC = '8'
)
) THEN 'Y'
ELSE 'N'
END AS my_result
结果
|my_result|
|---------|
| N |
1 个解决方案
#1
0
Borrowing sample data from that similar question I setup the following:
借用类似问题的样本数据，我设置了以下内容：
SQL小提琴
CREATE TABLE Table1
([ID] varchar(4), [Status] int)
;
INSERT INTO Table1
([ID], [Status])
VALUES
('y123', 2),
('y432', 2),
('y531', 2),
('y123', 2),
('n123', 1),
('n432', 3),
('n531', 2),
('n123', 2)
;
Query 1:
查询1：
select
coalesce(
(select 'Y' as yn
from (
select count(distinct [Status]) yn
from table1
where id like 'y%' -- data for yes
having count(distinct [Status]) = 1
) as yn)
,'N')
结果：
| |
|---|
| Y |
Query 2:
查询2：
select
coalesce(
(select 'Y' as yn
from (
select count(distinct [Status]) yn
from table1
where id like 'n%' -- data for no
having count(distinct [Status]) = 1
) as yn)
,'N')
结果：
| |
|---|
| N |
#1
0
Borrowing sample data from that similar question I setup the following:
借用类似问题的样本数据，我设置了以下内容：
SQL小提琴
CREATE TABLE Table1
([ID] varchar(4), [Status] int)
;
INSERT INTO Table1
([ID], [Status])
VALUES
('y123', 2),
('y432', 2),
('y531', 2),
('y123', 2),
('n123', 1),
('n432', 3),
('n531', 2),
('n123', 2)
;
Query 1:
查询1：
select
coalesce(
(select 'Y' as yn
from (
select count(distinct [Status]) yn
from table1
where id like 'y%' -- data for yes
having count(distinct [Status]) = 1
) as yn)
,'N')
结果：
| |
|---|
| Y |
Query 2:
查询2：
select
coalesce(
(select 'Y' as yn
from (
select count(distinct [Status]) yn
from table1
where id like 'n%' -- data for no
having count(distinct [Status]) = 1
) as yn)
,'N')
结果：
| |
|---|
| N |