查询帮助(不同和左联接) [英] Query Help (Distinct and Left Join)

问题描述

大家好.美好的一天！

香港专业教育学院创建了一个查询，但似乎有很多多余的数据.

这是我的查询:

Hello guys. Good day!

Ive created a query but it seems that there are many excess data.

here''s my query:

select distinct ticketstatus.tickettype, stat1.ticketcount as onhand, stat2.ticketcount as pending, stat3.ticketcount as sold from ticketstatus 

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = ''Blink182 (Get in the Phil)'' and tixstatus = ''OnHand'' group by TicketType, tixstatus) stat1 on stat1.tickettype = ticketstatus.tickettype

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = ''Blink182 (Get in the Phil)'' and tixstatus = ''Pending'' group by TicketType, tixstatus) stat2 on stat2.tickettype = ticketstatus.tickettype

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = ''Blink182 (Get in the Phil)'' and tixstatus = ''Sold'' group by TicketType, tixstatus) stat3 on stat3.tickettype = ticketstatus.tickettype

当我在程序上使用它显示所有票证类型时，我想显示特定音乐会的票证类型，例如在查询中我分配了一个在数据库上的票证类型.请帮我.我找不到我需要改变的东西.

预先谢谢你们.祝你有美好的一天. :)

when i use it on my program it shows all the tickettype, I want to show the ticket type for the particular concert like in my query i assigned one which is on my db. please help me. i cant spot what i need to change.

thank you guys in advance. have a great day forward. :)

推荐答案

没有什么奇怪的，使用左联接意味着您可以从第一个表中获得所有选定的行.
我想您真正想要的是关键点.但是由于您没有说明您拥有的sql-server版本，所以我不能确定它是否受支持.

但请放心，使用CASE WHEN
很容易
尝试以下操作或其他操作:

Nothing strange there, using left joins means that you get ALL selected rows from the first table.
I guess what you actually want is a pivot. But as you don''t state what version of sql-server you have, I can''t be sure it''s supported.

But don''t worry, it''s easy enough to do with a CASE WHEN

Try this or a variation of it:

SELECT  TicketType
       ,Sum(CASE TixStatus WHEN 'OnHand' THEN 1 ELSE 0 END) AS OnHand
       ,Sum(CASE TixStatus WHEN 'Pending' THEN 1 ELSE 0 END) AS Pending
       ,Sum(CASE TixStatus WHEN 'Sold' THEN 1 ELSE 0 END) AS Sold
FROM    TicketStatus
WHERE   ConcertTitle = 'Blink182 (Get in the Phil)'
GROUP BY TicketType

这篇关于查询帮助(不同和左联接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！