如何从带有条件的表中检索数据？

Question

我正在开发mysql服务器中的数据库视图，通过将grns加入注释表来获取grns的状态。我想显示grn被拒绝或批准。对于注释表上的单个grns，可能有几个注释。

如果grn的注释表状态中有一个grn的“批准”，则必须以“已批准”的形式返回，否则“拒绝”或“待定”，这里是两个表的虚拟。

​

​

预期成果：

G1 - rejected
G2 - approved
G3 - approved
G4 - approved
G5 - approved

Answer 1

可以通过测试comment中是否存在每个grn-no值具有approved或rejected状态的行来完成这一任务。如果grn既未被批准，也未被拒绝，则状态设置为pending。

SELECT g.`grn-no`,
       CASE WHEN EXISTS (SELECT * FROM comment c WHERE c.grn_id = g.id AND c.status = 'approved') THEN 'approved'
            WHEN EXISTS (SELECT * FROM comment c WHERE c.grn_id = g.id AND c.status = 'rejected') THEN 'rejected'
            ELSE 'pending' END AS status
FROM grn g    

输出：

grn-no  status
G1      rejected
G2      approved
G3      approved
G4      approved
G5      approved

dbfiddle演示

Answer 2

您可以使用group_concat(flatten)函数

Select
`grn-no`, 
Case when status like ('%approved%') then 'approved' else 'rejected' end as status 
From
(Select  
`grn-no`, 
 group_concat(status) as status 
 From grn 
 left join comment 
 on grn.id = comment.grn_id  
 group by 1) a

Answer 3

最后，我发现了上述代码后面的所有函数。上面的问题是开发下面的数据库视图。

CREATE OR REPLACE VIEW summery AS

    SELECT
    g.id,
    g.supply_date,
    g.grn_no,
    COUNT(b.bag_no) AS bags,
    CONCAT(s.fname," ", s.lname) AS name,
    SUM(b.weight) AS qty,
    AVG(b.bag_mc) AS mc,
    (g.dust_initial/g.dust_weight)*100 AS dust,
    (g.ubs_initial/g.ubs_weight)*100 AS ubs,
    p.fraction_1 AS fraction_1,
       CASE
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'approved') THEN 'Approved'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'approved with concession') THEN 'Approved with Concession'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'concession required') THEN 'Concession Required'
        WHEN EXISTS (SELECT * FROM comments c WHERE c.grn_id = g.id AND c.status = 'rejected') THEN 'Rejected'
       ELSE 'pending' END AS status
FROM grns g
    JOIN suppliers s ON s.id = g.supplier_id
    JOIN psds p ON g.id = p.grn_id
    JOIN bags b ON g.id = b.grn_id
GROUP BY g.id
ORDER BY g.id DESC