我是sql新手，我想有什么方法可以简化这个查询吗？

gev0vcfq 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(251)

我对sql非常陌生，这样做是为了满足我对工作的基本需求，基本上我不是一个技术人员。但我很想知道有没有什么方法可以简化我的sql代码：

SET sql_mode = '';

SELECT DISTINCT partner.kode_agent,
                institusi.nama,
                count(proposal.kode_proposal) AS 'Jumlah Proposal',
                sum(project.jumlah_pencairan) AS 'Jumlah Pencairan'
FROM brw_proposals proposal
JOIN ms_agent partner ON proposal.kode_agent = partner.kode_agent
JOIN ms_agent_institusi institusi ON partner.kode_institusi = institusi.kode_institusi
JOIN ms_projects project ON proposal.kode_proposal = project.kode_proposal
WHERE institusi.nama like 'BMT%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'BMT%' and proposal.status_proposal_id = 10
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'Baitul Maal Wat%' and proposal.status_proposal_id = 10
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 4
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 8
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 3
  OR institusi.nama like 'Ksp%' and proposal.status_proposal_id = 10
GROUP BY institusi.nama

因为我猜where子句太多了，我对子查询不太熟悉。
希望任何人都能分享他们的经验，以改善我的代码！谢谢

sql mysql redash

来源：https://stackoverflow.com/questions/62247413/im-new-to-sql-and-thinking-is-there-any-way-to-simplify-this-query

2条答案

按热度按时间

xwbd5t1u1#

以下是简化的查询：

SELECT DISTINCT partner.kode_agent,
                institusi.nama,
                count(proposal.kode_proposal) AS 'Jumlah Proposal',
                sum(project.jumlah_pencairan) AS 'Jumlah Pencairan'
FROM brw_proposals proposal
JOIN ms_agent partner ON proposal.kode_agent = partner.kode_agent
JOIN ms_agent_institusi institusi ON partner.kode_institusi = institusi.kode_institusi
JOIN ms_projects project ON proposal.kode_proposal = project.kode_proposal
WHERE (institusi.nama like 'BMT%' OR institusi.nama like 'Ksp%' OR institusi.nama like 'Baitul Maal Wat%') and proposal.status_proposal_id IN (4,8,3,10)
GROUP BY institusi.nama

你可以用 IN 子句匹配多个值，如

proposal.status_proposal_id IN (4,8,3,10)

赞(0）回复(0）举报 2021-07-26

6psbrbz92#

下面是一个有效查询的示例。没有足够的信息来说明它是否满足您的要求。。。

SELECT p.kode_agent
     , i.nama
     , COUNT(k.kode_proposal) 'Jumlah Proposal'
     , SUM(j.jumlah_pencairan) 'Jumlah Pencairan'
  FROM brw_proposals k
  JOIN ms_agent p 
    ON k.kode_agent = p.kode_agent
  JOIN ms_agent_institusi i
    ON i.kode_institusi = p.kode_institusi 
  JOIN ms_projects j
    ON k.kode_proposal = j.kode_proposal
 WHERE k.status_proposal_id IN(4,8,3,10)
   AND ( i.nama like 'BMT%'
      OR i.nama like 'Baitul Maal Wat%'
      OR i.nama like 'Ksp%' 
       )
 GROUP 
    BY i.nama
     , p.kode_agent;

赞(0）回复(0）举报 2021-07-26

我来回答

我是sql新手，我想有什么方法可以简化这个查询吗？

2条答案

相关问题

热门标签

最新问答