sql - 在 SQL Server 中应用案例时 GROUP BY 不起作用
问题描述
我的目标是获取repairNo
如果计数值等于 1,否则返回默认值为 null 或其他值。在这里应用 case 语句之前工作正常,但在使用 case 之后,没有按预期返回。将每个项目作为单行返回。未应用分组依据。如何解决这个问题。欢迎任何建议。
SELECT kunden.kundennr,
kunden.firma,
kunden.vorname,
kunden.nachname,
kunden.mobil,
kunden.email,
kunden.geburtsdatum,
kunden.isdatenschutzerklaerung,
Count(reparaturen.reparaturnr) AS Counts,
CASE
WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast(
reparaturen.reparaturnr AS NVARCHAR(20))
ELSE 0
END AS RepairNo
FROM kunden
LEFT JOIN reparaturen
ON reparaturen.kundennr = kunden.kundennr
LEFT JOIN personal AS PersonalAngenommen
ON reparaturen.personalnr = PersonalAngenommen.personalnr
LEFT JOIN lieferanten
ON reparaturen.kennnr = lieferanten.lieferantennr
AND reparaturen.kenntyp = 2
LEFT JOIN personal
ON reparaturen.kennnr = personal.personalnr
AND reparaturen.kenntyp = 1
WHERE kunden.geloescht = 0
AND ( kunden.firma LIKE '%G%' )
GROUP BY kunden.kundennr,
kunden.firma,
kunden.vorname,
kunden.nachname,
kunden.mobil,
kunden.email,
kunden.geburtsdatum,
kunden.isdatenschutzerklaerung,
reparaturen.reparaturnr
ORDER BY kunden.nachname,
kunden.vorname
解决方案
你不能拥有Reparaturen.ReparaturNr
,CASE
因为你没有GROUP BY
它。
(从正确告诉您将其从 中删除的评论继续GROUP BY
。)
将其从 中删除后GROUP BY
,然后尝试...
CASE
WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast(
MAX(reparaturen.reparaturnr) AS NVARCHAR(20))
ELSE 0
END AS RepairNo
然后你使用聚合函数,就像错误消息告诉你的那样。
SELECT kunden.kundennr,
kunden.firma,
kunden.vorname,
kunden.nachname,
kunden.mobil,
kunden.email,
kunden.geburtsdatum,
kunden.isdatenschutzerklaerung,
Count(reparaturen.reparaturnr) AS Counts,
CASE
WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast(
MAX(reparaturen.reparaturnr) AS NVARCHAR(20))
ELSE 0
END AS RepairNo
FROM kunden
LEFT JOIN reparaturen
ON reparaturen.kundennr = kunden.kundennr
LEFT JOIN personal AS PersonalAngenommen
ON reparaturen.personalnr = PersonalAngenommen.personalnr
LEFT JOIN lieferanten
ON reparaturen.kennnr = lieferanten.lieferantennr
AND reparaturen.kenntyp = 2
LEFT JOIN personal
ON reparaturen.kennnr = personal.personalnr
AND reparaturen.kenntyp = 1
WHERE kunden.geloescht = 0
AND ( kunden.firma LIKE '%G%' )
GROUP BY kunden.kundennr,
kunden.firma,
kunden.vorname,
kunden.nachname,
kunden.mobil,
kunden.email,
kunden.geburtsdatum,
kunden.isdatenschutzerklaerung
ORDER BY kunden.nachname,
kunden.vorname
推荐阅读
- reactjs - 为什么在这里为参数“人”添加花括号
- vue.js - buefy@0.9.3 需要 vue@^2.6.11 的对等点,但没有安装
- python - 如何将烧瓶中的单个字符串值返回到 Html 标签标记?
- c++ - 如何编译和运行一个hello world JUCE程序?
- python - 错误消息时差
- javascript - 如何以角度添加可折叠按钮以启动和停止
- c - 使用 Arduino 发送 APDU 命令时出错。更改模块上下文
- ruby-on-rails - 异常错误="未初始化的常量 V1" 使用运动鞋
- mysql - SQL中如何使用Trigger函数进行更新
- javascript - React - 意外的模板字符串表达式:no-template-curly-in-string