英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论