查询以将行值与列匹配

huangapple go评论73阅读模式
英文:

Query to tick rows values against colums

问题

对于网页内容,我想编写PL SQL查询。在这个网页上,有产品的列,如CAR、BIKE、VAN、LORRY、JEEP等。在同一页上有客户的行。

一旦客户拥有一辆CAR,它应该被打勾。同一个客户可以拥有多个产品。因此,他可以在他的名字下打勾多个产品。

如何编写这样的查询?

视图:

            cars   bike    van   jeep   lorry  cab   bus   

    john    *                     *                   *

    smith          *

    shane                                       *     *    

    mark                   *      *       *     *     *

(请注意,视图的格式在文本中可能不太清晰,实际查询需要根据数据库架构来编写。)

英文:

For a web page content I want to write PL SQL Query. In this web page have columns of products such as CAR,BIKE,VAN,LORRY,JEEP etc. In the same page have rows of customers.

Once the customer have a CAR, It should be ticked. Same customer can have multiple products. Therefore he can be ticked more products against his name.

How to write a query for this?

View:

        cars   bike    van   jeep   lorry  cab   bus   

john    *                     *                   *

smith          *

shane                                       *     *    

mark                   *      *       *     *     *

答案1

得分: 1

一个查询可以像这样创建:

select c.name as "name", 
       max(case when s.product_id = 1 then '*' end) as "cars",
       max(case when s.product_id = 2 then '*' end) as "bike",
       max(case when s.product_id = 3 then '*' end) as "van",
       max(case when s.product_id = 4 then '*' end) as "jeep",       
       max(case when s.product_id = 5 then '*' end) as "lorry",       
       max(case when s.product_id = 6 then '*' end) as "cab",       
       max(case when s.product_id = 7 then '*' end) as "bus"       
  from sells s
  left join customers c on c.id = s.customer_id  
 group by c.id, c.name 
 order by c.id

使用 illustration 进行演示。

英文:

A query might be created like

select c.name as "name", 
       max(case when s.product_id = 1 then '*' end) as "cars",
       max(case when s.product_id = 2 then '*' end) as "bike",
       max(case when s.product_id = 3 then '*' end) as "van",
       max(case when s.product_id = 4 then '*' end) as "jeep",       
       max(case when s.product_id = 5 then '*' end) as "lorry",       
       max(case when s.product_id = 6 then '*' end) as "cab",       
       max(case when s.product_id = 7 then '*' end) as "bus"       
  from sells s
  left join customers c on c.id = s.customer_id  
 group by c.id, c.name 
 order by c.id 

with the <kbd>illustration</kbd>

huangapple
  • 本文由 发表于 2020年1月3日 14:42:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/59574258.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定