Mysql 在 CASE 中使用 COUNT

Question

是否可以在 CASE 内使用 count，这是我的查询。给我一个错误 #1111

SELECT t1.id,t1.name,t1.date_entered,t1.date_modified, t1.adresse,t1.adresse_state,t1.adresse_city,t1.adresse_postalcode,t1.hausnummer,t1.center_lat,t1.center_long, t3.name as street_name,t3.strasse,t3.primaerstr 
FROM hi_haus as t1 
LEFT JOIN hi_haus_hi_strasse as t2 ON (t2.hi_haus_id = t1.id)
LEFT JOIN hi_strasse as t3 ON (t3.id = t2.hi_strasse_id)
WHERE t1.adresse !='' AND t1.adresse IS NOT NULL AND t1.adresse NOT LIKE '%bau%' AND t1.id IN ('58e722d6-44f5-4e24-8861-5cd4a6f07cbc','4d0a79ee-decf-49b1-8b4b-a912b278ffec')
AND t3.primaerstr =
CASE
WHEN count(t1.id) > '1' THEN t3.primaerstr='0'
ELSE
t3.primaerstr = '1'
END
GROUP BY t1.id

如果 id 重复多次，我需要的是获取 primaerstr '0'。

Answer 1

试试这个


选择 t1.id ，
t1.名称，
t1.输入日期，
t1.date_modified ,
t1.地址，
t1.adresse_state ,
t1.adresse_city ,
t1.adresse_postalcode ,
t1.hausnummer ,
t1.center_lat ,
t1.center_long ,
t3.name AS 街道名称 ,
t3大街，
t3.primaerstr
来自 hi_haus AS t1 ，
(选择 ID，
COUNT(*) AS 计数
来自 hi_haus
按 ID 分组
) AS 子查询
左连接 hi_haus_hi_strasse AS t2 ON ( t2.hi_haus_id = t1.id )
左连接 hi_strasse AS t3 ON ( t3.id = t2.hi_strasse_id )
其中 t1.adresse != ''
并且 t1.adresse 不为空
并且 t1.adresse 不像“%bau%”
AND t1.id IN ('58e722d6-44f5-4e24-8861-5cd4a6f07cbc',
'4d0a79ee-decf-49b1-8b4b-a912b278ffec')
AND (( t3.primaerstr = 0
AND t1.id = 子查询.id
AND 子查询.count > 1 )
或 (t3.primaerstr = 1
AND t1.id = 子查询.id
AND 子查询.count = 0)
)
按 t1.id 分组