SQL Server - 如果只有一个不同的值 + 空值而没有 ansi 警告，则聚合 [英] SQL Server - aggregate if only one distinct value + nulls without ansi warnings
问题描述
假设我有这样的数据
first_name last_name 城市约翰·邦乔维 null约翰列侬 null约翰鹿空我想创建聚合查询，它将返回如下所示的 json
{ "first_name": "John", "city": null }本质上，查询应该检查每一列中是否只有一个不同的值，如果是，则将此值放入 json.使用这样的查询可以相对容易地获取所有非空列:
选择当 count(distinct first_name) = 1 然后 max(first_name) 以 first_name 结尾的情况下，当 count(distinct last_name) = 1 然后 max(last_name) 以 last_name 结束的情况下，当 count(distinct city) = 1 然后 max(city) 以 city 结尾的情况从 ...对于 json 路径， without_array_wrapper或
选择如果 max(first_name) = min(first_name) 然后 max(first_name) 以 first_name 结尾，如果 max(last_name) = min(last_name) 然后 max(last_name) 以 last_name 结尾，当 max(city) = min(city) 然后 max(city) 以 city 结尾的情况从 ...对于 json 路径， without_array_wrapper上面查询的结果是这样的 json {"first_name":"John"}.但是随后存在空值问题.问题(1) - 上面的查询不考虑空值，所以如果我有这样的数据
first_name last_name 城市----------------------------------约翰列侬 null约翰列侬 null约翰空空然后姓氏也包含在生成的json中
{ "first_name": "John", "last_name": "Lennon" }好的，这是可以理解的(因为 ...Null 值被聚合消除了...)，我可以用这样的查询来解决它:
选择当 count(distinct first_name) = 1 和 count(first_name) = count(*) 然后 max(first_name) 以 first_name 结尾的情况下，当 count(distinct last_name) = 1 和 count(last_name) = count(*) 然后 max(last_name) 以 last_name 结束的情况下，当 count(distinct city) = 1 和 count(city) = count(*) 然后 max(city) 以 city 结尾的情况从 ...对于 json 路径， without_array_wrapper但是空值还有其他问题，我现在无法真正巧妙地解决.问题 (2) - 我想在我的 json 中也有 "city":null .我当然可以做这样的事情
<代码>...当 count(city) = 0 然后 'null' 以 city 结尾的情况...
然后用真正的空值替换字符串 null ，但它不是很整洁.另一个烦人的事情是 (3) - 我真的很想摆脱警告
警告:空值会被聚合或其他 SET 操作消除.
无需关闭ANSI_WARNINGS.现在我只能考虑在 isnull 中使用一些占位符，这看起来不是一个干净的解决方案
那么，关于如何优雅地解决问题 (2) 和 (3) 的任何想法?请参阅 db<>fiddle 中的示例.
好的，所以到目前为止没有人发布任何答案，我已经想到了一种方法.它并不完美，但似乎有效.所以我们的想法是在 select 中使用 @var = @var + 1 技巧.但它应该更复杂一点:
声明@first_name varchar(4), @first_name_state tinyint = 0,@last_name varchar(4), @last_name_state tinyint = 0,@city varchar(4), @city_state tinyint = 0,@country varchar(10), @country_state tinyint = 0,@result nvarchar(max) = '{}';选择@first_name_state =案件当@first_name_state = 0 然后 1当@first_name_state = 1 且@first_name = t.first_name 时，则为 1当@first_name_state = 1 且@first_name 为空且t.first_name 为空时，则为1其他 2结尾，@first_name = t.first_name，@last_name_state =案件当@last_name_state = 0 然后是 1当@last_name_state = 1 且@last_name = t.last_name 时，则为 1当@last_name_state = 1 且@last_name 为空且t.last_name 为空时，则为1其他 2结尾，@last_name = t.last_name，@city_state =案件当@city_state = 0 然后 1当@city_state = 1 且@city = t.city 时，则为 1当@city_state = 1 且@city 为空且t.city 为空时，则为1其他 2结尾，@city = t.city，@country_state =案件当@country_state = 0 然后 1当@country_state = 1 且@country = t.country 时，则为 1当@country_state = 1 且@country 为空且t.country 为空时，则为1其他 2结尾，@country = t.country从表1作为t;如果@first_name_state = 1set @result = json_modify(json_modify(@result,'$.first_name','null'),'strict $.first_name',@first_name);如果@last_name_state = 1set @result = json_modify(json_modify(@result,'$.last_name','null'),'strict $.last_name',@last_name);如果@city_state = 1set @result = json_modify(json_modify(@result,'$.city','null'),'strict $.city',@city);如果@country_state = 1set @result = json_modify(json_modify(@result,'$.country','null'),'strict $.country',@country);选择@result;----------------------------------{"first_name":"John","city":null}参见db<>摆弄示例.
请注意，根据 Microsoft docs 你不应该使用这个变量聚合赋值技巧，因为一些语句可以被调用多次.
<块引用>不要在 SELECT 语句中使用变量来连接值(即是，计算聚合值).可能会出现意外的查询结果.因为，SELECT 列表中的所有表达式(包括赋值)不一定对每个输出行只运行一次.
我希望在这种情况下它应该可以正常工作，因为它不完全是一个聚合，并且如果这些语句每行被调用多次也没关系.不过，您可以在这个答案中找到一些有用的链接.
Suppose I have a data like this
first_name last_name city
John Bon Jovi null
John Lennon null
John Deer null
And I want to create aggregating query which will return json which looks like this
{ "first_name": "John", "city": null }
Essentially, the query should check if there's only one distinct value within each column and if it is, put this value to json. All non-null columns are relatively easy to get with a query like this:
select
case when count(distinct first_name) = 1 then max(first_name) end as first_name,
case when count(distinct last_name) = 1 then max(last_name) end as last_name,
case when count(distinct city) = 1 then max(city) end as city
from ...
for json path, without_array_wrapper
or
select
case when max(first_name) = min(first_name) then max(first_name) end as first_name,
case when max(last_name) = min(last_name) then max(last_name) end as last_name,
case when max(city) = min(city) then max(city) end as city
from ...
for json path, without_array_wrapper
The result of the queries above is json like this {"first_name":"John"}. But then there are problems with nulls. Problem (1) - queries above do not take nulls into account, so if I have data like this
first_name last_name city
----------------------------------
John Lennon null
John Lennon null
John null null
Then last name is also included in the resulting json
{ "first_name": "John", "last_name": "Lennon" }
Ok, that's understandable (cause ...Null value is eliminated by an aggregate...) and I can solve it with a query like this:
select
case when count(distinct first_name) = 1 and count(first_name) = count(*) then max(first_name) end as first_name,
case when count(distinct last_name) = 1 and count(last_name) = count(*) then max(last_name) end as last_name,
case when count(distinct city) = 1 and count(city) = count(*) then max(city) end as city
from ...
for json path, without_array_wrapper
But there are other problems with nulls I can't really solve neatly for now. Problem (2) - I want to have also "city":null in my json. Of course I can do something like this
...
case when count(city) = 0 then 'null' end as city
...
and then replace string null with real nulls, but it's not very neat. Another annoying thing is (3) - I'd really like to get rid of warnings
Warning: Null value is eliminated by an aggregate or other SET operation.
without turning ANSI_WARNINGS off. For now I can only think about using some placeholders with isnull which doesn't look like a clean solution
...
case when count(distinct isnull(city, 'null')) = 1 then max(city) end as city
...
So, any ideas on how to elegantly solve problems (2) and (3)? see examples in db<>fiddle.
Ok, so nobody posted any answers so far, I have thought of one way doing it. It's not perfect, but it seems to work.
So the idea is to use @var = @var + 1 trick inside of select. But it should be a bit more complicated:
declare
@first_name varchar(4), @first_name_state tinyint = 0,
@last_name varchar(4), @last_name_state tinyint = 0,
@city varchar(4), @city_state tinyint = 0,
@country varchar(10), @country_state tinyint = 0,
@result nvarchar(max) = '{}';
select
@first_name_state =
case
when @first_name_state = 0 then 1
when @first_name_state = 1 and @first_name = t.first_name then 1
when @first_name_state = 1 and @first_name is null and t.first_name is null then 1
else 2
end,
@first_name = t.first_name,
@last_name_state =
case
when @last_name_state = 0 then 1
when @last_name_state = 1 and @last_name = t.last_name then 1
when @last_name_state = 1 and @last_name is null and t.last_name is null then 1
else 2
end,
@last_name = t.last_name,
@city_state =
case
when @city_state = 0 then 1
when @city_state = 1 and @city = t.city then 1
when @city_state = 1 and @city is null and t.city is null then 1
else 2
end,
@city = t.city,
@country_state =
case
when @country_state = 0 then 1
when @country_state = 1 and @country = t.country then 1
when @country_state = 1 and @country is null and t.country is null then 1
else 2
end,
@country = t.country
from Table1 as t;
if @first_name_state = 1
set @result = json_modify(json_modify(@result,'$.first_name','null'),'strict $.first_name',@first_name);
if @last_name_state = 1
set @result = json_modify(json_modify(@result,'$.last_name','null'),'strict $.last_name',@last_name);
if @city_state = 1
set @result = json_modify(json_modify(@result,'$.city','null'),'strict $.city',@city);
if @country_state = 1
set @result = json_modify(json_modify(@result,'$.country','null'),'strict $.country',@country);
select @result;
----------------------------------
{"first_name":"John","city":null}
Please note that, according to Microsoft docs you shouldn't use this variable aggregation assignment trick cause some of the statements can be called more than once.
Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row.
I hope in this case it should work fine cause it's not exactly an aggregation, and it's ok if these statements will be called more than once per row. Still, you can find some useful links in this answer.
这篇关于SQL Server - 如果只有一个不同的值 + 空值而没有 ansi 警告，则聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
