您好,我有以下select查询,它给出了4列的输出:
SELECT (C.customer_id) AS customer_id,
CONCAT((C.first_name),' ',(C.last_name)) Customername,
(CASE WHEN l.line_status=0 THEN '0' WHEN l.line_status ='1' THEN '1' END) STATUS,
,IF(l.`line_status` = '0', 'enabled', 'disabled') AS account_status
FROM
agingdetails ad , customer c , customers_cycletype cct , customer_contact cc , line l , customer_line_mapping clm
WHERE C.customer_id = ad.customerid
AND cct.customer_id=C.customer_id
AND cc.customer_id=C.customer_id
AND cct.cycle_type= 'A'
AND c.customer_id=24
AND ad.customerid=clm.customer_id
AND clm.line_id = l.line_id
GROUP BY l.line_id对于1个客户,上述查询的结果如下:
customer_id Customername STATUS account_status
24 ENDTEST5 1 disabled
24 ENDTEST5 0 enabled
24 ENDTEST5 0 enabled 要求是,如果这个customer_id的STATUS列在总共3行中显示为status '1‘,那么对于其他剩余的行,它也应该在account_status列中显示"disabled“。类似于下面的内容。
customer_id Customername STATUS account_status
24 ENDTEST5 1 disabled
24 ENDTEST5 0 disabled
24 ENDTEST5 0 disabled蒂娅祝大家。
发布于 2021-05-20 17:00:39
...
IF(SUM(l.`line_status`) OVER (PARTITION BY C.customer_id, C.first_name, C.last_name),
'enabled',
'disabled') AS account_status
...https://stackoverflow.com/questions/67617003
复制相似问题