gpt4 book ai didi

mysql - 子选择 SUM of columns WHERE x=y

转载 作者:太空宇宙 更新时间:2023-11-03 12:05:26 24 4
gpt4 key购买 nike

我目前有一个大致如下的声明:

SELECT
SUM(claims.sanctioned_resultant) AS 'Resultant',
SUM(claims.sanctioned_flooring) AS 'Flooring',
SUM(claims.sanctioned_contents) AS 'Furniture',

等等,我需要以某种方式在其中包含一个“WHERE”。沿着:

SUM(claims.sanctioned_resultant WHERE claims.a_column = 1)

显然这行不通,因为 SUM 只是..求和。我目前有一个完整的其他查询,其中包含 WHERE claims_a_column = 1 子句,然后合并结果。不能将“WHERE”条件添加到此查询,因为它不会选择大多数结果。

我研究了 CASE WHEN 和子选择,但无法弄清楚实际的语法。

完整查询,以防有帮助:

SELECT
CONCAT(users.user_first_name,IF((users.user_surname<>''),CONCAT(' ',users.user_surname),'')) as 'Surveyor',
SUM(claims.sanctioned_roof + claims.sanctioned_building + claims.sanctioned_internal + claims.sanctioned_resultant + claims.sanctioned_flooring + claims.sanctioned_contents) AS 'Overall',
SUM(claims.sanctioned_roof) AS 'Roofing',
SUM(claims.sanctioned_building + claims.sanctioned_internal) AS 'Building',
SUM(claims.sanctioned_resultant) AS 'Resultant',
SUM(claims.sanctioned_flooring) AS 'Flooring',
SUM(claims.sanctioned_contents) AS 'Furniture',
COUNT(CASE WHEN claims.claimed_amount > 0 THEN 1 END) AS 'OverallCount',
COUNT(CASE WHEN (claims.claimed_amount > 0 AND claims.peril_id IN (" . implode(',', $WRPPerils) . ")) THEN 1 END) AS 'WRPCount',
COUNT(CASE WHEN claims.claimed_roof > 0 THEN 1 END) AS 'RoofingCount',
COUNT(CASE WHEN (claims.claimed_building > 0) OR (claims.sanctioned_internal > 0) THEN 1 END) AS 'BuildingCount',
COUNT(CASE WHEN claims.claimed_resultant > 0 THEN 1 END) AS 'ResultantCount',
COUNT(CASE WHEN claims.claimed_flooring > 0 THEN 1 END) AS 'FlooringCount',
COUNT(CASE WHEN claims.claimed_contents > 0 THEN 1 END) AS 'FurnitureCount',
COUNT(CASE WHEN claims.claimed_amount > 0 AND claims.letter_of_findings = 'Y' THEN 1 END) AS 'LOFCount'
FROM
claims claims
INNER JOIN users users ON claims.surveyor_id = users.user_id
INNER JOIN insurers insurers on claims.insurer_id = insurers.insurer_id
WHERE
(claims.claim_type <> " . $conn->qstr(TYPE_DESKTOP) . ") AND
(claims.claim_type <> " . $conn->qstr(TYPE_AUDIT) . ") AND
(claims.claim_cancelled_id <= 0) AND
(claims.date_completed BETWEEN '" . $start_date . " 00:00:00' AND '" . $end_date . " 23:59:59') AND
(claims.overturn_confirmed = 'N' OR claims.overturn_confirmed IS NULL)
GROUP BY surveyor_id

最佳答案

我认为最简单的方法是在 SUM 函数本身中使用 CASE 语句,如下所示:

SUM(CASE WHEN claims.a_column = 1 THEN claims.sanctioned_resultant ELSE 0 END)

关于mysql - 子选择 SUM of columns WHERE x=y,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26867692/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com