具有由组子查询和计数

Question

tbl_Offer

OFID bigint 
Offer_Text text 

OFID Offer_Text 
------- ----------  
1014 Test1 
1015 Test2 

tbl_TransactionDishout

offerNo TerminalID  Created 
--------------------------------- 
1014  170924690436418 2010-05-25 12:51:59.547 

tblVTSettings

gid  mid  tid 
----------------------- 
50  153  119600317313328 
104  158  160064024922223 
76  162  256674529511898 
1111  148  123909123909123 

这些都是THRE表格。

现在我想了解由学校分开的所有优惠（优惠）的信息（看看gid TerminalID在（50,76,104））。

这是三个学校：（50,76,104）

的O/P应该具备以下字段：
OFFERID（欧佩克国际发展基金），学校要约的，OFFER_TEXT，时间数的报价。

查询可能会以某种方式是这样的：

SELECT OFID, Offer_Text, 
    Counter = 
    (
    SELECT COUNT(*) FROM dbo.tbl_TransactionDishout t 
    WHERE t.OfferNo = CAST(OFID AS NVARCHAR(30)) 
    and t.TerminalID in 
    (select TID from tblVTSettings where gid in (50,76,104)) 
    ) 
    FROM dbo.tbl_Offer 
    Where EXISTS (SELECT * FROM dbo.tbl_TransactionDishout 
      WHERE OfferNo = CAST(OFID AS NVARCHAR(30))) 

Answer 1

请试试这个。

SELECT to.OFID 
     ,ts.gid AS 'School the offer is for' 
     ,to.Offer_Text 
     ,COUNT(to.OFID) AS 'Number of time the offer is' 
    FROM tbl_Offer to 
    JOIN tbl_TransactionDishout tt 
    ON to.OFID = tt.offerNo 
    JOIN tblVTSettings ts 
    ON ts.tid = tt.TerminalID 

Answer 2

尝试：

SELECT o.OFID, 
     s.gid, 
     o.Offer_Text, 
     count(*) over (partition by o.OFID) number_schools, 
     count(*) over (partition by s.gid) number_offers 
    FROM tbl_Offer o 
    JOIN tbl_TransactionDishout d ON o.OFID = d.offerNo 
    JOIN tblVTSettings s ON s.tid = d.TerminalID